管理模式

最近更新时间:2019-12-03 17:09:06

模式本质上是一个名字空间,Oracle 中一般叫用户,SQL Server 中叫框架,MySQL 中叫数据库,模式里面包含表、数据类型、函数以及操作符,对象名称可以与在其他模式中存在的对象重名,访问某个模式中的对象时可以使用"模式名.对象名"。

创建模式

标准语句

postgres=# create schema tbase;
CREATE SCHEMA

扩展语法(不存在时才创建)

postgres=# create schema if not exists tbase ;   
NOTICE:  schema "tbase" already exists, skipping
CREATE SCHEMA

postgres=# 

指定所属用户

postgres=# create schema tbase_pgxz AUTHORIZATION pgxz;
CREATE SCHEMA

postgres=# \dn tbase_pgxz
  List of schemas
    Name    | Owner 
------------+-------
 tbase_pgxz | pgxz
(1 row)

修改模式属性

修改模式名

postgres=# alter schema tbase rename to tbase_new;
ALTER SCHEMA

修改所有者

postgres=# alter schema tbase_pgxz owner to tbase;
ALTER SCHEMA

删除模式

postgres=# drop schema tbase_new;
DROP SCHEMA

当模式中存在对象时,则会删除失败,提示如下:

postgres=# create table tbase_pgxz.t(id int);
NOTICE:  Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE
postgres=# drop schema tbase_pgxz;
ERROR:  cannot drop schema tbase_pgxz because other objects depend on it
DETAIL:  table tbase_pgxz.t depends on schema tbase_pgxz
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

可强制删除:

postgres=# drop schema tbase_pgxz CASCADE;
NOTICE:  drop cascades to table tbase_pgxz.t
DROP SCHEMA

配置用户访问模式权限

普通用户对于某个模式下的对象访问除访问对象要授权外,模式也需要授权。

[tbase@VM_0_37_centos root]$ psql -U tbase
psql (PostgreSQL 10.0 TBase V2)
Type "help" for help.

postgres=# create table tbase.t(id int);
NOTICE:  Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE
--这里授权用户可以访问tbase.t表
postgres=# grant select on tbase.t to pgxz;
GRANT
postgres=# \q
[tbase@VM_0_37_centos root]$ psql -U pgxz
psql (PostgreSQL 10.0 TBase V2)
Type "help" for help.
--在没授权用户可以使用tbase模式前,无法访问
postgres=> select * from tbase.t;
ERROR:  permission denied for schema tbase
LINE 1: select * from tbase.t;
                      ^
postgres=> \q
[tbase@VM_0_37_centos root]$ psql -U tbase
psql (PostgreSQL 10.0 TBase V2)
Type "help" for help.

postgres=# grant USAGE on SCHEMA tbase to pgxz;
GRANT
postgres=# \q
[tbase@VM_0_37_centos root]$ psql -U pgxz
psql (PostgreSQL 10.0 TBase V2)
Type "help" for help.

postgres=> select * from tbase.t;
 id 
\----
(0 rows)

postgres=> 

配置访问模式的顺序

TBase 数据库中有一个运行变量为 search_path,其值为模式名列表,用于配置访问数据对象的顺序,如下所示:

当前连接用户

postgres=# select current_user;
 current_user 
\--------------
 tbase
(1 row)

共三个模式

postgres=# \dn
   List of schemas
     Name     | Owner 
--------------+-------
 public       | tbase
 tbase        | tbase
 tbase_schema | tbase
(3 rows)

搜索路径只配置为"$user", public,其中"$user"为当前用户名,即上面的 current_user 值“tbase”

postgres=# show search_path ;
   search_path   
\-----------------
 "$user", public
(1 row)

不指定模式创建数据表,则该表存放于第一个搜索模式下面

postgres=# create table t(id int,mc text);
NOTICE:  Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE
postgres=# \dt
       List of relations
Schema | Name | Type  | Owner 
--------+------+-------+-------
 tbase  | t    | table | tbase
(1 row)

指定表位于某个模式,不同模式下表名可以相同

postgres=# create table public.t(id int,mc text);
NOTICE:  Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE
postgres=# \dt public.t 
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | t    | table | tbase
(1 row)

postgres=# create table tbase_schema.t1(id int,mc text);      
NOTICE:  Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE
postgres=# 

访问不在搜索路径对象时,需要写全路径

postgres=# select * from t1;
ERROR:  relation "t1" does not exist
LINE 1: select * from t1;
                      ^
postgres=# select * from tbase_schema.t1;
 id | mc 
----+----
(0 rows)

以上出错是因为模式 tbase_schema 没有配置在 search_path 搜索路径中。