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;