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

先自我介绍一下，小编浙江大学毕业，去过华为、字节跳动等大厂，目前阿里P7

深知大多数程序员，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新软件测试全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上软件测试知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

如果你需要这些资料，可以添加V获取：vip1024b （备注软件测试）

正文

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 hollywood
CREATE TABLE films (title text, release date, awards text[])
CREATE VIEW winners AS
SELECT title, release FROM films WHERE awards IS NOT NULL;

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

需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注软件测试）

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

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

CREATE SCHEMA hollywood
CREATE TABLE films (title text, release date, awards text[])
CREATE VIEW winners AS
SELECT title, release FROM films WHERE awards IS NOT NULL;

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

需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注软件测试）
[外链图片转存中…(img-oXUTX3Pn-1713150004008)]

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