PostgreSQL Role Management

PostgreSQL 数据库管理

首先需要知道Pg的数据库逻辑分层1. Database -> 2. Schema -> 3. Table; Pg 的用户有1.Superuser 2. User Group 3. User

1. 创建用户

create role name (create role 后面可以有很多options, 下面举一些例子 )

1. create role name login (用户可以connect database, default create cannot login; CREATE USER is equivalent to CREATE ROLE WITH LOGIN)

2. create role name with login createdb createrole (用户可以create role and create db )

3. create role name with login password 'string'

4. alter role name password string

2. 创建Group

(这里我们创建group:test, 以及两个role: dev1, dev2)

1. create role user_group

2. create role dev1 with login

3. create role dev2 with login

4. grant test to dev1 (向test添加成员)

5. grant test to dev2

   lmy=# \du
                                      List of roles
    Role name |                         Attributes                         | Member of 
   -----------+------------------------------------------------------------+-----------
    dev1      |                                                            | {test}
    dev2      |                                                            | {test}
    lmy       | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
    test      | Cannot login                                               | {}

6. revoke test from dev2 (从test移出成员)

   lmy=# revoke test from dev2;
   REVOKE ROLE
   lmy=# \du
                                      List of roles
    Role name |                         Attributes                         | Member of 
   -----------+------------------------------------------------------------+-----------
    dev1      |                                                            | {test}
    dev2      |                                                            | {}
    lmy       | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
    test      | Cannot login

7. Group 的设计就是为了方便权限的管理, 所以成员可以继承group的一些权限

   属性: superuser createdb createrole login password 是不会被继承的

   1. grant all on schema.table to role

   2. grant all on all tables in schema schema to role

   3. revoke all on schema.table to role

   4. revoke all on all tables in schema schema to role

Database 管理

1. Pg的database 默认是任意可以login 的role 都可以access, 若要进行限制
   REVOKE connect ON DATABASE database FROM PUBLIC;