PostgreSQL14中预定义角色pg_write_all_data和pg_read_all_data

2022-05-09 21:32 abce 阅读(626) 评论(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权限：

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

再次尝试查看表：

1 2	`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中的其它表：

1 2	`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所有表的权限)

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

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

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

1 2	`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也要能访问。

1 2	`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

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

postgresql14中

在postgresql14中，提供预先定义的角色：

角色	允许访问的范围
pg_read_all_data	读所有的数据(表、视图、序列等)，即在这些对象上有select权限，在所有schemas上有usage权限；该角色不包含BYPASSRLS设置的属性；
pg_write_all_data	写所有的数据(表、视图、序列等)，即在这些对象上有update、insert、delete权限，在所有schemas上有usage权限；该角色不包含BYPASSRLS设置的属性；

现在，来看看在postgresql 14中是如何完成上面的场景用例的。

先创建用户

pg14-database1 postgres =# create user appuser;
CREATE ROLE
pg14-database1 postgres =# alter user appuser password 'supersecretpasswd';
ALTER ROLE

如果，这个时候就想访问表，肯定会报错：

pg14-database1 user1 => \c database1 appuser
Password for user appuser:
You are now connected to database "database1" as user "appuser".
 
pg14-database1 appuser => select * from schema1.table1 limit 5;
ERROR:  permission denied for schema schema1
LINE 1: select * from schema1.table1 limit 5 ;

神奇之旅开始，我们可以使用postgresql 14中预先定义的角色：

1 2	`pg14-database1 postgres =#` `grant` `pg_read_all_data` `to` `appuser ;` `GRANT` `ROLE`

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

果然有效果。

即使访问其它的schema也没有问题：

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

现在，我们新加一个表：

pg14-database1 user1 => create table schema1.table3 (like schema1.table1) ;
CREATE TABLE
 
pg14-database1 user1 => insert into schema1.table3(id, data)
database1-> select i,i::text from generate_series(1,1000000) i;
INSERT 0 1000000

仍然可以查询：

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

如果是其它数据库呢：

pg14-database1 appuser => \c database2 appuser
You are now connected to database "database2" as user "appuser".
 
pg14-database2 appuser => select * from schema1.table1 limit 5;
 id |  data
----+------
  1 | 1
  2 | 2
  3 | 3
  4 | 4
  5 | 5
(5 rows)

还是可以访问。

pg_write_all_data的用户类似。

不过，只是直接授予DML权限，或pg_write_all_data角色，而没有read权限，用户不能对数据的子集执行update、delete操作，因为这两个操作需要过滤数据：

pg14-database1 write_appuser => \dg write_appuser
                List of roles
 Role name      | Attributes |      Member of
----------------+------------+---------------------
 write_appuser  |            | {pg_write_all_data}
 
pg14-database1 write_appuser => insert into schema1.table1(id, data)
values(2000000, 'database1.schema1.table1-2000000');
INSERT 0 1
 
pg14-database1 write_appuser => update schema1.table1 set data = 'database1.schema1.table1-2000000-upd' where id = 2000000 ;
ERROR:  permission denied for table table1
 
pg14-database1 write_appuser => delete from schema1.table1 where id = 2000000 ;
ERROR:  permission denied for table table1
 
pg14-database1 write_appuser => update schema1.table1 set data = '';
UPDATE 1000001
pg14-database1 write_appuser => delete from schema1.table1;
DELETE 1000001