[转帖]PostgreSQL中的schema和user

postgresql中，用户创建的所有对象都被创建在指定的schema(或namespace)中。其他用户可能拥有、也可能不拥有访问这些对象的权限，甚至都不可以在对应的schema中创建对象。

从上面的表可以看出，用户(或角色)是全局对象，不是定义在数据库中，而是定义在实例的级别。schema是用户在指定的数据库中创建的，其中包含数据库对象。

 

可能有人对下面的操作感到困惑：

1 2 3 4 5 6 7

$ psql psql (11.9) Type "help" for help.   postgres=# create table t1(a int); CREATE TABLE postgres=#

在这个创建表的语句中，并没有指定schema。那这些表会在哪里呢?

每个postgresql数据库默认都有一个public schema，如果没有为对象显式地指定schema，新创建的对象就是放在public schema中。有几种方式可以查看给定的表是属于哪个schema，但是最常用的主要有两种：1.查看系统目录视图；2.使用psql短命令

1 2 3 4 5 6 7 8 9 10 11 12 13

postgres=# select schemaname from pg_tables where tablename ='t1';  schemaname ------------  public (1 rows)   postgres=# \d t1                  Table "public.t1"  Column |  Type   | Collation | Nullable | Default --------+---------+-----------+----------+---------  a      | integer |           |          |   postgres=#

在postgresql中，public schema是一个特殊的schema，你要么移除该schema，要么收回在public schema上授予给public的权限。

 

当你删除了public schema后尝试创建一个表，会发生什么呢？

1 2 3 4 5 6 7 8

postgres=# drop schema public cascade; NOTICE:  drop cascades to table t1 DROP SCHEMA postgres=# create table t1 ( a int ); ERROR:  no schema has been selected to create in LINE 1: create table t1 ( a int );                      ^ postgres=#

　　

我们现在没有schema了：

1 2 3 4 5

postgres=# \dn List of schemas  Name | Owner ------+------- (0 rows)

postgresql不知道去哪里创建表。这里也可以看出，在postgresql中schema和user是不同的了。我们是以postgres用户登录，但是没有schema可以创建对象。

 

现在，我们来创建一个schema：

1 2 3 4 5 6 7

postgres=# create schema my_schema; CREATE SCHEMA postgres=# create table t1(a int); ERROR:  no schema has been selected to create in LINE 1: create table t1(a int);                      ^ postgres=#

仍然不可以创建表。这里可能会问：为什么有public schema就可以呢？在上面的演示中我们并没有指定public schema呀！这里就需要提到参数search_path了：

1 2 3 4 5 6 7

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

缺省情况下，search_path包含当前用户名和public。所以会创建失败。

有两种方式来解决：

1 2 3 4 5 6 7

postgres=# create table my_schema.t1 ( a int ); CREATE TABLE postgres=# \d my_schema.t1                Table "my_schema.t1"  Column |  Type   | Collation | Nullable | Default --------+---------+-----------+----------+---------  a      | integer |           |          |

或者调整search_path的值：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

postgres =  # set search_path = 'my_schema',"$user",public; SET postgres =  # show search_path ; search_path ---------------------------- my_schema, "$user", public (1 row)   postgres =  # create table t2 ( a int ); CREATE TABLE postgres =  # \d t2                 Table "my_schema.t2"  Column |  Type   | Collation | Nullable | Default --------+---------+-----------+----------+---------  a      | integer |           |          |   postgres =  #