脑子进水养啥鱼?

脑子进水养啥鱼?

一、概述

1.1 角色和用户

  PostgreSQL 使用角色的概念管理数据库访问权限。角色是一系列相关权限的集合。为了管理方便,通常把一系列相关的数据库权限赋给一个角色,如果哪个用户需要这些权限,就把角色赋给相应的用户。由于用户也拥有一系列的相关权限,为了简化管理,在 PostgreSQL 中,角色与用户是没有区别的,一个用户也是一个角色,我们可以把一个用户的权限赋给另一个用户。
  用户和角色在整个数据库实例中是全局的,在同一个实例中的不同数据库中,看到的用户都是相同的。在初始化数据库系统时有一个预定义的超级用户,这个用户的名称与初始化该数据库的操作系统用户名相同。如果数据库是建在操作系统用户“postgres”(通常我们把数据库安装在此用户下)下的,那么这个数据库超级用户的名称也叫“postgres”。可以用这个超级用户连接数据库,然后创建出更多的普通用户或其他超级用户。
  在 SQL 标准中,用户和角色之间的区别很清楚,并且用户不会自动继承权限而角色会继承。这种行为在 PostgreSQL 中也可以实现:为要用作 SQL 角色的角色给予 INHERIT 属性,而为要用作 SQL 用户的角色给予 NOINHERIT 属性。不过,为了向后兼容 8.1 以前的发布(在其中用户总是拥有它们所在组的权限),PostgreSQL 默认给所有的角色 INHERIT 属性。

1.2 用户和用户组

  PostgreSQL 可以把用户分组在一起,权限可以被授予一整个组或从一整个组回收。一旦组角色存在,可以使用 GRANT 和 REVOKE 命令增加和移除成员:

GRANT group_role TO role1, ... ;
REVOKE group_role FROM role1, ... ;

注意事项

  • 一个用户可以是多个组的成员。
  • 不允许环状的成员关系。
  • 不允许把一个用户授予给 PUBLIC。

1.3 PUBLIC 角色

  PUBLIC 是 PostgreSQL 数据库中一个特殊的角色组,在元数据表(pg_roles)中都查不到该角色数据库中,所创建的角色都可以理解为是 PUBLIC 角色组成员。而且对 PUBLIC 权限的继承完全不受 NOINHERIT 的控制,一旦创建了一个拥有 login 权限的角色,它会立即继承 PUBLIC 角色组拥有的权限,此时如果想通过 revoke(比如 revoke connect on database)来回收的话不会成功,只能从 PUBLIC 组回收相关权限(比如 revoke connect on database from PUBLIC)。

PUBLIC 默认权限

  • 数据库的 connect,temp/temprary 权限。任何新建的数据库,系统会自动为 PUBLIC 角色赋予connect 和在任何 schema 下创建临时表的权限。
  • public 模式的 usage,create 权限。在任何新建的数据库的 public 模式下有 usage 和 create 的权限。
  • 函数的 execute 权限(仅限于 public 模式下)。
  • 语言和数据类型(包括域)的 usage 权限。

二、权限授予

  在 PostgreSQL 数据库中,每个数据库对象都有一个所有者(owner),对于大部分类型的对象,默认只有其所有者和超级用户(管理员用户)能够对该对象做任何事情。为了允许其他角色使用它,必须分配对应的权限。
  根据 PostgreSQL 数据库的逻辑结构:实例(集簇)->数据库->schema->数据库对象,可将其权限分为如下几类:

  • 实例权限:由 postgresql.conf,pg_hba.conf 文件控制,控制哪些用户哪些IP以哪种方式连接数据库。
  • 数据库权限:是否允许连接数据库,是否允许在数据库中创建模式(schema)。
  • 模式权限:是否允许查看模式中的对象,是否允许在模式下建表。
  • 对象权限:对表来说就是增删改查,对函数来说就是执行,修改等。
  • 系统权限:也可以称为角色属性,即创建用户时,自身携带的一些权限。

2.1 实例权限

https://xiaosonggong.blog.csdn.net/article/details/124264877

2.2 数据库权限

为已创建用户授权数据库操作权限。

grant {{create|connect|temporary|temp}|all[ privileges]} on database 数据库名 to 用户名|pubilc [with grant option];

参数说明

2.3 模式权限

为已创建用户授权模式操作权限。

grant {{create|usage}|all [privileges]} on schema 模式名 to 用户名|pubilc [with grant option];

参数说明

2.4 对象权限

为已创建用户授权基本对象操作权限。

grant {{select|insert|update|delete|truncate|references|trigger}|all [privileges]} on {[table] 表名|all tables in schema 模式名} to 用户名|pubilc [with grant option];

参数说明

2.5 系统权限

  创建用户时携带属性,创建后也可使用 alter 进行修改。create user 是 create role 的一个别名。唯一的区别是 create user 中 login 被作为默认值,而 create role 中 nologin 是默认值。

create user/role 用户名|public [with] option;
alter user/role 用户名|public [with] option;

option 可以为

三、权限查看

3.1 权限查看

--数据库权限查看
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 appdb     | appuser  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 d1        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | u1=c/postgres
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(5 rows)

--模式权限查看
postgres=# \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         |
 sch1   | postgres |                      |
(2 rows)

--对象权限查看
d1=> select * from information_schema.table_privileges where grantee = 'u1';
 grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
 u1      | u1      | d1            | d1s1         | t1         | INSERT         | YES          | NO
 u1      | u1      | d1            | d1s1         | t1         | SELECT         | YES          | YES
 u1      | u1      | d1            | d1s1         | t1         | UPDATE         | YES          | NO
 u1      | u1      | d1            | d1s1         | t1         | DELETE         | YES          | NO
 u1      | u1      | d1            | d1s1         | t1         | TRUNCATE       | YES          | NO
 u1      | u1      | d1            | d1s1         | t1         | REFERENCES     | YES          | NO
 u1      | u1      | d1            | d1s1         | t1         | TRIGGER        | YES          | NO
(7 rows)

--系统权限查看
d1=> 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
 pg_read_server_files      | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4569
 postgres                  | t        | t          | t             | t           | t           | t              |           -1 | ********    |               | t            |           |    10
 pg_write_server_files     | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4570
 pg_execute_server_program | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4571
 pg_read_all_stats         | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3375
 pg_monitor                | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3373
 syd                       | f        | t          | f             | f           | t           | f              |           -1 | ********    |               | f            |           | 16387
 u1                        | f        | t          | f             | f           | t           | f              |           -1 | ********    |               | f            |           | 41008
 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
 u2                        | f        | t          | f             | f           | t           | f              |           -1 | ********    |               | f            |           | 41009
 repmgr                    | t        | t          | f             | f           | t           | f              |           -1 | ********    |               | f            |           | 32769
(13 rows)

3.2 ACL 格式说明

上述数据库及模式权限均显示为 ACL(访问控制列表)缩写,显示格式为:

接受赋权的用户(省略时表示 PUBLIC 角色组)= 授予的权限/授予的用户

授予的权限具体含义如下

访问权限摘要

四、撤销权限

上述所有通过 grant 方式授予的权限,均可通过列语法撤销。

revoke [grant option for] {上述所有权限} from {用户名|public} [cascade|restrict];

五、默认权限修改

5.1 概述

  ALTER DEFAULT PRIVILEGES 允许设置将被应用于未来要创建的对象的特权(它不会影响分配给已经存在的对象的特权)。当前,只能修改用于模式、表(包括视图和外部表)、序列、函数和类型(包括域)的特权。其中,可设置权限的函数包括聚集函数和过程函数。当这个命令应用于函数时,单词 FUNCTIONS 和 ROUTINES 是等效的。(推荐使用 ROUTINES,因为它是用来囊括函数和过程的一个标准术语。在较早的 PostgreSQL 发行版中,只允许单词 FUNCTIONS。无法为函数或过程单独设置默认特权。)只能改变你自己或者你属于其中的角色所创建的对象的默认特权。这些特权可以对全局范围设置(即对当前数据库中创建的所有对象),或者只对在指定模式中创建的对象设置。

5.2 语法

ALTER DEFAULT PRIVILEGES
    [ FOR { ROLE | USER } target_role [, ...] ]
    [ IN SCHEMA schema_name [, ...] ]
    abbreviated_grant_or_revoke

其中abbreviated_grant_or_revoke是下列之一:

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON TABLES
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON SEQUENCES
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON { FUNCTIONS | ROUTINES }
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

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

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

REVOKE [ GRANT OPTION FOR ]
    { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON TABLES
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON SEQUENCES
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { EXECUTE | ALL [ PRIVILEGES ] }
    ON { FUNCTIONS | ROUTINES }
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { USAGE | ALL [ PRIVILEGES ] }
    ON TYPES
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { USAGE | CREATE | ALL [ PRIVILEGES ] }
    ON SCHEMAS
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

参数说明

六、示例

6.1 创建只读用户

创建 readonly 用户,只可查询 test 模式下的表,连入对应数据库执行如下操作。

--创建只读用户
create user readonly with password 'postgres';

--将 schema 中 usage 权限赋予给 readonly 用户,访问所有已存在的表
grant usage on schema test to readonly;
grant select on all tables in schema test to readonly;

--未来访问 test 模式下所有新建的表
alter default privileges in schema test grant select on tables to readonly ;

6.2 删除只读用户

修改过默认特权的用户要进行删除,均先删除修改的默认特权后在进行删除用户操作

--删除授予的默认特权
drop owned by readonly;

--删除用户
drop user readonly;
07-18 11:36