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

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

2024-12-25 14:22

(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选项参数如下:

可以看出 ALTER ROLE 命令的option选项与 CRAETE ROLEUSER 的时相同的，具体示例如下：

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 ROLEUSER 或者 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 some_privilege ON database_object_type object_name TO role_name;

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

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

权限的示例

创建一个只读用户：
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.模式的创建

语法：

语法解析：

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;

注意子命令不以分号结束。

下面是达到相同结果的等效的方法：

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

2.模式的修改

语法：

1.重命名模式：

ALTER SCHEMA name RENAME TO new_name;

2.更改模式的owner：

ALTER SCHEMA name OWNER TO { new_owner | CURRENT_USER | SESSION_USER };

注意：要使用ALTER SCHEMA，你必须拥有该模式。要重命名一个模式，你还必须拥有该数据库的CREATE特权。要更改拥有者，你还必须
是新拥有角色的一个直接或者间接成员，并且该角色必须具有该数据库上的 CREATE特权（注意超级用户自动拥有所有这些特权）。

3.删除模式

语法：

DROP SCHEMA [ IF EXISTS ] name [, …] [ CASCADE | RESTRICT ]

语法解析：

1.IF EXISTS
如果该模式不存在则不要抛出一个错误，而是发出一个提示。

2.name
一个模式的名称。

3.CASCADE
自动删除包含在该模式中的对象（表、函数等），然后删除所有依赖于那些对象的对象（见第 5.13 节）。

4.RESTRICT
如果该模式含有任何对象，则拒绝删除它。这是默认值。

注意：使用CASCADE选项可能会使这条命令移除除
指定模式之外其他模式中的对象。因为其他模式中的对象依赖于被删除的模式中的对象，会被级联删除。

4.模式中对象的访问

在创建数据库时Postgresql会在数据库中默认创建名称为 public 的模式，任何用户链接到数据库中都可以在public模式下创建表或者试图等对象，

1.public模式下的表可以直接访问。

2.非public模式下的表的访问方式时：

模式的搜索路径

访问数据库对象时，虽然可以通过加模式名前缀这样全称的方式访问，但是这样做程序中就需要输入每个模式的名称，这很繁琐也不安全，因此，Postgresql提供了一种类似于Linux系统中$PATH这样的环境变量的方式进行快捷访问——模式搜索路径。

数据库执行命令：

第一个元素说明一个和当前用户同名的模式会被搜索。如果不存在这个模式，该项将被忽略。第二个元素指向我们已经见过的公共模式。

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

±-----
(0 rows)

“$user”, public
(1 row)