PG用户default privileges授权不生效？

PG数据库的数据库用户授权相较MySQL是有所不同的。

• MySQL通过grant对整个schema的表对象授权select权限，被授权账号将会拥有该schema下的所有表对象以及未来创建的表对象的select权限。
• PostgreSQL数据库通过grant对整个schema的表对象授权select后，被授权账号默认只拥有该schema下当前所有表对象的select权限，不包含该schema下未来创建的表对象的权限。如果需要数据库用户对该schema下未来创建的表对象继承select权限，需要通过alter defaults进行授权。

笔者虽然清楚需要通过alter defaults授权的方式来保证未来创建对象的权限继承，但是还是发现出现了未来创建对象表权限继承失败的情况，详细信息可看下述场景复现。

场景复现

以下表格为我们对业务用户授权说明以及不同用户查询表时候的一些权限表现：

用户名称	数据库授权操作	现象
postgres	默认super账号
aa	通过postgres账号授权aa用户为schema aa 的owner	业务账号，用户表对象创建
bb	通过aa用户授权bb用户拥有 schema aa 所有table的select权限 + alter default	对当前表 + 未来创建表均具有select权限
cc	通过postgres用户授权cc用户拥有schema aa 所有table的select权限 + alter default	对当前表具有select权限，未来创建表没有权限

1、aa用户授权

• 数据库用户aa为schema aa的owner
• aa用户为schema aa相关业务的业务账号，后续的表创建、业务数据读写都是通过该账号进行操作

# 使用postgres创建aa账号，并进行授权
db1=# create user aa with password '123123qwe';
CREATE ROLE

db1=# create schema aa authorization aa;
CREATE SCHEMA


# 使用aa用户登录数据库，创建表结构，检查相关表对象owner
db1=# \dn
      List of schemas
      Name      |  Owner   
----------------+----------
 aa             | aa
 
db1=> set search_path ='aa';
SET
db1=> create table t1(id int primary key);
CREATE TABLE

db1=> \d
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 aa     | t1   | table | aa
(1 row)

db1=> insert into t1 values(1);
INSERT 0 1
db1=> select * from t1;
 id 
----
  1
(1 row)

2、bb用户授权

• bb用户为schema aa的业务只读账号
• 用户bb的授权使用aa账号进行授权

# 使用postgres创建bb账号
db1=# create user bb with password '123123qwe';
CREATE ROLE

# 使用aa进行授权
db1=> grant USAGE on SCHEMA aa to bb ;
GRANT
db1=> grant SELECT on ALL tables in schema aa to bb ;
GRANT
db1=> alter default privileges in schema aa grant select on tables to bb ;
ALTER DEFAULT PRIVILEGES

3、cc用户授权

• cc用户为schema aa的业务只读账号
• 用户cc的授权使用postgres账号进行授权

# 使用postgres创建cc账号
db1=# create user cc with password '123123qwe';
CREATE ROLE

# 使用postgres进行授权
db1=# grant USAGE on SCHEMA aa to cc ;
GRANT
db1=# grant SELECT on ALL tables in schema aa to cc ;
GRANT
db1=# alter default privileges in schema aa grant select on tables to cc ;
ALTER DEFAULT PRIVILEGES

4、权限继承效果查看

1）初始权限

• 由于上述所有的授权都是在t1创建之后操作的，所以可以看到aa、bb用户都拥有t1表的select权限

# 
db1=# \dp
                            Access privileges
 Schema | Name | Type  | Access privileges | Column privileges | Policies 
--------+------+-------+-------------------+-------------------+----------
 aa     | t1   | table | aa=arwdDxt/aa    +|                   | 
        |      |       | bb=r/aa          +|                   | 
        |      |       | cc=r/aa           |                   | 
(1 row)

# 账号 bb 查询 t1 表数据
db1=> select * from t1;
 id 
----
  1
(1 row)


# 账号 cc 查询 t1 表数据
db1=> select * from t1;
 id 
----
  1
(1 row)

2）使用aa账号，在schema aa下创建新增表t2，观察各账号权限继承情况

• 使用aa用户在schema aa下创建新增表t2
• 可以看到用户bb继承了默认的select权限，但是用户cc没有继承默认的select权限

# 使用aa用户创建新增表t2
db1=> create table t2(id int primary key);
CREATE TABLE
db1=> insert into t2 values(1);
INSERT 0 1
db1=> select * from t2;
 id 
----
  1
(1 row)

# 检查新增表
db1=> \dp
                            Access privileges
 Schema | Name | Type  | Access privileges | Column privileges | Policies 
--------+------+-------+-------------------+-------------------+----------
 aa     | t1   | table | aa=arwdDxt/aa    +|                   | 
        |      |       | bb=r/aa          +|                   | 
        |      |       | cc=r/aa           |                   | 
 aa     | t2   | table | aa=arwdDxt/aa    +|                   | 
        |      |       | bb=r/aa           |                   | 
(2 rows)

# 账号 bb 查询 t2 表数据
db1=> select * from t2;
 id 
----
  1
(1 row)

# 账号 cc 查询 t2 表数据
db1=> select * from t2;
ERROR:  permission denied for table t2

3）使用postgres 用户在schema aa下创建新增表t3，观察各账号权限继承情况

• 使用postgres用户在schema aa下创建新增表t3
• 可以看到用户cc继承了默认的select权限，但是用户bb没有继承默认的select权限

db1=# create table t3(id int primary key);
CREATE TABLE
db1=# \dp
                                Access privileges
 Schema | Name | Type  |     Access privileges     | Column privileges | Policies 
--------+------+-------+---------------------------+-------------------+----------
 aa     | t1   | table | aa=arwdDxt/aa            +|                   | 
        |      |       | bb=r/aa                  +|                   | 
        |      |       | cc=r/aa                   |                   | 
 aa     | t2   | table | aa=arwdDxt/aa            +|                   | 
        |      |       | bb=r/aa                   |                   | 
 aa     | t3   | table | postgres=arwdDxt/postgres+|                   | 
        |      |       | cc=r/postgres             |                   | 
(3 rows)

# 账号 aa 查询 t3 表数据
db1=> select * from t3;
ERROR:  permission denied for table t3

# 账号 bb 查询 t3 表数据
db1=> select * from t3;
ERROR:  permission denied for table t3

# 账号 cc 查询 t3 表数据
db1=> select * from t3;
 id 
----
(0 rows)

问题解决

如果说每次对数据库账号进行default privilege授权时，必须登录对应schema下的owner账号进行授权是非常麻烦的，为此PG本身通过额外的一些参数可以完美的解决这个问题。

alter default privilege语法：

ALTER DEFAULT PRIVILEGES
    [ FOR { ROLE | USER } target_role [, ...] ]
    [ IN SCHEMA schema_name [, ...] ]
    abbreviated_grant_or_revoke

重要参数解释：

target_role
	The name of an existing role of which the current role is a member. If FOR ROLE is omitted, the current role is assumed.

该参数的意思就是，当我们使用postgres数据库用户进行alter default privilege授权时，可以通过 target_role 指定 我们需要授权schema 的owner角色，这时就如同我们登录 owner 用户进行alter default授权。

1、案例演示
1）通过\ddp可以查看 default privilege的授权，当前授权情况表示：
bb可以继承aa创建所有表的default权限；cc可以继承postgres创建所有表的default权限。

db1=> \ddp
           Default access privileges
  Owner   | Schema | Type  | Access privileges 
----------+--------+-------+-------------------
 aa       | aa     | table | bb=r/aa
 postgres | aa     | table | cc=r/postgres
(2 rows)

2）通过 alter default privileges for role进行授权

db1=# alter default privileges for role aa in schema aa grant select ON tables TO cc;
ALTER DEFAULT PRIVILEGES

3）再次查看dp授权情况以及建表查询测试

-- 登录aa用户进行建表
db1=> create table t7(id int primary key);
CREATE TABLE

-- 登录cc用户，查询t7表成功
db1=> select * from t7;
 id 
----
(0 rows)
-- 查看 dp 权限
db1=> \ddp
           Default access privileges
  Owner   | Schema | Type  | Access privileges 
----------+--------+-------+-------------------
 aa       | aa     | table | bb=r/aa          +
          |        |       | cc=r/aa				# 可以看到相对之前，aa owner 下额外有了 cc 对 aa schema 的 r权限的继承
 postgres | aa     | table | cc=r/postgres
(2 rows)

问题总结

• PostgreSQL数据库通过grant对指定schema下表对象授权，受影响对象仅仅是该schema下已存在表对象
• 若需要对schema下未来创建表对象继承指定权限，需要通过alter default privileges进行授权
• 通过alter default privilege授权，被授权用户仅仅会继承当前授权用户在该schema下创建的表对象的指定默认权限；如果需要通过超级用户对其他业务 schema 进行默认授权，需要通过 alter default privileges for role ${schema_role_name} 来进行授权。