实验背景

PG中，一个对象创建后默认只有owner或者superuser有权限访问，其他用户如果需要访问，需要grant对应的权限。
生产中如果我们需要对每一个新创建的对象都grant权限比较麻烦，能不能实现新创建的对象都默认grant一些权限呢？
可以使用ALTER DEFAULT PRIVILEGES语句来实现这一需求。

实验步骤

创建实验schema和两个用户

postgres=# create schema privilege_test_schema;
CREATE SCHEMA
postgres=# create user user_app1 password 'user_app1';
CREATE ROLE
postgres=# create user user_app2 password 'user_app2';
CREATE ROLE
postgres=# grant usage on schema privilege_test_schema to user_app1;
GRANT
postgres=# grant usage on schema privilege_test_schema to user_app2;
GRANT

验证默认新创建的对象非owner和superuser没有权限访问

• 其他用户需要grant对应的权限后才可以访问新创建的对象

postgres=# select current_user;
 current_user 
--------------
 postgres
(1 row)
postgres=# create table privilege_test_schema.t1(id int);
CREATE TABLE
postgres=# insert into privilege_test_schema.t1 values(1);
INSERT 0 1

postgres=# set role user_app1;
SET
postgres=> select current_user;
 current_user 
--------------
 user_app1
(1 row)
postgres=> select count(*) from privilege_test_schema.t1;
ERROR:  permission denied for table t1
postgres=> 

postgres=> set role postgres;
SET
postgres=# grant select on table privilege_test_schema.t1 to user_app1;
GRANT
postgres=# set role user_app1;
SET
postgres=> select current_user;
 current_user 
--------------
 user_app1
(1 row)
postgres=> select count(*) from privilege_test_schema.t1;
 count 
-------
     1
(1 row)

修改DEFAULT PRIVILEGES验证新创建的对象对其他用户可以访问

• 修改DEFAULT PRIVILEGES后新创建的对象其他用户已经可以访问了
• 修改DEFAULT PRIVILEGES之前创建的对象其他用户还是不能访问

postgres=> set role postgres;
SET
postgres=# create table privilege_test_schema.t2 as select * from privilege_test_schema.t1;
SELECT 1
postgres=# ALTER DEFAULT PRIVILEGES IN SCHEMA privilege_test_schema GRANT SELECT ON TABLES TO user_app1;
ALTER DEFAULT PRIVILEGES
postgres=# create table privilege_test_schema.t3 as select * from privilege_test_schema.t1;
SELECT 1
postgres=# set role user_app1;
SET
postgres=> select current_user;
 current_user 
--------------
 user_app1
(1 row)

postgres=> select count(*) from privilege_test_schema.t2;
ERROR:  permission denied for table t2
postgres=> select count(*) from privilege_test_schema.t3;
 count 
-------
     1
(1 row)

postgres=> 

验证DEFAULT PRIVILEGES只对执行ALTER DEFAULT PRIVILEGES语句创建的对象有效

• 用户1执行了ALTER DEFAULT PRIVILEGES后，用户2新创建的对象并没有自动grant权限

postgres=> set role postgres;
SET  
postgres=# grant create on schema privilege_test_schema to user_app2;
GRANT
postgres=# set role user_app2;
SET
postgres=> create table privilege_test_schema.t4(id int);
CREATE TABLE
postgres=> insert into privilege_test_schema.t4 values(1);
INSERT 0 1
postgres=> set role user_app1;
SET
postgres=> select count(*) from privilege_test_schema.t4;
ERROR:  permission denied for table t4
postgres=> set role user_app2;
SET
postgres=> ALTER DEFAULT PRIVILEGES IN SCHEMA privilege_test_schema GRANT SELECT ON TABLES TO user_app1;
ALTER DEFAULT PRIVILEGES
postgres=> create table privilege_test_schema.t5 as select * from privilege_test_schema.t4;
SELECT 1
postgres=> set role user_app1;
SET
postgres=> select count(*) from privilege_test_schema.t5;
 count 
-------
     1
(1 row)

验证可以为制定的用户执行ALTER DEFAULT PRIVILEGES

• ALTER DEFAULT PRIVILEGES可以为制定的用户执行

postgres=# create user user_app3 password 'user_app3';
CREATE ROLE
postgres=# grant usage, create on schema privilege_test_schema to user_app3;
GRANT
postgres=# ALTER DEFAULT PRIVILEGES IN SCHEMA privilege_test_schema for role user_app3 GRANT SELECT ON TABLES TO user_app1;
ALTER DEFAULT PRIVILEGES
postgres=# set role user_app3;
SET
postgres=> create table privilege_test_schema.t6(id int);
CREATE TABLE
postgres=> insert into privilege_test_schema.t6 values(1);
INSERT 0 1
postgres=> set role user_app1;
SET
postgres=> select count(*) from privilege_test_schema.t6;
 count 
-------
     1
(1 row)

postgres=> 

DEFAULT PRIVILEGES grant后可以revoke

• 更多语法参考官方文档：https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html

ALTER DEFAULT PRIVILEGES IN SCHEMA privilege_test_schema for role user_app3 GRANT SELECT ON TABLES TO user_app1;

ALTER DEFAULT PRIVILEGES IN SCHEMA privilege_test_schema for role user_app3 REVOKE SELECT ON TABLES FROM user_app1;

\ddp命令可以查看DEFAULT PRIVILEGES

postgres=# \ddp
                     Default access privileges
   Owner   |        Schema         | Type  |   Access privileges   
-----------+-----------------------+-------+-----------------------
 postgres  | privilege_test_schema | table | user_app1=r/postgres
 user_app2 | privilege_test_schema | table | user_app1=r/user_app2
(2 rows)