PostgreSQL：使用者角色管理
建立使用者/角色
建立好使用者（角色）之後需要連線的話，還需要修改 2 個許可權控制的配置檔案（pg_hba.conf、pg_ident.conf）。並且建立使用者（user）和建立角色（role）一樣，唯一的區別是使用者預設可以登入，而建立的角色預設不能登入。建立使用者和角色的各個引數選項是一樣的。
語法
CREATE USER/ROLE name [ [ WITH ] option [ ... ] ] : 關鍵詞 USER,ROLE； name 使用者或角色名； where option can be: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | INHERIT | NOINHERIT | LOGIN | NOLOGIN | REPLICATION | NOREPLICATION | BYPASSRLS | NOBYPASSRLS | CONNECTION LIMIT connlimit | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL | VALID UNTIL 'timestamp' | IN ROLE role_name [, ...] | IN GROUP role_name [, ...] | ROLE role_name [, ...] | ADMIN role_name [, ...] | USER role_name [, ...] | SYSID uid
-
SUPERUSER | NOSUPERUSER：超級許可權，擁有所有許可權，預設nosuperuser。
-
CREATEDB | NOCREATEDB：建庫許可權，預設 nocreatedb。
-
CREATEROLE | NOCREATEROLE：建角色許可權，擁有建立、修改、刪除角色，預設nocreaterole。
-
INHERIT | NOINHERIT：繼承許可權，可以把除 superuser 許可權繼承給其他使用者/角色，預設inherit。
-
LOGIN | NOLOGIN：登入許可權，作為連線的使用者，預設 nologin，除非是create user（預設登入）。
-
REPLICATION | NOREPLICATION：複製許可權，用於物理或則邏輯複製（複製和刪除slots），預設是 noreplication。
-
BYPASSRLS | NOBYPASSRLS：安全策略RLS許可權，預設 nobypassrls。
-
CONNECTION LIMIT connlimit：限制使用者併發數，預設-1，不限制。正常連線會受限制，後臺連線和 prepared 事務不受限制。
-
[ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL：設定密碼，密碼僅用於有 login 屬性的使用者，不使用密碼身份驗證，則可以省略此選項。可以選擇將空密碼顯式寫為 PASSWORD NULL。
加密方法由配置引數 password_encryption 確定，密碼始終以加密方式儲存在系統目錄中。
-
VALID UNTIL 'timestamp'：密碼有效期時間，不設定則用不失效。
-
IN ROLE role_name [, ...]：新角色將立即新增為新成員。
-
IN GROUP role_name [, ...]：與 IN ROLE 相同，是已過時的語法。
-
ROLE role_name [, ...]：ROLE 子句列出一個或多個現有角色，這些角色自動新增為新角色的成員。 （這實際上使新角色成為“組”）。
-
ADMIN role_name [, ...]：與 ROLE 類似，但命名角色將新增到新角色 WITH ADMIN OPTION，使他們有權將此角色的成員資格授予其他人。
-
USER role_name [, ...]：與 ROLE 子句相同，是已過時的語法。
-
SYSID uid：被忽略，但是為向後相容性而存在。
示例
-
建立不需要密碼登陸的使用者zjy：
postgres=# CREATE ROLE zjy LOGIN; CREATE ROLE
建立該使用者後，還不能直接登入。需要修改 pg_hba.conf 檔案（後面會對該檔案進行說明），加入：
①：本地登陸：local all all trust
②：遠端登陸：host all all 192.168.163.132/32 trust
-
建立需要密碼登陸的使用者zjy1：
postgres=# CREATE USER zjy1 WITH PASSWORD 'zjy1'; CREATE ROLE
和ROLE的區別是：USER帶LOGIN屬性。也需要修改 pg_hba.conf 檔案，加入：
host all all 192.168.163.132/32 md5
-
建立有時間限制的使用者 zjy2：
postgres=# CREATE ROLE zjy2 WITH LOGIN PASSWORD 'zjy2' VALID UNTIL '2019-05-30'; CREATE ROLE
和 2 的處理方法一樣，修改 pg_hba.conf 檔案，該使用者會的密碼在給定的時間之後過期不可用。
-
建立有建立資料庫和管理角色許可權的使用者 admin：
postgres=# CREATE ROLE admin WITH CREATEDB CREATEROLE; CREATE ROLE
注意：擁有建立資料庫，角色的使用者，也可以刪除和修改這些物件。
-
建立具有超級許可權的使用者：admin
postgres=# CREATE ROLE admin WITH SUPERUSER LOGIN PASSWORD 'admin'; CREATE ROLE
-
建立複製賬號：repl
postgres=# CREATE USER repl REPLICATION LOGIN ENCRYPTED PASSWORD 'repl'; CREATE ROLE
-
其他說明
-- 建立複製使用者 CREATE USER abc REPLICATION LOGIN ENCRYPTED PASSWORD ''; CREATE USER abc REPLICATION LOGIN ENCRYPTED PASSWORD 'abc'; ALTER USER work WITH ENCRYPTED password ''; -- 建立 scheme 角色 CREATE ROLE abc; CREATE DATABASE abc WITH OWNER abc ENCODING UTF8 TEMPLATE template0; \c abc -- 建立schema CREATE SCHEMA abc; ALTER SCHEMA abc OWNER to abc; revoke create on schema public from public; -- 建立使用者 create user abc with ENCRYPTED password ''; GRANT abc to abc; ALTER ROLE abc WITH abc; -- 建立讀寫賬號 CREATE ROLE abc_rw; CREATE ROLE abc_rr; -- 賦予訪問資料庫許可權，schema許可權 grant connect ON DATABASE abc to abc_rw; GRANT USAGE ON SCHEMA abc TO abc_rw; -- 賦予讀寫許可權 grant select,insert,update,delete ON ALL TABLES IN SCHEMA abc to abc; -- 賦予序列許可權 GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA abc to abc; -- 賦予預設許可權 ALTER DEFAULT PRIVILEGES IN SCHEMA abc GRANT select,insert,update,delete ON TABLES TO abc; -- 賦予序列許可權 ALTER DEFAULT PRIVILEGES IN SCHEMA abc GRANT ALL PRIVILEGES ON SEQUENCES TO abc; -- 使用者對db要有連線許可權 grant connect ON DATABASE abc to abc; -- 使用者要對schema usage 許可權，不然要select * from schema_name.table ,不能用搜尋路徑 GRANT USAGE ON SCHEMA abc TO abc; grant select ON ALL TABLES IN SCHEMA abc to abc; ALTER DEFAULT PRIVILEGES IN SCHEMA abc GRANT select ON TABLES TO abc; create user abc_w with ENCRYPTED password ''; create user abc_r with ENCRYPTED password ''; GRANT abc_rw to abc_w； GRANT abc_rr to abc_r;
修改使用者屬性
語法
ALTER USER role_specification [ WITH ] option [ ... ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
| VALID UNTIL 'timestamp'
ALTER USER name RENAME TO new_name
ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter
ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL
where role_specification can be:
role_name
| CURRENT_USER
| SESSION_USER
示例
option 選項裡的使用者都可以透過 alter role 進行修改
-
修改使用者為超級/非超級使用者
alter role caocao with superuser/nosuperuser;
-
修改使用者為可/不可登陸使用者
alter role caocao with nologin/login;
-
修改使用者名稱
alter role caocao rename to youxing;
-
修改使用者密碼，移除密碼用 NULL
alter role youxing with password 'youxing';
-
修改使用者引數，該使用者登陸後的以該引數為準
alter role zjy in database zjy SET geqo to 0/default;
檢視使用者屬性
-
檢視當前使用者
zjy=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- admin | Superuser, Cannot login | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} zjy | | {} zjy=# select * from pg_roles; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid ----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+------- pg_signal_backend | f | t | f | f | f | f | -1 | ******** | | f | | 4200 postgres | t | t | t | t | t | t | -1 | ******** | | t | | 10 admin | t | t | f | f | f | f | -1 | ******** | | f | | 16456 pg_read_all_stats | f | t | f | f | f | f | -1 | ******** | | f | | 3375 zjy | f | t | f | f | t | f | -1 | ******** | | f | | 16729 pg_monitor | f | t | f | f | f | f | -1 | ******** | | f | | 3373 pg_read_all_settings | f | t | f | f | f | f | -1 | ******** | | f | | 3374 pg_stat_scan_tables | f | t | f | f | f | f | -1 | ******** | | f | | 3377 (8 rows) -
檢視使用者許可權
zjy=# select * from information_schema.table_privileges where grantee='zjy'; grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy ----------+---------+---------------+--------------+------------+----------------+--------------+---------------- postgres | zjy | zjy | zjy | zjy | INSERT | NO | NO postgres | zjy | zjy | zjy | zjy | SELECT | NO | YES postgres | zjy | zjy | zjy | zjy | UPDATE | NO | NO postgres | zjy | zjy | zjy | zjy | DELETE | NO | NO postgres | zjy | zjy | zjy | zjy1 | INSERT | NO | NO postgres | zjy | zjy | zjy | zjy1 | SELECT | NO | YES postgres | zjy | zjy | zjy | zjy1 | UPDATE | NO | NO postgres | zjy | zjy | zjy | zjy1 | DELETE | NO | NO postgres | zjy | zjy | zjy | zjy2 | INSERT | NO | NO postgres | zjy | zjy | zjy | zjy2 | SELECT | NO | YES postgres | zjy | zjy | zjy | zjy2 | UPDATE | NO | NO postgres | zjy | zjy | zjy | zjy2 | DELETE | NO | NO postgres | zjy | zjy | zjy | zjy3 | INSERT | NO | NO postgres | zjy | zjy | zjy | zjy3 | SELECT | NO | YES postgres | zjy | zjy | zjy | zjy3 | UPDATE | NO | NO postgres | zjy | zjy | zjy | zjy3 | DELETE | NO | NO
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31490526/viewspace-2742181/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- PostgreSQL技術大講堂 - Part 6：PG使用者與角色管理SQL
- Oracle使用者角色許可權管理Oracle
- 使用者角色許可權管理架構架構
- PostgreSQL資料庫使用者許可權管理SQL資料庫
- RabbitMQ使用教程（二）RabbitMQ使用者管理,角色管理及許可權設定MQ
- mongodb使用者與角色使用MongoDB
- PostgreSQL：Schema 管理SQL
- 3,角色管理新增頁面
- AWS EKS 新增IAM使用者角色
- 關於SQL Server資料庫中的使用者許可權和角色管理SQLServer資料庫
- Oracle建立使用者、角色、授權、建表Oracle
- PostgreSQL：許可權管理SQL
- PostgreSQL建立只讀使用者SQL
- PostgreSQL的單使用者模式SQL模式
- 【ASP.NET Core】按使用者角色授權ASP.NET
- Rbac使用者角色許可權表設計
- MySQL5.7&8.0許可權-角色管理MySql
- 【PG管理】postgresql資料庫管理相關SQL資料庫
- postgresql 資料庫基本管理SQL資料庫
- 【clickhouse專欄】新建庫角色使用者初始化
- 淺談PostgreSQL使用者許可權SQL
- Laravel——使用者角色許可權控制包 Laravel-permissionLaravel
- 使用者可在Messenger內讀書並與角色互動Messenger
- 使用者管理
- 玩家角色——角色跳躍
- 如何檢視postgresql使用者許可權SQL
- 對不同角色使用者設定不同的系統首頁
- 使用者許可權管理之使用者與組管理
- 從“整理”到“思考”：文件管理工具的新角色
- Linux使用者管理Linux
- ARGOCD使用者管理Go
- 3、使用者管理
- oracle 使用者管理Oracle
- MySQL 使用者管理MySql
- MariaDB使用者管理
- [譯]PostgreSQL中更安全的應用使用者SQL
- Linux命令整理,使用者管理,使用者組管理,系統管理,目錄管理常用命令Linux
- 通過實驗理解PG邏輯結構：1 使用者（角色）