PostgreSQL：使用者角色管理

Ryan_Bai發表於2020-12-14
SQL

建立使用者/角色

建立好使用者（角色）之後需要連線的話，還需要修改 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：被忽略，但是為向後相容性而存在。

示例

  1. 建立不需要密碼登陸的使用者zjy：

    postgres=# CREATE ROLE zjy LOGIN;
CREATE ROLE

    建立該使用者後，還不能直接登入。需要修改 pg_hba.conf 檔案（後面會對該檔案進行說明），加入：

    ①：本地登陸：local  all  all  trust

    ②：遠端登陸：host  all  all  192.168.163.132/32   trust

  2. 建立需要密碼登陸的使用者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

  3. 建立有時間限制的使用者 zjy2：

    postgres=# CREATE ROLE zjy2 WITH LOGIN PASSWORD 'zjy2' VALID UNTIL '2019-05-30';
CREATE ROLE

    和 2 的處理方法一樣，修改 pg_hba.conf 檔案，該使用者會的密碼在給定的時間之後過期不可用。

  4. 建立有建立資料庫和管理角色許可權的使用者 admin：

    postgres=# CREATE ROLE admin WITH CREATEDB CREATEROLE;
CREATE ROLE

    注意：擁有建立資料庫，角色的使用者，也可以刪除和修改這些物件。

  5. 建立具有超級許可權的使用者：admin

    postgres=# CREATE ROLE admin WITH SUPERUSER LOGIN PASSWORD 'admin';
CREATE ROLE

  6. 建立複製賬號：repl 

    postgres=# CREATE USER repl REPLICATION LOGIN ENCRYPTED PASSWORD 'repl';
CREATE ROLE

  7. 其他說明

    -- 建立複製使用者
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;

檢視使用者屬性

  1. 檢視當前使用者

    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)

  2. 檢視使用者許可權

    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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章