首页 » PostgreSQL/GaussDB » Migrate Oracle to PostgreSQL (系)： User and Schema
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
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。