PostgreSQL中的默认权限(default privileges)
2020-11-03 09:05 abce 阅读(2347) 评论(0) 编辑 收藏 举报有时候我们会遇到这种情况(这种情况并不少见):用户schema中有很多对象,并且你想授权题用户访问这些表。你可以通过grant直接授权,但是当对象属主创建新的对象呢?你可能还需要再次授权,但是postgresql提供一个解决方案。
postgres=# create user a password 'a'; CREATE ROLE postgres=# grant connect on database postgres to a; GRANT postgres=# create schema a authorization a; CREATE SCHEMA postgres=# alter user a set search_path=a; ALTER ROLE postgres=# create user b password 'b'; CREATE ROLE postgres=# grant connect on database postgres to b; GRANT postgres=# create schema b authorization b; CREATE SCHEMA postgres=# alter user b set search_path=b; ALTER ROLE postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- a | | {} b | | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} postgres=# \dn List of schemas Name | Owner --------+---------- a | a b | b public | postgres (3 rows) postgres=#
这里用户a是对象的属主。创建两张表:
postgres=> \c postgres a You are now connected to database "postgres" as user "a". postgres=> create table t1(a int); CREATE TABLE postgres=> create table t2(a int); CREATE TABLE postgres=> insert into t1(a) values(1); INSERT 0 1 postgres=> insert into t2(a) values(2); INSERT 0 1 postgres=> \d List of relations Schema | Name | Type | Owner --------+------+-------+------- a | t1 | table | a a | t2 | table | a (2 rows) postgres=>
给用户b授权:
postgres=> grant select on table t1 to b; GRANT postgres=> grant select on table t2 to b; GRANT postgres=>
现在用户b是否可以查看a所创键的表的内容呢?
postgres=> \c postgres b You are now connected to database "postgres" as user "b". postgres=> select count(*) from a.t1; ERROR: permission denied for schema a LINE 1: select count(*) from a.t1; ^ postgres=>
这里还需要给b授权访问schema a的权限:
postgres=> \c postgres a You are now connected to database "postgres" as user "a". postgres=> grant usage on schema a to b; GRANT postgres=>
这里只是授权b访问schema a的权限,并不是授权访问里面的表。
postgres=> \c postgres b You are now connected to database "postgres" as user "b". postgres=> select count(*) from a.t1; count ------- 1 (1 row) postgres=> select count(*) from a.t2; count ------- 1 (1 row) postgres=>
现在a再创建一个新表:
postgres=> \c postgres a You are now connected to database "postgres" as user "a". postgres=> create table t3 as select * from t1; SELECT 1 postgres=> \d List of relations Schema | Name | Type | Owner --------+----------------+----------+---------- public | t1 | table | a public | t2 | table | a public | t3 | table | a (3 rows) postgres=>
再次尝试使用b访问t3表:
postgres=> \c postgres b You are now connected to database "postgres" as user "b". postgres=> select count(*) from a.t3; ERROR: permission denied for table a.t3 postgres=>
现在修改一下default privileges:
postgres=> \c postgres a You are now connected to database "postgres" as user "a". postgres=# alter default privileges in schema a grant select on tables to b; ALTER DEFAULT PRIVILEGES postgres=#
那现在b可以访问表t3了吗?
postgres=# \c postgres b You are now connected to database "postgres" as user "b". postgres=> select count(*) from a.t3; ERROR: permission denied for table a.t3 postgres=>
还是不可以,修改了default privileges之后,只是对授权之后创建的对象有效。
现在a用户再创建表t4:
postgres=# \c postgres a You are now connected to database "postgres" as user "a". postgres=> create table t4 as select from t1; SELECT 1 postgres=>
再次看看b是否查看表t4:
postgres=> \c postgres b You are now connected to database "postgres" as user "b". postgres=> select count(*) from a.t4; count ------- 1 (1 row) postgres=>