PostgreSQL创建只读账户

目前PostgreSQL并不能像MySQL一样直接对某个数据库赋予只读权限，现实中有研发需要新建一个用户然后赋予对某个数据库只读权限。

举例说明如何创建

用edbstore用户连接edbstore数据库，并创建一个测试schema

$ psql -U edbstore edbstore

psql (9.6.4)

Type "help" for help.

edbstore=> create schema ota_data;

CREATE SCHEMA

edbstore=> \dn ota_data

   List of schemas

   Name   |  Owner

----------+----------

 ota_data | edbstore

(1 row)

在测试schema下创建一张测试表并插入部分数据

edbstore=> set search_path to ota_data ;

SET

edbstore=> show search_path ;

 search_path

-------------

 ota_data

(1 row)

edbstore=> create table tb1(name varchar,age int);

CREATE TABLE

edbstore=> insert into tb1 values('chris',23);

INSERT 0 1

edbstore=> insert into tb1 values('tonny',25);

INSERT 0 1

edbstore=> select * from tb1;

 name  | age

-------+-----

 chris |  23

 tonny |  25

(2 rows)

现在新建一个readonly用户并尝试访问edbstore用户下新建的测试数据

postgres=# create role readonly password 'readonly' login;

CREATE ROLE

$ export PGPASSWORD=readonly

$ psql -h 172.16.101.66 -U readonly edbstore

psql (9.6.)

Type "help" for help.

edbstore=> set search_path to ota_data ;

SET

edbstore=> \d

No relations found.

可以看到默认情况下新用户readonly是无法看到edbstore用户下的数据的，现在赋予用户readonly查看schema对象的权限

edbstore=> GRANT USAGE ON SCHEMA ota_data TO readonly;

GRANT

readonly用户再次查看

edbstore=> \d

         List of relations

  Schema  | Name | Type  |  Owner

----------+------+-------+----------

 ota_data | tb1  | table | edbstore

(1 row)

edbstore=> select * from tb1 ;

ERROR:  permission denied for relation tb1

可以看到虽然用户可以查看到该对象，但是没用select权限，现在进行赋权

edbstore=> \dp

                             Access privileges

  Schema  | Name | Type  | Access privileges | Column privileges | Policies

----------+------+-------+-------------------+-------------------+----------

 ota_data | tb1  | table |                   |                   |

(1 row)

edbstore=> GRANT SELECT ON ALL TABLES IN SCHEMA ota_data TO readonly;

GRANT

edbstore=> \dp

                                 Access privileges

  Schema  | Name | Type  |     Access privileges     | Column privileges | Policies

----------+------+-------+---------------------------+-------------------+----------

 ota_data | tb1  | table | edbstore=arwdDxt/edbstore+|                   |

          |      |       | readonly=r/edbstore       |                   |

(1 row)

readonly用户再次查看

edbstore=> select * from tb1 ;

 name  | age

-------+-----

 chris |  23

 tonny |  25

(2 rows)

edbstore=> insert into tb1 values('tina',19);

ERROR:  permission denied for relation tb1

该命令只能对已经存在的对象（表，视图等）赋权，新建立的表，readonly用户是无法查看的，新建一张表

edbstore=> create table tb2 as select * from tb1;

SELECT 2

edbstore=> \dp

                                 Access privileges

  Schema  | Name | Type  |     Access privileges     | Column privileges | Policies

----------+------+-------+---------------------------+-------------------+----------

 ota_data | tb1  | table | edbstore=arwdDxt/edbstore+|                   |

          |      |       | readonly=r/edbstore       |                   |

 ota_data | tb2  | table |                           |                   |

(2 rows)

readonly用户再次查看

edbstore=> \d

         List of relations

  Schema  | Name | Type  |  Owner

----------+------+-------+----------

 ota_data | tb1  | table | edbstore

 ota_data | tb2  | table | edbstore

(2 rows)

edbstore=> select * from tb2;

ERROR:  permission denied for relation tb2

为了让readonly用户可以有权限继续查看新建的表，需要为该新建的schema指定default权限

edbstore=> ALTER DEFAULT PRIVILEGES IN SCHEMA ota_data GRANT SELECT ON TABLES TO readonly;

ALTER DEFAULT PRIVILEGES

edbstore=> \ddp

             Default access privileges

  Owner   |  Schema  | Type  |  Access privileges

----------+----------+-------+---------------------

 edbstore | ota_data | table | readonly=r/edbstore

(1 row)

edbstore=> create table tb3 as select * from tb1;

SELECT 2

edbstore=> \dp

                                 Access privileges

  Schema  | Name | Type  |     Access privileges     | Column privileges | Policies

----------+------+-------+---------------------------+-------------------+----------

 ota_data | tb1  | table | edbstore=arwdDxt/edbstore+|                   |

          |      |       | readonly=r/edbstore       |                   |

 ota_data | tb2  | table |                           |                   |

 ota_data | tb3  | table | edbstore=arwdDxt/edbstore+|                   |

          |      |       | readonly=r/edbstore       |                   |

(3 rows)

可以看到新建的表tb3会自动继承我们给该schema下的表预定义的select权限。

edbstore=> select * from information_schema.role_table_grants where grantee='readonly';

 grantor  | grantee  | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy

----------+----------+---------------+--------------+------------+----------------+--------------+----------------

 edbstore | readonly | edbstore      | ota_data     | tb1        | SELECT         | NO           | YES

 edbstore | readonly | edbstore      | ota_data     | tb3        | SELECT         | NO           | YES

(2 rows)

如何取消该权限

alter default privileges in schema public revoke select on tables from readonly ;

关于如何对整个输几局所有schema添加权限可以参考如下命令：

select 'grant usage on schema '|| schema_name || ' to readonly ;' from information_schema.schemata;

select 'GRANT SELECT ON ALL TABLES IN SCHEMA '|| schema_name || ' TO readonly ;' from information_schema.schemata;

select 'ALTER DEFAULT PRIVILEGES IN SCHEMA '|| schema_name || ' GRANT SELECT ON TABLES TO readonly ;' from information_schema.schemata;