Postgresql学习笔记之——逻辑结构管理之用户、角色、模式_new_owner current_user session_user

最新推荐文章于 2024-05-21 10:48:30 发布

2401_84563287

最新推荐文章于 2024-05-21 10:48:30 发布

阅读量227

点赞数 4

文章标签：单元测试功能测试学习

本文链接：https://blog.csdn.net/2401_84563287/article/details/138940300

版权

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以戳这里获取

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

在Postgresql数据库中，用户和角色是没有区别的。用户和角色的创建语法如下：

CREATE USER name [ [ WITH ] option [ … ] ]

CREATE ROLE name [ [ WITH ] option [ … ] ]

两个命令在Postgresql数据库中都是定义了一个角色（role），不同的是 ”CREATE USER“ 定义的角色默认具有 LOGIN 权限（直白一点：能登录的角色就是用户，主要看有没有 LOGIN权限，），所以说是创建了用户。而 ”CREATE ROLE“ 就是单纯的创建了一个角色（角色可以说是一系列权限的集合），如果创建角色后又赋予了 LOGIN 权限，那么可以说它成了可以登录的用户了。

注意：

1.创建的用户或者授予了LOGIN权限的角色必须设置密码后才能正式链接。设置密码可以在创建时直接指定，也可以创建后再设置。

2.用户和角色在整个数据库实例中都是全局的，在同一个示例下的不同数据库中看到的用户也是相同的。

CREATE USER 和 CREATE ROLE 的option都有如下选项：

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

1.SUPERUSER | NOSUPERUSER :
这些子句确定新角色是否为“超级用户”，可以覆盖数据库中的所有访问限制。超级用户状态是危险的，应该只在真正需要的时候使用。你必须是一个超级用户才能创造一个新的超级用户。如果未指定，则默认为NOSUPERUSER。

2.CREATEDB | NOCREATEDB:
这些子句定义了角色创建数据库的能力。如果指定了CREATEDB，则允许定义的角色创建新数据库。指定NOCREATEDB将剥夺角色创建数据库的能力。如果没有指定，NOCREATEDB是默认值。

3.CREATEROLE | NOCREATEROLE:
这些子句确定是否允许角色创建新角色(即执行create role)。具有CREATEROLE特权的角色还可以更改和删除其他角色。如果没有指定，NOCREATEROLE是默认的。

4.INHERIT | NOINHERIT:
这些子句确定角色是否“继承”其所属角色的特权。具有INHERIT属性的角色可以自动使用已授予其直接或间接成员的所有角色的任何数据库特权。如果没有继承，另一个角色的成员身份只允许将角色设置为另一个角色;其他角色的特权只有在这样做之后才可用。如果未指定，则默认为INHERIT。

5.LOGIN | NOLOGIN:
这些子句确定是否允许角色登录;也就是说，是否可以在客户端连接。具有LOGIN属性的角色可以视为用户。没有此属性的角色对于管理数据库特权很有用，但不是通常意义上的用户。如果未指定，则默认为NOLOGIN。

6.REPLICATION | NOREPLICATION:
这些子句确定一个角色是否是复制角色。角色必须具有此属性(或超级用户)，以便能够以复制模式(物理或逻辑复制)连接到服务器，并能够创建或删除复制槽。具有REPLICATION属性的角色是一个特权非常高的角色，应该只在实际用于复制的角色上使用。如果未指定，则默认为NOREPLICATION。

7,BYPASSRLS | NOBYPASSRLS:
这些子句确定角色是否绕过每个行级安全(RLS)策略。NOBYPASSRLS是默认值。注意，pg_dump将默认将row_security设置为OFF，以确保将表中的所有内容转储出去。如果运行pg_dump的用户没有适当的权限，将返回一个错误。被转储的表的超级用户和所有者总是绕过RLS。

8.CONNECTION LIMIT connlimit :
如果角色可以登录，则指定该角色可以建立多少个并发连接。-1(默认值)表示没有限制。请注意，只有正常连接才算到这个极限。无论是预先准备的交易还是后台工作连接都不计入这个限额。

9.[ ENCRYPTED ] PASSWORD ‘password’ | PASSWORD NULL:
设置角色的密码。(密码只用于具有LOGIN属性的角色，但是您可以为没有该属性的角色定义一个密码。)如果不打算使用密码身份验证，可以忽略此选项。如果没有指定密码，则将密码设置为null，该用户的密码身份验证将始终失败。可以选择将空密码显式写入为空密码。

注意：
1.指定一个空字符串也会将密码设置为null，但在PostgreSQL版本10之前不是这样的。在早期的版本中，可以使用空字符串，也可以不使用空字符串，这取决于身份验证方法和确切的版本，libpq在任何情况下都不会使用空字符串。为了避免歧义，应该避免指定空字符串。
2.密码总是被加密存储在系统目录中。加密的关键字没有效果，但是可以向后兼容。加密方法由配置参数password_encryption决定。如果所提供的密码字符串已经采用md5加密或超扰码加密格式，则无论password_encryption(因为系统无法解密指定的加密密码字符串，所以要用不同的格式加密)，它都按原样存储。这允许在转储/恢复期间重新加载加密的密码。

10.VALID UNTIL ‘timestamp’ ：
VALID UNTIL子句设置一个日期和时间，在此日期和时间之后，角色的密码将不再有效。如果省略了这个子句，密码将一直有效。

11.IN ROLE role_name ：
IN ROLE子句列出一个或多个现有角色，新角色将立即作为新成员添加到这些角色中。(请注意，没有添加新的管理员角色的选项;使用一个单独的GRANT命令。)

12.IN GROUP role_name：
IN GROUP是IN ROLE的一种过时拼写。

13.ROLE role_name：
ROLE子句列出一个或多个现有角色，这些角色自动添加为新角色的成员。(这实际上使新角色成为一个“group”。)

14.ADMIN role_name ：
ADMIN子句类似于ROLE，但是已命名的角色被添加到具有ADMIN选项的新角色中，从而使它们有权将此角色中的成员资格授予其他人。

15.USER role_name :
USER子句是ROLE子句的一种过时拼写。

16.SYSID uid:
SYSID子句被忽略，但是为了向后兼容而被接受。

PS：用户的创建也可以通过createuser工具在shell命令上执行。参考：https://www.postgresql.org/docs/12/app-createuser.html

注意

1.使用ALTER ROLE更改角色的属性，使用DROP ROLE删除角色。ALTER ROLE命令可以修改CREATE ROLE指定的所有属性。

2.添加和删除作为组使用的角色成员的首选方法是使用GRANT和REVOKE。

3.VALID UNTIL子句仅为密码定义过期时间，而不是角色本身。特别是，在使用非基于密码的身份验证方法登录时，不会强制执行过期时间。

4.INHERIT属性管理可授予特权(即数据库对象和角色成员身份的访问特权)的继承。它不适用于CREATE role和ALTER role设置的特殊角色属性。例如，具有CREATEDB特权的角色的成员不能立即授予创建数据库的能力，即使设置了INHERIT;在创建数据库之前，有必要通过SET role成为该角色。

5.出于向后兼容性的考虑，INHERIT属性是默认的:在以前的PostgreSQL版本中，用户总是可以访问他们所属组的所有特权。但是，NOINHERIT提供了与SQL标准中指定的语义更接近的匹配。

6.小心CREATEROLE特权。对于CREATEROLE-role的权限中没有继承的概念。这意味着，即使一个角色没有特定的特权，但是可以创建其他角色，它也可以轻松地创建具有不同于自身特权的其他角色(除了创建具有超级用户特权的角色之外)。例如，如果角色“user”具有CREATEROLE特权，但不具有CREATEDB特权，那么它仍然可以使用CREATEDB特权创建新角色。因此，将具有CREATEROLE特权的角色视为几乎是超级用户角色。

7.PostgreSQL包含一个createuser程序，它具有与CREATE角色相同的功能(事实上，它调用了这个命令)，但是可以从命令shell中运行。

8.CONNECTION LIMIT 连接限制选项只能近似执行;如果两个新会话几乎同时开始，而角色只剩下一个连接“插槽”，那么两个会话都可能失败。此外，对于超级用户，这个限制不是强制执行的。

9.当使用此命令指定未加密的密码时，必须谨慎。密码将以明文形式传输到服务器，也可以记录在客户机的命令历史记录或服务器日志中。但是，命令createuser传输加密的密码。此外，psql包含一个命令\密码，可以用于以后安全地更改密码。

示例：

1.创建一个可以登录的角色，但是不指定密码：

CREATE ROLE jonathan LOGIN;

2.创建一个用户（可登录）并执行密码：

CREATE USER davide WITH PASSWORD ‘jw8s0F4’;

(CREATE USER与CREATE角色相同，不同之处在于有没有授予 LOGIN.)

3.创建一个角色，该角色的密码在2004年底之前有效。2005年第一秒后，密码不再有效：

CREATE ROLE miriam WITH LOGIN PASSWORD ‘jw8s0F4’ VALID UNTIL ‘2005-01-01’;

4.创建一个可以创建数据库和管理角色的角色：

CREATE ROLE admin WITH CREATEDB CREATEROLE;

2.角色的修改

语法如下：

ALTER ROLE role_specification [ WITH ] option [ … ]

option选项参数如下:

 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 ROLE 命令的option选项与 CRAETE ROLE\USER 的时相同的，具体示例如下：

1.更改角色的密码：

ALTER ROLE davide WITH PASSWORD ‘hu8jmn3’;

2.移除角色的密码：

ALTER ROLE davide WITH PASSWORD NULL;

3.更改密码过期日期，指定密码在2015年5月4日中午到期，使用的时区比UTC早一个小时:

ALTER ROLE chris VALID UNTIL ‘May 4 12:00:00 2015 +1’;

4.设置角色密码永久有效：

ALTER ROLE fred VALID UNTIL ‘infinity’;

5.赋予角色创建其他角色和新数据库的能力:

ALTER ROLE miriam CREATEROLE CREATEDB;

6.重命名角色的名称：

ALTER ROLE name RENAME TO new_name

注意：

1.ALTER ROLE更改一个 PostgreSQL角色的属性。

2.前面列出的这个命令的第一种变体能够更改CREATE ROLE中指定的很多角色属性（覆盖了所有可能的属性，不过没有增加和移除成员关系的选项，如果要增加和移除成员关系可使用GRANT和 REVOKE）。该命令中没有提到的属性保持它们之前的设置。数据库超级用户能够更改任何角色的任何这些设置。具有CREATEROLE 特权的角色能够更改任何这些设置，但是只能为非超级用户和非复制角色修改。普通角色只能更改它们自己的口令。

3.第二种变体更改该角色的名称。数据库超级用户能重命名任何角色。具有 CREATEROLE特权的角色能够重命名任何非超级用户角色。当前的会话用户不能被重命名（如果需要这样做，请以一个不同的用户连接）。由于 MD5加密的口令使用角色名作为 salt，因此如果一个角色的口令是 MD5加密的，重命名该角色会清空其口令。

4.其余的变体用于更改一个角色的配置变量的会话默认值，可以为所有数据库设置，或者只为IN DATABASE中指定的数据库设置。如果指定的是 ALL而不是一个角色名，将会为所有角色更改该设置。把 ALL和IN DATABASE一起使用实际上和使用命令ALTER DATABASE … SET …相同。

5.只要改角色后续开始一个新会话，指定的值将会成为该会话的默认值，并且会覆盖 postgresql.conf中存在的值或者从 postgres命令行收到的值。这只在登录时发生，执行 SET ROLE或者 SET SESSION AUTHORIZATION不会导致新的配置值被设置。对于所有数据库设置的值会被附加到一个角色的数据库相关的设置所覆盖。特定数据库或角色的设置会覆盖为所有角色所作的设置。

6.超级用户能够更改任何人的会话默认值。具有CREATEROLE特权的角色能够更改非超级用户的默认值。普通角色只能为它们自己设置默认值。某些配置变量不能以这种方式设置，或者只能由一个超级用户发出的命令设置。只有超级用户能够更改所有角色在所有数据库中的设置。

3.删除角色

语法：

DROP ROLE [ IF EXISTS ] name [, …]

参数解析：

1.IF EXISTS：如果角色存在就删除，不存在也不会报错。

2.name：需要被删除的角色名称。

示例：

删除角色：DROP ROLE jonathan;

PS:同样有一个程序dropuser，它具有与这个命令相同的功能(实际上，它调用这个命令)，但是可以从命令shell中运行。参考：https://www.postgresql.org/docs/12/app-dropuser.html

4.角色代表了权限

上面说到角色是一系列权限的集合，其中组成角色的权限分为了两类，一类是上述在 CREATE ROLE\USER 或者 ALTER ROLE 进行创建和i修改的。还有一类权限则需要使用 GRANT和REVOKE进行管理，比如：
1.在数据库中创建模式（schema）；
2.在指定的库中创建临时表；
3.链接某个数据库；
4.在指定的模式中创建数据库对象（public模式任何用户都可以创建）；
5.对一些指定的表或者表中指定的列进行DML操作；
6.对序列进行查询，使用、更新操作；
7.在声明表上创建触发器；
8.对数据对象进行迁移操作等。

GRANT语法：

1.让某个用户成为某个角色的成员，让其有整个角色的权限：

GRANT role_name[ , … ] TO role_name[ , … ] [ WITH ADMIN OPTION ]

2.把某些数据库逻辑结构的操作权限赋予某个用户或角色：

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 ]

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 ]

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 ]

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 ]

以上语法可以简写为：

GRANT some_privilege ON database_object_type object_name TO role_name;

其中的 ”some_privilege“ 表示在这个数据库对象中的权限， ”database_object_type“ 是数据库对象的类型，如 TABLE、sequence、schema等。

”some_privilege“ 主要包含一下权限：

SELECT
INSERT
UPDATE
DELETE
TRUNCATE
REFERENCES
TRIGGER
CREATE
CONNECT
TEMPORARY
EXECUTE
USAGE
TEMP
ALL PRIVILEGES

权限的示例

创建一个只读用户：
1.首先执行一下SQL：

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

在Postgresql中任何用户默认都可以在名称为public的模式中创建表，单纯的只读用户不允许，与因此要收回这个权限。

2.创建一个名称为readonly的用户：

CREATE USER readonly WITH PASSWORD ‘query’;

3.然后将模式postgres下的所有表的SELECT权限授予只读用户readonly，执行如下SQL：

GRANT SELECT ON ALL TABLES IN SCHEMA postgres TO readonly;

注意：上面的授权SELECT的语句是将postgres中现有表的权限授予了readonly用户，如果后面有创建新的表，readonly还是没有查询权限的。这个问题可以通过下面的授权语句解决：

ALTER DEFAULT PRIVILEGES IN SCHEMA postgres GRANT SELECT ON TABLES TO readonly;

如果还想将其他模式的表的SELECT权限授予readonly用户，需要重复执行一下语句：

GRANT SELECT ON ALL TABLES IN SCHEMA other_schema TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA other_schema GRANT SELECT ON TABLES TO readonly;

二、模式

模式（schema）是数据库中的一个概念，可以将它理解为一个命名空间或者目录，存放数据库中的逻辑对象，如表、视图、函数、物化视图等，不同的模式下可以有相同名称的表、函数等对象且互相不冲突，提出模式（schema）的概念主要是为了便于管理，只要有权限，每个模式中的对象可以互相调用。

1.在Postgresql中一个数据库可以包含多个模式，不同的数据库之间可以有相同名称的模式（schema）。

2.一个数据库连接不能同时访问不同数据库中的对象，需要访问另一个数据库中模式下的对象时需要从新指定链接中配置的数据库。

3.一个数据库链接可以访问同一个数据库下不同的模式（只要有相应的权限）。

1.模式的创建

语法：

CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification

其中 role_specification 可以是：

    user_name
  | CURRENT_USER
  | SESSION_USER

语法解析：

1.schema_name
要创建的一个模式名。如果省略， user_name将被用作模式名。该名称不能以pg_开始，因为这样的名称是用作系统模式的。

2.user_name
将拥有新模式的用户的角色名。如果省略，默认为执行该命令的用户。要创建由另一个角色拥有的角色，你必须是那个角色的一个直接或者间接成员，或者是一个超级用户。

3.schema_element
要在该模式中创建的对象的定义 SQL 语句。当前，只有CREATE TABLE、CREATE VIEW、CREATE INDEX、CREATE SEQUENCE、CREATE TRIGGER以及GRANT被接受为 CREATE SCHEMA中的子句。其他类型的对象可以在模式被创建之后用单独的命令创建。

4.IF NOT EXISTS
如果一个具有同名的模式已经存在，则什么也不做（不过发出一个提示）。使用这个选项时不能包括 schema_element子命令。

5.AUTHORIZATION role_specification
在创建模式时指定模式的owner。

注意：要创建一个模式，调用用户必须拥有当前数据库的CREATE 特权（当然，超级用户可以绕过这种检查）。

示例

1.创建一个模式：

CREATE SCHEMA myschema;

2.为用户joe创建一个模式，该模式也将被命名为 joe：

CREATE SCHEMA AUTHORIZATION joe;

3.创建一个被用户joe拥有的名为test的模式，除非已经有一个名为test的模式（不管joe 是否拥有该已经存在的模式）。

CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION joe;

4.创建一个模式并且在其中创建一个表和视图：

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以戳这里获取

CREATE SCHEMA myschema;

2.为用户joe创建一个模式，该模式也将被命名为 joe：

CREATE SCHEMA AUTHORIZATION joe;

3.创建一个被用户joe拥有的名为test的模式，除非已经有一个名为test的模式（不管joe 是否拥有该已经存在的模式）。

CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION joe;

4.创建一个模式并且在其中创建一个表和视图：

[外链图片转存中…(img-7U6nUswt-1715815783985)]
[外链图片转存中…(img-45U689ia-1715815783986)]

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以戳这里获取

2401_84563287

关注

4
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
Postgresql学习笔记之——逻辑结构管理之用户、角色、模式_new_owner current_user session_user

在Postgresql数据库中，用户和角色是没有区别的。用户和角色的创建语法如下：两个命令在Postgresql数据库中都是定义了一个角色（role），不同的是 ”CREATE USER“ 定义的角色默认具有 LOGIN 权限（直白一点：能登录的角色就是用户，主要看有没有 LOGIN权限，），所以说是创建了用户。而 ”CREATE ROLE“ 就是单纯的创建了一个角色（角色可以说是一系列权限的集合），如果创建角色后又赋予了 LOGIN 权限，那么可以说它成了可以登录的用户了。
复制链接

扫一扫