摘要：數(shù)據(jù)庫管理首先需要知道的數(shù)據(jù)庫邏輯分層的用戶有創(chuàng)建用戶后面可以有很多下面舉一些例子用戶可以用戶可以創(chuàng)建這里我們創(chuàng)建以及兩個(gè)向添加成員

首先需要知道Pg的數(shù)據(jù)庫邏輯分層1. Database -> 2. Schema -> 3. Table; Pg 的用戶有1.Superuser 2. User Group 3. User

1. 創(chuàng)建用戶

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. 創(chuàng)建Group

(這里我們創(chuàng)建group:test, 以及兩個(gè)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 的設(shè)計(jì)就是為了方便權(quán)限的管理, 所以成員可以繼承g(shù)roup的一些權(quán)限

屬性: superuser createdb createrole login password 是不會(huì)被繼承的

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 默認(rèn)是任意可以login 的role 都可以access, 若要進(jìn)行限制
REVOKE connect ON DATABASE database FROM PUBLIC;