PostgreSQL14中预定义角色pg_write_all_data和pg_read_all_data

2022-05-09 21:32 abce 阅读(464) 评论(0) 编辑收藏举报

场景用例：

两个数据库database1、database2；

database1有两个schema，database2有一个schema；

每个schema都属于不同的user；

每个schema中含有一个或多个表。

database database1:
  schema schema1:
    owner: user1
    table table1:
      columns:
      - id: integer
      - data: text
    table table2:
      columns:
      - id: integer
      - data: text
  schema schema2:database database1:
  schema schema1:
    owner: user1
    table table1:
      columns:
      - id: integer
      - data: text
    table table2:
      columns:
      - id: integer
      - data: text
  schema schema2:
    owner: user2
    table table1:
      columns:
      - id: integer
      - data: text
database database2:
  schema schema1:
    owner: user1
    table table1:
      columns:
      - id: integer
      - data: text
    owner: user2
    table table1:
      columns:
      - id: integer
      - data: text
database database2:
  schema schema1:
    owner: user1
    table table1:
      columns:
      - id: integer
      - data: text

假设有个用户appuser需要访问所有的表。

在PostgreSQL13中：

首先创建user：

pg13-database1 postgres =# create user appuser;
CREATE ROLE
pg13-database1 postgres =# alter user appuser password 'secretsuperpasswd';
ALTER ROLE

如果我们使用新创建的用户登录，并尝试访问schema1中的表：

pg13-database1 user1 => \c database1 appuser
Password for user appuser:
psql (14.1, server 13.5 (Debian 13.5-1.pgdg110+1))
You are now connected to database "database1" as user "appuser".

pg13-database1 user1 => select * from schema1.table1 limit 5;
ERROR:  permission denied for schema schema1
LINE 1: select * from schema1.table1 limit 5;
                      ^

没有权限。那我们来给apuser授与usage权限：

Pg13-database1 postgres =# grant usage on schema schema1 to appuser;
GRANT

再次尝试查看表：

pg13-database1 appuser => select * from schema1.table1 limit 5;
ERROR:  permission denied for table table1

再来，授予select权限：

pg13-database1 user1 => grant select on schema1.table1 to appuser;
GRANT

pg13-database1 appuser => select * from schema1.table1 limit 5;
 id | data
----+------
  1 | 1
  2 | 2
  3 | 3
  4 | 4
  5 | 5
(5 rows)

再来尝试访问相同schema中的其它表：

pg13-database1 appuser => select * from schema1.table2 limit 5;
ERROR:  permission denied for table table2

我们授与访问所有表的select权限：

pg13-database1 user1 => grant select on all tables in schema schema1 to appuser;
GRANT

pg13-database1 appuser => select * from schema1.table2 limit 5 ;
 id | data
----+------
  1 | 1
  2 | 2
  3 | 3
  4 | 4
  5 | 5
(5 rows)

成功了！

假如，我们发现需要新增加一个表呢？

pg13-database1 user1 => create table schema1.table3 (like schema1.table1) ;
CREATE TABLE

pg13-database1 user1 => insert into schema1.table3(id, data)
select i,i::text from generate_series(1,1000) i;
INSERT 0 1000

如果这时候使用appuser来查看表table3(之前已经授予了select所有表的权限)

pg13-database1 appuser => select * from schema1.table3 limit 5;
ERROR:  permission denied for table table3

还是会报没有权限。如果想查看新增加的表，还得继续按照之前的操作执行一次授权。

为了让我们的用户即使在新表也能保持读取访问权限，我们必须更改schema的默认权限：

pg13-database1 user1 => alter default privileges in schema schema1 grant select on tables to appuser;
ALTER DEFAULT PRIVILEGES

现在可以查看新加的表了：

pg13-database1 user1 => create table schema1.table4 (like schema1.table1) ;
CREATE TABLE

pg13-database1 user1 => insert into schema1.table4(id, data)
select i,i::text from generate_series(1,1000) i;
INSERT 0 1000

pg13-database1 appuser => select * from schema1.table4 limit 5 ;
 id | data
----+------
  1 | 1
  2 | 2
  3 | 3
  4 | 4
  5 | 5
(5 rows)

但是别忘了，还有别的schema，appuser也要能访问。

pg13-database1 appuser => select * from schema2.table1 limit 5;
ERROR:  permission denied for schema schema2

同样，也还要按照上面的操作，再次执行一次。

别忘了，还有其它的数据库呢！

pg13-database1 appuser => \c database2 appuser
psql (14.1, server 13.5 (Debian 13.5-1.pgdg110+1))
You are now connected to database "database2" as user "appuser".

pg13-database2 appuser =>  select * from schema1.table1 limit 5;
ERROR:  permission denied for schema schema1

我们，还是要按照上面的操作，再次执行一次操作！！！