Postgresql 授权只读用户

---

2017-02-22 by dongnan

目标

为 postgresql 数据库 serviceDB 库创建一个只读（没有写权限）的用户。

条件

需要使用超级用户授权操作

操作步骤

进入授权的数据库

\c serviceDB

创建角色

CREATE ROLE xxx LOGIN PASSWORD 'yyy';

注意：xxx是用户名，yyy是密码。

为角色加只读权限

GRANT SELECT ON ALL TABLES IN SCHEMA public TO ruser;

调整默认权限

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO ruser;

注意，因为作用在现在已有的表上，之后可能会添加新的表，所以要改一下默认权限。

验证

psql -h postgresql_IP -U ruser -d postgres
Password for user postgres:
psql (9.5.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

扩展

常用命令

列出数据库

postgres=> \l
#...省略
serviceDB   | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
(4 rows)

当前数据库

postgres=> \conninfo
You are connected to database "postgres" as user "ruser" on host "postgres_IP" at port "5432".
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

切换数据库

postgres=> \c serviceDB
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "serviceDB" as user "ruser".
serviceDB=>

列出角色

serviceDB=> \du
List of roles
Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
ruser     |                                                            | {}

列出所有表

serviceDB=> \dt
                        List of relations
Schema |                Name                 | Type  |  Owner  
--------+--------------------------------------+-------+----------
 public | attachment                           | table | postgres
#...省略

列出表结构

serviceDB=> \d attachment
                    Table "public.attachment"
   Column    |           Type           |       Modifiers        
-------------+--------------------------+------------------------
 uid         | character varying(64)    | not null
 file_name   | character varying(200)   | not null
#...省略 
Indexes:
#...省略 
"attachment_object_uid_idx" btree (object_uid)

列出表空间

serviceDB=> \db attachment
   List of tablespaces
 Name | Owner | Location
------+-------+----------
(0 rows)

列出表权限

serviceDB=> \dp attachment
                                   Access privileges
 Schema |    Name    | Type  |     Access privileges     | Column privileges | Policies
--------+------------+-------+---------------------------+-------------------+----------
 public | attachment | table | postgres=arwdDxt/postgres+|                   |
        |            |       | ruser=r/postgres          |                   |
(1 row)