pg數(shù)據(jù)庫進行用戶授權(quán)

方式一：通過navicat進行簡單界面式操作（此處不講解）

方式二：命令方式

• 創(chuàng)建用戶角色

此處說明，在pg庫里面用戶和角色的概念其實無區(qū)別

CREATE USER/ROLE name [ [ WITH ] option [ ... ] ]  : 關(guān)鍵詞 USER,ROLE； name 用戶或角色名； 
where option can be:
      SUPERUSER | NOSUPERUSER      :超級權(quán)限，擁有所有權(quán)限，默認(rèn)nosuperuser。
    | CREATEDB | NOCREATEDB        :建庫權(quán)限，默認(rèn)nocreatedb。
    | CREATEROLE | NOCREATEROLE    :建角色權(quán)限，擁有創(chuàng)建、修改、刪除角色，默認(rèn)nocreaterole。
    | INHERIT | NOINHERIT          :繼承權(quán)限，可以把除superuser權(quán)限繼承給其他用戶/角色，默認(rèn)inherit。
    | LOGIN | NOLOGIN              :登錄權(quán)限，作為連接的用戶，默認(rèn)nologin，除非是create user（默認(rèn)登錄）。
    | REPLICATION | NOREPLICATION  :復(fù)制權(quán)限，用于物理或則邏輯復(fù)制（復(fù)制和刪除slots），默認(rèn)是noreplication。
    | BYPASSRLS | NOBYPASSRLS      :安全策略RLS權(quán)限，默認(rèn)nobypassrls。
    | CONNECTION LIMIT connlimit   :限制用戶并發(fā)數(shù)，默認(rèn)-1，不限制。正常連接會受限制，后臺連接和prepared事務(wù)不受限制。
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL :設(shè)置密碼，密碼僅用于有l(wèi)ogin屬性的用戶，不使用密碼身份驗證，則可以省略此選項。可以選擇將空密碼顯式寫為PASSWORD NULL。
                                                         加密方法由配置參數(shù)password_encryption確定，密碼始終以加密方式存儲在系統(tǒng)目錄中。
    | VALID UNTIL 'timestamp'      :密碼有效期時間，不設(shè)置則用不失效。
    | IN ROLE role_name [, ...]    :新角色將立即添加為新成員。
    | IN GROUP role_name [, ...]   :同上
    | ROLE role_name [, ...]       :ROLE子句列出一個或多個現(xiàn)有角色，這些角色自動添加為新角色的成員。 （這實際上使新角色成為“組”）。
    | ADMIN role_name [, ...]      :與ROLE類似，但命名角色將添加到新角色WITH ADMIN OPTION，使他們有權(quán)將此角色的成員資格授予其他人。
    | USER role_name [, ...]       :同上
    | SYSID uid                    :被忽略，但是為向后兼容性而存在。

• 授權(quán)命令

 
 
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]
 
##單表授權(quán)：授權(quán)test賬號可以訪問schema為test的t1表
grant select,insert,update,delete on test.t1 to test;
##所有表授權(quán)：
##shema說明：選擇的模式為public，我將public模式下的所有表的增刪改查授權(quán)給角色名為test的角色
grant select,insert,update,delete on all tables in schema "public" to test;
 
 
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
    ON [ TABLE ] table_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
 
 
GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, ...]
         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]
 
##序列（自增鍵）屬性授權(quán)，指定test schema下的seq_id_seq 給test用戶
grant select,update on sequence test.seq_id_seq to test;
##序列（自增鍵）屬性授權(quán)，給用戶test授權(quán)test schema下的所有序列
grant select,update on all sequences in schema "public" to test;
 
 
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
 
 
GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON DOMAIN domain_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
 
##
GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN DATA WRAPPER fdw_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
##
 
GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN SERVER server_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
##
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON { { FUNCTION | PROCEDURE | ROUTINE } routine_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
         | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]
##
 
 
GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON LANGUAGE lang_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
##
 
GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
    ON LARGE OBJECT loid [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]##
 
GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
 
##連接模式權(quán)限，授權(quán)test訪問public模式權(quán)限
grant usage on schema "public" to test;
 
GRANT { CREATE | ALL [ PRIVILEGES ] }
    ON TABLESPACE tablespace_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
 
GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON TYPE type_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
 
where role_specification can be:
 
    [ GROUP ] role_name
  | PUBLIC
  | CURRENT_USER
  | SESSION_USER
 
GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]

• 授權(quán)參數(shù)說明

權(quán)限說明：
SELECT：允許從指定表，視圖或序列的任何列或列出的特定列進行SELECT。也允許使用COPY TO。在UPDATE或DELETE中引用現(xiàn)有列值也需要此權(quán)限。對于序列，此權(quán)限還允許使用currval函數(shù)。對于大對象，此權(quán)限允許讀取對象。

INSERT：允許將新行INSERT到指定的表中。如果列出了特定列，則只能在INSERT命令中為這些列分配（因此其他列將接收默認(rèn)值）。也允許COPY FROM。

UPDATE：允許更新指定表的任何列或列出的特定列，需要SELECT權(quán)限。

DELETE：允許刪除指定表中的行，需要SELECT權(quán)限。

TRUNCATE：允許在指定的表上創(chuàng)建觸發(fā)器。

REFERENCES：允許創(chuàng)建引用指定表或表的指定列的外鍵約束。

TRIGGER：允許在指定的表上創(chuàng)建觸發(fā)器。

CREATE：對于數(shù)據(jù)庫，允許在數(shù)據(jù)庫中創(chuàng)建新的schema、table、index。

CONNECT：允許用戶連接到指定的數(shù)據(jù)庫。在連接啟動時檢查此權(quán)限。

TEMPORARY、TEMP：允許在使用指定數(shù)據(jù)庫時創(chuàng)建臨時表。

EXECUTE：允許使用指定的函數(shù)或過程以及在函數(shù)。

USAGE：對于schema，允許訪問指定模式中包含的對象；對于sequence，允許使用currval和nextval函數(shù)。對于類型和域，允許在創(chuàng)建表，函數(shù)和其他模式對象時使用類型或域。

ALL PRIVILEGES：一次授予所有可用權(quán)限。

• 權(quán)限撤銷

 
REVOKE [ GRANT OPTION FOR ]
   { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
   [, ...] | ALL [ PRIVILEGES ] }
   ON { [ TABLE ] table_name [, ...]
        | ALL TABLES IN SCHEMA schema_name [, ...] }
   FROM { [ GROUP ] role_name | PUBLIC } [, ...]
   [ CASCADE | RESTRICT ]
 
##移除用戶test在schema public上所有表的select權(quán)限
revoke select on all tables in schema "public" from test;
 
 
REVOKE [ GRANT OPTION FOR ]
   { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
   [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
   ON [ TABLE ] table_name [, ...]
   FROM { [ GROUP ] role_name | PUBLIC } [, ...]
   [ CASCADE | RESTRICT ]
 
##移除用戶test在 public模式的t1表的id列的查詢權(quán)限
revoke select (id) on public.t1 from test;
 
 
REVOKE [ GRANT OPTION FOR ]
   { { USAGE | SELECT | UPDATE }
   [, ...] | ALL [ PRIVILEGES ] }
   ON { SEQUENCE sequence_name [, ...]
        | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
   FROM { [ GROUP ] role_name | PUBLIC } [, ...]
   [ CASCADE | RESTRICT ]
##序列
 
 
REVOKE [ GRANT OPTION FOR ]
   { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
   ON DATABASE database_name [, ...]
   FROM { [ GROUP ] role_name | PUBLIC } [, ...]
   [ CASCADE | RESTRICT ]
##庫
 
 
REVOKE [ GRANT OPTION FOR ]
   { USAGE | ALL [ PRIVILEGES ] }
   ON DOMAIN domain_name [, ...]
   FROM { [ GROUP ] role_name | PUBLIC } [, ...]
   [ CASCADE | RESTRICT]
##
 
 
REVOKE [ GRANT OPTION FOR ]
   { USAGE | ALL [ PRIVILEGES ] }
   ON FOREIGN DATA WRAPPER fdw_name [, ...]
   FROM { [ GROUP ] role_name | PUBLIC } [, ...]
   [ CASCADE | RESTRICT]
##
 
REVOKE [ GRANT OPTION FOR ]
   { USAGE | ALL [ PRIVILEGES ] }
   ON FOREIGN SERVER server_name [, ...]
   FROM { [ GROUP ] role_name | PUBLIC } [, ...]
   [ CASCADE | RESTRICT]
##
 
 
REVOKE [ GRANT OPTION FOR ]
   { EXECUTE | ALL [ PRIVILEGES ] }
   ON { { FUNCTION | PROCEDURE | ROUTINE } function_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
        | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
   FROM { [ GROUP ] role_name | PUBLIC } [, ...]
   [ CASCADE | RESTRICT ]
##
REVOKE [ GRANT OPTION FOR ]
   { USAGE | ALL [ PRIVILEGES ] }
   ON LANGUAGE lang_name [, ...]
   FROM { [ GROUP ] role_name | PUBLIC } [, ...]
   [ CASCADE | RESTRICT ]
##
 
 
REVOKE [ GRANT OPTION FOR ]
   { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
   ON LARGE OBJECT loid [, ...]
   FROM { [ GROUP ] role_name | PUBLIC } [, ...]
   [ CASCADE | RESTRICT ]
##
 
 
REVOKE [ GRANT OPTION FOR ]
   { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
   ON SCHEMA schema_name [, ...]
   FROM { [ GROUP ] role_name | PUBLIC } [, ...]
   [ CASCADE | RESTRICT ]
##schema權(quán)限
 
 
REVOKE [ GRANT OPTION FOR ]
   { CREATE | ALL [ PRIVILEGES ] }
   ON TABLESPACE tablespace_name [, ...]
   FROM { [ GROUP ] role_name | PUBLIC } [, ...]
   [ CASCADE | RESTRICT ]
##
 
 
REVOKE [ GRANT OPTION FOR ]
   { USAGE | ALL [ PRIVILEGES ] }
   ON TYPE type_name [, ...]
   FROM { [ GROUP ] role_name | PUBLIC } [, ...]
   [ CASCADE | RESTRICT ]
##
REVOKE [ ADMIN OPTION FOR ]
   role_name [, ...] FROM role_name [, ...]
   [ CASCADE | RESTRICT ]

• 如果用戶需要創(chuàng)建表的權(quán)限對該角色進行全部授權(quán)之后，發(fā)現(xiàn)建表的時候報錯說什么當(dāng)前用戶是一個readonly的會話可以進行下面操作進行修改

ALTER USER xx set default_transaction_read_only=NO --修改用戶的會話不是只讀

參考連接：https://blog.csdn.net/eagle89/article/details/112169903文章來源地址http://www.zghlxwxcb.cn/news/detail-503564.html

到了這里，關(guān)于pg數(shù)據(jù)庫授權(quán)的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！