PostgreSQL Role Management

摘要：数据库管理首先需要知道的数据库逻辑分层的用户有创建用户后面可以有很多下面举一些例子用户可以用户可以创建这里我们创建以及两个向添加成员

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

1. 创建用户

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

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

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

create role name with login password "string"

alter role name password string

2. 创建Group

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

create role user_group

create role dev1 with login

create role dev2 with login

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

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                                               | {}

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

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

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

grant all on schema.table to role

grant all on all tables in schema schema to role

revoke all on schema.table to role

revoke all on all tables in schema schema to role

Database 管理

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