[大数据]PG用户default privileges授权不生效？

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

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

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

场景复现

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

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)

问题总结

• PostgreSQL数据库通过grant对指定schema下表对象授权，受影响对象仅仅是该schema下已存在表对象
• 若需要对schema下未来创建表对象继承指定权限，需要通过alter default privileges进行授权
• 通过alter default privilege授权，被授权用户仅仅会继承授权用户在该schema下创建的表对象权限，所以对指定schema下的默认权限继承，需要使用后续负责进行DDL操作的数据库账号（通常是schema owner）来进行授权

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖