什么是PostgreSQL用户和角色

57次阅读

共计 7629 个字符，预计需要花费 20 分钟才能阅读完成。

本篇内容介绍了“什么是 PostgreSQL 用户和角色”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

PostgreSQL 使用角色的概念：管理数据库访问权限。根据角色自身的设置不同，一个角色可以看做是一个数据库用户，或者一组数据库用户。角色可以拥有数据库对象 (比如表) 以及可以把这些对象上的权限赋予其它角色，以控制谁拥有访问哪些对象的权限。

1. 创建角色

语法：

1) 角色属性：数据库角色有一系列的属性，这些属性定义了他们的权限。

登录权限：只有具有 LOGIN 属性的角色，才可以登陆（连接数据库）。具有 LOG IN 属性的角色实际上就是一个数据库用户。

CREATE ROLE name LOGIN：等价于 CREATE USER name

Superuser：数据库超级用户会绕过所有的权限检查。具有一切权限。

CREATE ROLE name SUPERUSER（只有本身是超级用户才能执行）

CREATEDB：创建数据库。

REPLICATION：只能与 LOGIN, 同时指定。使用 Replicate mode 登录数据库或者创建删除 replicate slots，需要该权限。

CREATE ROLE name REPLICATION LOGIN.

2) 角色关系：通常情况下会把很多角色分成同一组去管理。比如把权限授予一个组，或者回收一个组的权限。一般加入组的用户不会授予 LOGIN 权限。

CREATE ROLE name

创建 ROLE 之后，就可以 grant，revoke 成员。

GRANT group_role TO role1, … ;

REVOKE group_role FROM role1, … ;

INHERIT：这些子句确定角色是否“继承”其所属角色的特权。一个具有 INHERIT 属性的角色可以自动使用任何数据库特权授予它直接或间接属于的所有角色。没有继承，加入另一个角色只授予将角色设置为该另一个角色的能力; 另一方的特权角色只有在完成此操作后才可用。如果未指定，则默认为 INHERIT。

2. 其他选项

BYPASSRLS: 绕过所有的行级别安全策略。

CONNECTION LIMIT connlimit：限制用户登录次数，默认值 -1（不限制）。

[ENCRYPTED] PASSWORD password：设置登录密码。ENCRYPTED（默认省略，为向后兼容），加密方式是由配置文件决定。

VALID UNTIL timestamp：指定密码有效期。

IN ROLE：把当前创建的 role 加入已经指定的 role 组当中。注：不能作为 administer 加入，只能单独使用 grant 的方式。

ROLE role_name：把 role_name 作为一个成员，加入当前创建的 role 中（即当前 role 作为 group）。

ADMIN role_name: 在 ROLE role_name 的基础上，赋予 role_name grant membership 的权限。类似于 oracle 的级联权限。

测试：

[postgres@abc ~]$ psql
Password for user postgres:

psql (11.2)
Type help for help.
postgres=# CREATE ROLE LANSHAN LOGIN; 创建一个角色
CREATE ROLE
postgres=# CREATE USER lanshan WITH PASSWORD admin123 可以看出 pg 中用户名不区分大小写
ERROR: role lanshan already exists

postgres=# CREATE USER lanshan1 WITH PASSWORD admin123 创建一个用户，并设置密码
CREATE ROLE
postgres=# CREATE ROLE lanshan2 WITH PASSWORD admin123 创建一个角色，并设置密码
CREATE ROLE
postgres=# \q
[postgres@abc ~]$ psql -Ulanshan1 -dpostgres 用户登录到数据库
Password for user lanshan1:

psql (11.2)
Type help for help.

postgres= \q
[postgres@abc ~]$ psql -Ulanshan2 -dpostgres lanshan2 是一个角色，无法登录。
Password for user lanshan2:

psql: FATAL: role lanshan2 is not permitted to log in

postgres=# CREATE USER lanshan2 WITH PASSWORD admin123 用户和角色不能重名
ERROR: role lanshan2 already exists

二、创建用户

其实用户和角色都是角色，只是用户是具有登录权限的角色。

语法：

测试创建用户：

方式 1: 在系统命令行中使用 createuser 命令中创建
Create user username

方式 2: 在 PostgresSQL 命令行中使用 CREATE ROLE 指令创建
CREATE ROLE rolename;

[postgres@abc ~]$ createuser lss
[postgres@abc ~]$ psql
psql (11.2)
Type help for help.
postgres-# \du
List of roles
Role name | Attributes | Member of

———–+————————————————————+———–
lss | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
授权
postgres=# alter role lss Createrole CREATEDB;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of

———–+————————————————————+———–
lss | Superuser, Create role, Create DB | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
授予多个权限用空格隔开
postgres=# alter role lss REPLICATION BYPASSRLS;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of

———–+————————————————————+———–
lss | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

修改用户密码
postgres=# ALTER ROLE lss WITH PASSWORD admin123
ALTER ROLE
给用户授权
pgtest=# GRANT ALL PRIVILEGES ON DATABASE postgres to lss;
GRANT
登录：
[postgres@abc ~]$ psql -U lss -d pgtest -W
Password:

psql: FATAL: Peer authentication failed for user lss
登陆失败
[postgres@abc data]$ cat pg_hba.conf — 该配置文件指定认证方式
# TYPE DATABASE USER ADDRESS METHOD

# local is for Unix domain socket connections only 操作系统直接登录
local all all md5
#local all all peer
# IPv4 local connections: 指定 IP 地址登录
#host all all 127.0.0.1/32 ident
host all all 127.0.0.1/32 md5
# IPv6 local connections: 指定 IP 地址登录
host all all ::1/128 ident
# Allow replication connections from localhost, by a user with the
# replication privilege. 复制权限，认证方式
local replication all peer
host replication all 127.0.0.1/32 ident
host replication all ::1/128 ident

ident：通过联系客户端的 ident 服务器获取客户端的操作系统名，并且检查它是否匹配被请求的数据库用户名。Ident 认证只能在 TCIP/IP 连接上使用。

Peer：从操作系统获得客户端的操作系统用户，并且检查它是否匹配被请求的数据库用户名。这只对本地连接可用。

md5: 要求客户端提供一个双重 MD5 加密的口令进行认证。

Trust: 不需要认证

把操作系统认证登录的认证方式改为 MD5
local all all md5
[postgres@abc data]$ pg_ctl reload —- 重新加载配置文件
[postgres@abc data]$ psql -Ulss -dpgtest

Password for user lss:

psql (11.2)
Type help for help.
pgtest=#

如果超级用户忘记密码怎办？
vi pg_hba.conf
local all all trust
[postgres@abc data]$ pg_ctl reload

[postgres@abc data]$ psql
psql (11.2)
Type help for help.
postgres=# alter user postgres WITH PASSWORD admin123
ALTER ROLE
postgres=# exit
改完密码之后，把认证方式改为 md5, 重新加载配置。
[postgres@abc data]$ psql
Password for user postgres:

psql: FATAL: password authentication failed for user postgres
[postgres@abc data]$ psql
Password for user postgres:

psql (11.2)
Type help for help.
postgres=#

三、SCHEMA

CREATE SCHEMA 在当前数据库中创建一个新 SCHEMA。SCHEMA 名称必须与当前数据库中现有 SCHEMA 的名称不同。

SCHEMA 本质上是一个名称空间: 它包含命名对象(表、数据类型、函数和操作符)，这些对象的名称可以复制其他 SCHEMA 中存在的其他对象的名称。通过将 SCHEA 名作为前缀“限定”它们的名称，或者通过设置包含所需 SCHEMA 的搜索路径来访问命名对象。指定非限定对象名称的 CREATE 命令创建当前模式中的对象(搜索路径前面的对象，可以使用函数 current_schema 确定)。

CREATE SCHEMA 还可以包含子命令，用于在新 SCHEMA 中创建对象。子命令本质上与创建 SCHEMA 后发出的单独命令相同，但如果使用了授权子句，则创建的所有对象都将由该用户拥有。

1. 创建 schema

语法：

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
where role_specification can be:
user_name
| CURRENT_USER
| SESSION_USER

2. 参数

schema_name:schema 名称，不能以 pg_开头，pg_是为系统用户保留。

user_name：新创建的用户将属于新 SCHEMA。如果省略，则默认为执行命令的用户。要创建由另一个 role 拥有的 SCHEMA，您必须是该 role 的直接或间接成员，或者是 superuser。

schema_element：定义要在模式中创建的对象的 SQL 语句。目前, 只有创建表，创建视图，创建索引，创建序列，创建触发器和 GRANT 被接受为 CREATE SCHEMA 中的子句。其他类型的对象可以在模式创建之后在单独的命令中创建。

IF NOT EXISTS：如果已经存在具有相同名称的模式，则不创建。使用此选项时，无法包含 schema_element 子命令。

测试：

创建一个 schema:
postgres=# CREATE SCHEMA myschema;
CREATE SCHEMA

为一个用户创建 schema:
postgres=# CREATE SCHEMA AUTHORIZATION joe;(这个用户或者 role 必须存在)
ERROR: role joe does not exist
postgres=# CREATE SCHEMA AUTHORIZATION lss;
CREATE SCHEMA

创建一个名为 test 的 SCHEMA，该 SCHEMA 将由用户 lss 拥有，除非已经有一个名为 test 的模式。(lss 是否拥有已经存在的 SCHEMA 并不重要。)
postgres=# CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION lss;
CREATE SCHEMA

创建一个 schema, 不指定以分号结尾，默认为子命令：
postgres=# CREATE SCHEMA hollywood
postgres-# CREATE TABLE films (title text, release date, awards text[])
postgres-# CREATE VIEW winners AS
postgres-# SELECT title, release FROM films WHERE awards IS NOT NULL;
CREATE SCHEMA
等价于：
先删除创建的对象：
ERROR: cannot drop table hollywood.films because other objects depend on it
DETAIL: view hollywood.winners depends on table hollywood.films
HINT: Use DROP … CASCADE to drop the dependent objects too.
postgres=# DROP TABLE hollywood.films cascade;
NOTICE: drop cascades to view hollywood.winners
DROP TABLE
删除 schema
postgres=# drop schema hollywood cascade;
DROP SCHEMA
可以看出跟 oracle 的命令还是很像的。
postgres=# CREATE SCHEMA hollywood;
CREATE SCHEMA
postgres=# CREATE TABLE hollywood.films (title text, release date, awards text[]);
CREATE TABLE
postgres=# CREATE VIEW hollywood.winners AS SELECT title, release FROM hollywood.films WHERE awards IS NOT NULL;
CREATE VIEW
加了分号之后，就不是子命令，必须指定 schema 名称。