PG里 user,db,schema,table 之间的关系
1.结论
1.schema是每个database中都有的。 schema概念有点像命名空间或者把它想像成一个文件系统中的目录
2.user是pg cluster级别的。
3.查询表，是指定 schema.tablename
USER : -> 可以在DB里创建同名的schema ,指定 search_path
DB : -> 每个DB 有自己的owner ，默认为超级用户 postgres
SCHEMA ： -> 在DB里面，有了schema才可以创建对象，默认为schema为 public
注意事项：
1.用户创建的所有对象都被创建在指定的schema(或namespace)中。其他用户可能拥有、也可能不拥有访问这些对象的权限，甚至都不可以在对应的schema中创建对象。
2.用户(或角色)是全局对象，不是定义在数据库中，而是定义在实例的级别。schema是用户在指定的数据库中创建的，其中包含数据库对象。
3.postgresql数据库默认都有一个public schema，如果没有为对象显式地指定schem
4.db owner不一定能操作其下面的某个schema (db里的 schema 的owner 可以指定其它user)
5.schema owner 不一定能操作其下面的某张表 (需要有表的权限，或table owner)
6、授予某个用户select on all tables in schema XX时，需要先对用户授权usage访问schema XX，否则会出现报错Invalid operation: permission denied for schema XX;
grant usage on schema s9 to owner_2;
grant select on all tables in schema s9 to owner_2;
7、以上第6项仅用户只能查询该schema下已经存在的表，无法查询该schema下新建的表. 如果想对该schema下新建的表也获得权限，需要对该schema的owner授权给用户
如：
alter default privileges for user s9_owner in schema s9 grant select on tables to owner_2;\
--以后schema s9的owner s9_owner在schema s9下新建的表,用户owner_2都可以访问
alter default privileges in schema s9 grant select on tables to owner_2;
--当前用户执行如上语句后，此用户在s9下新建的任何表，owner_2都可以访问（其他用户用户创建的表，owner_2不能访问）
--上述语句不是这个意思：对于任何用户在s9下新建的表，owner_2都可以访问
alter default privileges for user user1,user2 in schema s9 grant select on tables to owner_2;
--以后user1,user2在schema s9下新建的表,用户owner_2都可以访问
备注：目前postgresql没有一种方法，可以使以后任何用户在s9下新建的表，owner_2都可以访问。
2.测试
2.1 测试用户1
# 创建huyi 一个用户，2个db
postgres=#CREATE USER huyi WITH PASSWORD 'huyi'
postgres=#CREATE DATABASE huyidb OWNER huyi;
postgres=#GRANT ALL PRIVILEGES ON DATABASE huyidb TO huyi;
postgres=#CREATE DATABASE huyidb2 OWNER huyi;
postgres=#GRANT ALL PRIVILEGES ON DATABASE huyidb2 TO huyi;
postgres=# \l
psql -h 127.0.0.1 -U huyi -d huyidb
huyidb=> create table t1(a int);
huyidb=> \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
public | t1 | table | huyi
(1 row)
# 创建和用户同名的huyi
huyidb=> create schema huyi;
CREATE SCHEMA
huyidb=> create table t2(a int);
CREATE TABLE
huyidb=> \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
huyi | t2 | table | huyi
public | t1 | table | huyi
(2 rows)
huyidb=> show search_path;
search_path
-----------------
"$user", public
(1 row)
# 创建新的schem 的huyisc , 我们发现我们的schema owner 默认是我们的username
huyidb=> \dn
List of schemas
Name | Owner
--------+----------
huyi | huyi
huyisc | huyi
public | postgres
(3 rows)
# 设置search_path
set search_path = 'huyisc',"$user",public;
huyidb=> show search_path ;
search_path
-------------------------
huyisc, "$user", public
(1 row)
# 创建T3表 指定huyisc
huyidb=> create table huyisc.t3(a int);
CREATE TABLE
huyidb=> \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
huyi | t2 | table | huyi
huyisc | t3 | table | huyi
public | t1 | table | huyi
(3 rows)
2.2测试用户2
# 新创建用户huyi2,新把huyidb2授权给 huyi2 ； 即huyidb2 同时授权给huyi，huyi2 两个用户
postgres=# CREATE USER huyi2 WITH PASSWORD 'huyi2' ;
CREATE ROLE
postgres=#
postgres=# GRANT ALL PRIVILEGES ON DATABASE huyidb2 TO huyi2;
GRANT
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+------------------------------
huyidb | huyi | UTF8 | C | C | =Tc/huyi +
| | | | | huyi=CTc/huyi
huyidb2 | huyi | UTF8 | C | C | =Tc/huyi +
| | | | | huyi=CTc/huyi +
| | | | | huyi2=CTc/huyi
# 以huyi2 用户在huyidb2 中创建 schema testsc 指定owner 为 huyi
postgres=# GRANT "huyi" to huyi2;
GRANT ROLE
psql -h 127.0.0.1 -U huyi2 -d huyidb2
huyidb2=> create schema testsc AUTHORIZATION huyi ;
CREATE SCHEMA
huyidb2=> \dn
List of schemas
Name | Owner
--------+----------
public | postgres
testsc | huyi
(2 rows)
set search_path = 'testsc',"$user",public;
# 创建T4表指定testsc
huyidb2=> create table testsc.t4(a int);
CREATE TABLE
huyidb2=> \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
testsc | t4 | table | huyi2
(1 row)
# 这时虽然Schema 的Owner 是 huyi ,但table 的owner 还是huyi2 所以用huyi是查不到t4 数据库的
psql -h 127.0.0.1 -U huyi -d huyi2
huyidb2=> select * from testsc.t4;
ERROR: permission denied for table t4
# 更改t4表的owner 到huyi 后可以查询
psql -h 127.0.0.1 -U huyi2 -d huyi2
huyidb2=> alter table testsc.t4 OWNER TO huyi ;
ALTER TABLE
huyidb2=> \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
testsc | t4 | table | huyi
(1 row)
huyidb2=>
#在次以huyi 用户登录查询
psql -h 127.0.0.1 -U huyi -d huyi2
huyidb2=> select * from testsc.t4;
a
---
(0 rows)
#现在用huyi 还是huyi2用户 都可查询 ，
#huyi 用户
huyidb2=> select * from testsc.t4;
a
---
(0 rows)
#huyi2 用户
huyidb2=> select * from testsc.t4;
a
---
(0 rows)
#因为huyidb库里面 ，huyi2 里有 huyi信息的Member ;
huyidb2=> \du
List of roles
Role name | Attributes | Member of
-----------------+------------------------------------------------------------+-----------------------
huyi | | {}
huyi2 | | {huyi}
select current_schemas(true);
#当 回收huyi2用户里的 huyi的member 信息时，就没有权限
postgres@s2ahumysqlpg01-> psql -h 127.0.0.1
psql (12.4)
Type "help" for help.
postgres=# revoke "huyi" from huyi2;
REVOKE ROLE
postgres=# quit
postgres@s2ahumysqlpg01-> psql -h 127.0.0.1 -U huyi2 -d huyidb2
psql (12.4)
Type "help" for help.
huyidb2=> select * from testsc.t4;
ERROR: permission denied for schema testsc
#这里要用同时授权这2个权限， huyi2步可以又访问t4这张表了
grant USAGE on SCHEMA testsc to huyi2 ;
grant SELECT on testsc.t4 to huyi2 ;
postgres@s2ahumysqlpg01-> psql -h 127.0.0.1 -U huyi2 -d huyidb2
psql (12.4)
Type "help" for help.
huyidb2=> select * from testsc.t4;
a
---
(0 rows)
2.3 补充
select current_database(); --当前db 或直接 \c
select current_user; ---查看当前用户 \ 或 select user;
pg_catalog存放了各系统表,内置函数等等,它总是在搜索路径中,需要通过current_schemas看到
select current_schemas(true);
show search_path ; 参考
https://www.cnblogs.com/abclife/p/13905336.html
http://blog.itpub.net/30126024/viewspace-2661690/
