Migrate Oracle to PostgreSQL (系)： User and Schema

当Oracle DBA开始接触PostgreSQL系数据库时，总是会对Schema和USER产生一些混淆。在 Oracle 中，Schema和USER是一对一的关系，Schema和USER之间没有真正的区别，在一些基于PG国产数据库可能在创建用户时递归创建了同名schema。在 PostgreSQL 中，情况有所不同：用户创建的所有对象都是在特定Schema（或命名空间）中创建的。其他用户可能有权也可能无权使用此对象，甚至有权在特定schema中创建新对象。与 Oracle 相比，PG Schema概念又有点像Oracle Tablespace。最近一套数据库在迁移PG系国产库时，遇到了synonym的问题，刚好再总结一下schema与user.

PostgreSQL 中的层次结构是这样的：

|-------------------------------------------|---|
| PostgreSQL instance                       |   |
|-------------------------------------------| U |
|     Database 1      |     Database 2      | S |
|---------------------|---------------------| E |
| Schema 1 | Schema 2 | Schema 1 | Schema 2 | R |
|----------|----------|----------|----------| S |
| t1,t2,t3 | t1,t2,t3 | t1,t2,t3 | t1,t2,t3 |   |
-------------------------------------------------

PostgreSQL 中的用户（和角色）是全局对象，不是在数据库中定义，而是在实例级别定义。Schema由用户在特定数据库中创建. 但是Postgresql中自带了一个”public” schema, 默认未创建schema前，创建表不报错原因是因为自动创建到了该schema中。

环境kingbase ES v8

-- 查看user

kingbase=# \du
                                        List of roles
    Role name    |                         Attributes                         |  Member of
-----------------+------------------------------------------------------------+--------------
 esrep           | Superuser                                                  | {}
 sao             | No inheritance                                             | {}
 sso             | No inheritance                                             | {}
 system          | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

-- 查看schema 
kingbase=# \dn+
                            List of schemas
       Name       | Owner  | Access privileges |      Description
------------------+--------+-------------------+------------------------
 anon             | system | system=UC/system +|
                  |        | sso=UC/system     |
 dbms_sql         | system |                   |
 perf             | system | system=UC/system  |
 public           | system | system=UC/system +| standard public schema
                  |        | =UC/system        |
 src_restrict     | system | system=UC/system +|
                  |        | sso=UC/system     |
 sysaudit         | system | system=UC/system +|
                  |        | sso=UC/system    +|
                  |        | sao=UC/system    +|
                  |        | =UC/system        |
 sysmac           | system | system=UC/system +|
                  |        | sso=UC/system    +|
                  |        | =U/system         |
 test             | system |                   |
 xlog_record_read | system |                   |
(9 rows)

-- 创建用户(无授权)
kingbase=# create user anbob password 'anbob';
CREATE ROLE


-- 以anbob登录数据库 kingbase
kingbase=# \c kingbase anbob
Password for user anbob:
You are now connected to database "kingbase" as user "anbob".

kingbase=> create table test100(id int);
CREATE TABLE
kingbase=> insert into test100 values (1);
INSERT 0 1

-- 以anbob登录数据库 test
kingbase=> \c test anbob
Password for user anbob:
You are now connected to database "test" as user "anbob".

test=> create table test100(id int);
CREATE TABLE
test=> insert into test100 values (2);
INSERT 0 1

test=> \dt test100
        List of relations
 Schema |  Name   | Type  | Owner
--------+---------+-------+-------
 public | test100 | table | anbob
(1 row)

test=> select * from pg_tables where tablename='test100';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
 public     | test100   | anbob      |            | f          | f        | f           | f
(1 row)


-- 创建另一个用户

kingbase=# create user weejar password 'weejar';
CREATE ROLE

kingbase=# \c test weejar
Password for user weejar:
You are now connected to database "test" as user "weejar".

test=> \dt
         List of relations
 Schema |  Name   | Type  | Owner
--------+---------+-------+--------
 public | t1      | table | system
 public | test1   | table | anbob
 public | test100 | table | anbob
(3 rows)

test=> select * from test100;
ERROR:  permission denied for table test100
test=> select * from t1;
ERROR:  permission denied for table t1

test=> create table test100(id int);
ERROR:  relation "test100" already exists

test=> create table test200(id int);
CREATE TABLE

kingbase=# \du
                                        List of roles
    Role name    |                         Attributes                         |  Member of
-----------------+------------------------------------------------------------+--------------
 anbob           |                                                            | {}
 esrep           | Superuser                                                  | {}
 sao             | No inheritance                                             | {}
 sso             | No inheritance                                             | {}
 system          | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 tpcc            | Superuser                                                  | {}
 weejar          |                                                            | {}

Note:
所有用户默认可以在public中创建表，并用对象名是schema级唯一，所有者是不同用户。这样就会比较混乱。怎样才能避免这种情况呢？

建议从 public 中撤销所有内容：

postgres=# revoke all on schema public from public;
REVOKE

当您删除公共架构并在之后尝试创建表时会发生什么情况？

ksql -U system   -d test  -h 127.0.0.1 -p 54321
Password:
ksql (V8.0)
test=# revoke all on schema public from public;
REVOKE
test=# \c test anbob
Password for user anbob:
You are now connected to database "test" as user "anbob".
test=> \dt
Did not find any relations.
test=> create table test101(id int);
ERROR:  no schema has been selected to create in
LINE 1: create table test101(id int);

--创建schema 使用管理员
test=# create schema weejar;
CREATE SCHEMA

test=# grant all on schema weejar to weejar;   -- grant schema to user;
GRANT

test=# \c test weejar
Password for user weejar:
You are now connected to database "test" as user "weejar".

test=> create table weejar.test101(id int); --schema.
CREATE TABLE

test=> \dt test101
         List of relations
 Schema |  Name   | Type  | Owner
--------+---------+-------+--------
 weejar | test101 | table | weejar
(1 row)

当PUBLIC schema 存在时，为什么它还能工作？创建表时我们没有指定上面的”public.”，但它有效。这就是 search_path 发挥作用的地方：

test=> show search_path
test-> ;
   search_path
-----------------
 "$user", public
(1 row)

默认情况下，search_path 包含您当前的 username 和 public。

如何在 PostgreSQL 中设置/更改默认架构

1，会话级调整 search_path

SET SEARCH_PATH 命令允许用户将任何其他架构设置为默认架构。

test=> set search_path=weejar,"$user", public;
SET
test=> create table test200( id int);
CREATE TABLE
test=> \dt test200
         List of relations
 Schema |  Name   | Type  | Owner
--------+---------+-------+--------
 weejar | test200 | table | weejar
(1 row)

2，数据库级永久更改search_path

ALTER DATABASE db_name SET search_path TO schema_name;

test=# alter database test set search_path=weejar,"$user", public;
ALTER DATABASE
test=# \c test weejar
Password for user weejar:
You are now connected to database "test" as user "weejar".
test=> show search_path;
       search_path
-------------------------
 weejar, "$user", public
(1 row)

test=> \c test anbob
Password for user anbob:
You are now connected to database "test" as user "anbob".
test=> show search_path;
       search_path
-------------------------
 weejar, "$user", public
(1 row)

3, 用户级永久修改search_path

test=> \c test system
Password for user system:
You are now connected to database "test" as user "system".
test=# create schema anbob;
CREATE SCHEMA
test=# alter user anbob set search_path to anbob,"$user", public;
ALTER ROLE
test=# \c test anbob
Password for user anbob:
You are now connected to database "test" as user "anbob".
test=> show search_path
test-> ;
      search_path
------------------------
 anbob, "$user", public
(1 row)

小结：
在 PostgreSQL 中，可以创建不同的schema, 然后可以根据schema再给user授权，用户可以跨schema访问，因为postgresql中没有synonym的概念。“SET SEARCH_PATH”命令用于临时更改schema。要在数据库级别或用户杠杆上永久更改schema，“ALTER DATABASE”和“ALTER USER”命令分别与 “SET SEARCH_PATH”命令一起使用。除此之外我们还应该使用pg_hba.conf 限制访问。

References
a-schema-and-a-user-are-not-the-same-in-postgresql

如何在 PostgreSQL 中设置/更改默认架构

1，会话级调整 search_path

2， 数据库级永久更改search_path

3, 用户级永久修改search_path

2，数据库级永久更改search_path