简单权限管理

• 创建角色
• 权限赋予

角色是拥有数据库对象和权限的实体。在不同的环境中角色可以认为是一个用户，一个组或者兼顾两者。

• 在数据库中添加一个新角色，角色无登录权限。
• 创建角色的用户必须具备CREATE ROLE的权限或者是系统管理员。

语法格式

其中角色信息设置子句option语法为：

    | {AUDITADMIN | NOAUDITADMIN}
    | {CREATEDB | NOCREATEDB}
    | {USEFT | NOUSEFT}
    | {CREATEROLE | NOCREATEROLE}
    | {INHERIT | NOINHERIT}
    | {LOGIN | NOLOGIN}
    | {REPLICATION | NOREPLICATION}
    | {INDEPENDENT | NOINDEPENDENT}
    | {VCADMIN | NOVCADMIN}
    | CONNECTION LIMIT connlimit
    | VALID BEGIN 'timestamp'
    | VALID UNTIL 'timestamp'
    | RESOURCE POOL 'respool'
    | PERM SPACE 'spacelimit'
    | TEMP SPACE 'tmpspacelimit'
    | SPILL SPACE 'spillspacelimit'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN rol e_name [, ...]
    | USER role_name [, ...]
    | SYSID uid
    | DEFAULT TABLESPACE tablespace_name
    | PROFILE DEFAULT
    | PROFILE profile_name
    | PGUSER

--创建一个角色，名为manager，密码为Bigdata@123。
postgres=# CREATE ROLE manager IDENTIFIED BY 'Bigdata@123';
--创建一个角色，从2015年1月1日开始生效，到2026年1月1日失效。
postgres=# CREATE ROLE miriam WITH LOGIN PASSWORD 'Bigdata@123' VALID BEGIN '2015-01-01' VALID UNTIL '2026-01-01';
--修改角色manager的密码为abcd@123。
postgres=# ALTER ROLE manager IDENTIFIED BY 'abcd@123' REPLACE 'Bigdata@123';
--修改角色manager为系统管理员。
postgres=# ALTER ROLE manager SYSADMIN;
--删除角色manager。
postgres=# DROP ROLE manager;
--删除角色miriam。
postgres=# DROP ROLE miriam;

创建一个用户。

注意事项

• 通过CREATE USER创建的用户，默认具有LOGIN权限；
• 通过CREATE USER创建用户的同时系统会在执行该命令的数据库中，为该用户创建一个同名的SCHEMA；其他数据库中，则不自动创建同名的SCHEMA；用户可使用CREATE SCHEMA命令，分别在其他数据库中，为该用户创建同名SCHEMA。
• 系统管理员在普通用户同名schema下创建的对象，所有者为schema的同名用户（非系统管理员）。

CREATE USER user_name [ [ WITH ] option [ ... ] ] [ ENCRYPTED | UNENCRYPTED ] { PASSWORD | IDENTIFIED BY } { 'password' | DISABLE };

其中option子句用于设置权限及属性等信息。

{SYSADMIN | NOSYSADMIN}
    | {AUDITADMIN | NOAUDITADMIN}
    | {CREATEDB | NOCREATEDB}
    | {USEFT | NOUSEFT}
    | {CREATEROLE | NOCREATEROLE}
    | {INHERIT | NOINHERIT}
    | {REPLICATION | NOREPLICATION}
    | {INDEPENDENT | NOINDEPENDENT}
    | {VCADMIN | NOVCADMIN}
    | CONNECTION LIMIT connlimit
    | VALID BEGIN 'timestamp'
    | RESOURCE POOL 'respool'
    | PERM SPACE 'spacelimit'
    | TEMP SPACE 'tmpspacelimit'
    | SPILL SPACE 'spillspacelimit'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid
    | DEFAULT TABLESPACE tablespace_name
    | PROFILE DEFAULT
    | PROFILE profile_name
    | PGUSER

示例

--创建用户jim，登录密码为Bigdata@123。
postgres=# CREATE USER jim PASSWORD 'Bigdata@123';
--下面语句与上面的等价。
postgres=# CREATE USER kim IDENTIFIED BY 'Bigdata@123';
--如果创建有“创建数据库”权限的用户，则需要加CREATEDB关键字。
postgres=# CREATE USER dim CREATEDB PASSWORD 'Bigdata@123';
--将用户jim的登录密码由Bigdata@123修改为Abcd@123。
postgres=# ALTER USER jim IDENTIFIED BY 'Abcd@123' REPLACE 'Bigdata@123';
--为用户jim追加CREATEROLE权限。
postgres=# ALTER USER jim CREATEROLE;
--将enable_seqscan的值设置为on， 设置成功后，在下一会话中生效。
postgres=# ALTER USER jim SET enable_seqscan TO on;
--重置jim的enable_seqscan参数。
postgres=# ALTER USER jim RESET enable_seqscan;
--锁定jim帐户。
postgres=# ALTER USER jim ACCOUNT LOCK;
--删除用户。
postgres=# DROP USER kim CASCADE;
postgres=# DROP USER jim CASCADE;
postgres=# DROP USER dim CASCADE;

对角色和用户进行授权操作。

使用GRANT命令进行用户授权包括以下三种场景：

• 将系统权限授权给角色或用户

  系统权限又称为用户属性，包括SYSADMIN、CREATEDB、CREATEROLE、AUDITADMIN和LOGIN。

• 将数据库对象授权给角色或用户

  将数据库对象（表和视图、指定字段、数据库、函数、模式、表空间等）的相关权限授予特定角色或用户；

• 将角色或用户的权限授权给其他角色或用户

  将一个角色或用户的权限授予一个或多个其他角色或用户。在这种情况下，每个角色或用户都可视为拥有一个或多个数据库权限的集合。

• 将表或视图的访问权限赋予指定的用户或角色。

  GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES } [, ...] 
        | ALL [ PRIVILEGES ] }
      ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
      TO { [ GROUP ] role_name | PUBLIC } [, ...] 
      [ WITH GRANT OPTION ];

• 将表中字段的访问权限赋予指定的用户或角色。

  GRANT { {{ SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )} [, ...] 
        | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
      ON [ TABLE ] table_name [, ...]
      TO { [ GROUP ] role_name | PUBLIC } [, ...]
      [ WITH GRANT OPTION ];

• GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...]
        | ALL [ PRIVILEGES ] }
      ON DATABASE database_name [, ...]
      [ WITH GRANT OPTION ];

• 将外部数据源的访问权限赋予给指定的用户或角色。

• 将外部服务器的访问权限赋予给指定的用户或角色。

  GRANT { USAGE | ALL [ PRIVILEGES ] }
      ON FOREIGN SERVER server_name [, ...]
      TO { [ GROUP ] role_name | PUBLIC } [, ...]
      [ WITH GRANT OPTION ];

• 将函数的访问权限赋予给指定的用户或角色。

  GRANT { EXECUTE | ALL [ PRIVILEGES ] }
      ON { FUNCTION {function_name ( [ {[ argmode ] [ arg_name ] arg_type} [, ...] ] )} [, ...]
         | ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
      TO { [ GROUP ] role_name | PUBLIC } [, ...]
      [ WITH GRANT OPTION ];

• 将过程语言的访问权限赋予给指定的用户或角色。

  GRANT { USAGE | ALL [ PRIVILEGES ] }
      ON LANGUAGE lang_name [, ...]
      TO { [ GROUP ] role_name | PUBLIC } [, ...]
      [ WITH GRANT OPTION ];

• 将大对象的访问权限赋予指定的用户或角色。

  GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
      ON LARGE OBJECT loid [, ...]
      TO { [ GROUP ] role_name | PUBLIC } [, ...]
      [ WITH GRANT OPTION ];

• 将模式的访问权限赋予指定的用户或角色。

  GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
      ON SCHEMA schema_name [, ...]
      TO { [ GROUP ] role_name | PUBLIC } [, ...]
      [ WITH GRANT OPTION ];

• 将表空间的访问权限赋予指定的用户或角色。

  GRANT { CREATE | ALL [ PRIVILEGES ] }
      ON TABLESPACE tablespace_name [, ...]
      TO { [ GROUP ] role_name | PUBLIC } [, ...]
      [ WITH GRANT OPTION ];

• 将类型的访问权限赋予指定的用户或角色。

  GRANT { USAGE | ALL [ PRIVILEGES ] }
      ON TYPE type_name [, ...]
      TO { [ GROUP ] role_name | PUBLIC } [, ...]
      [ WITH GRANT OPTION ];

• 将角色的权限赋予其他用户或角色的语法。

  GRANT role_name [, ...]
     TO role_name [, ...]
     [ WITH ADMIN OPTION ];

• GRANT ALL { PRIVILEGES | PRIVILEGE }
     TO role_name;

示例

示例：将系统权限授权给用户或者角色。

创建名为joe的用户，并将sysadmin权限授权给他。

授权成功后，用户joe会拥有sysadmin的所有权限。

示例：将对象权限授权给用户或者角色。

1. 撤销joe用户的sysadmin权限，然后将模式tpcds的使用权限和表tpcds.reason的所有权限授权给用户joe。

   postgres=# REVOKE ALL PRIVILEGES FROM joe;
   postgres=# GRANT USAGE ON SCHEMA tpcds TO joe;
   postgres=# GRANT ALL PRIVILEGES ON tpcds.reason TO joe;

   授权成功后，joe用户就拥有了tpcds.reason表的所有权限，包括增删改查等权限。

2. 将tpcds.reason表中r_reason_sk、r_reason_id、r_reason_desc列的查询权限，r_reason_desc的更新权限授权给joe。

   postgres=# GRANT select (r_reason_sk,r_reason_id,r_reason_desc),update (r_reason_desc) ON tpcds.reason TO joe;

   授权成功后，用户joe对tpcds.reason表中r_reason_sk，r_reason_id的查询权限会立即生效。如果joe用户需要拥有将这些权限授权给其他用户的权限，可以通过以下语法对joe用户进行授权。

   postgres=# GRANT select (r_reason_sk, r_reason_id) ON tpcds.reason TO joe WITH GRANT OPTION;

   将数据库postgres的连接权限授权给用户joe，并给予其在postgres中创建schema的权限，而且允许joe将此权限授权给其他用户。

   postgres=# GRANT create,connect on database postgres TO joe WITH GRANT OPTION;

   创建角色tpcds_manager，将模式tpcds的访问权限授权给角色tpcds_manager，并授予该角色在tpcds下创建对象的权限，不允许该角色中的用户将权限授权给其他人。

   postgres=# CREATE ROLE tpcds_manager PASSWORD 'Bigdata@123';
   postgres=# GRANT USAGE,CREATE ON SCHEMA tpcds TO tpcds_manager;

   将表空间tpcds_tbspc的所有权限授权给用户joe，但用户joe无法将权限继续授予其他用户。

   postgres=# CREATE TABLESPACE tpcds_tbspc RELATIVE LOCATION 'tablespace/tablespace_1';
   postgres=# GRANT ALL ON TABLESPACE tpcds_tbspc TO joe;

示例：将用户或者角色的权限授权给其他用户或角色。

1. 创建角色manager，将joe的权限授权给manager，并允许该角色将权限授权给其他人。

   postgres=# CREATE ROLE manager PASSWORD 'Bigdata@123';
   postgres=# GRANT joe TO manager WITH ADMIN OPTION;

2. 创建用户senior_manager，将用户manager的权限授权给该用户。

   postgres=# CREATE ROLE senior_manager PASSWORD 'Bigdata@123';
   postgres=# GRANT manager TO senior_manager;

3. postgres=# REVOKE manager FROM joe;
   postgres=# REVOKE senior_manager FROM manager;
   postgres=# DROP USER manager;

示例：撤销上述授予的权限，并清理角色和用户。