【原】美女DBA带你了解PostgreSQL用户及角色

数据和云 2020-07-01

展开全文

墨墨导读：本文为大家讲述了PostgreSQL数据库的用户及角色，希望对刚接触PostgreSQL数据库的朋友们有帮助。

7.24 晚 20:30-21:30相约云和恩墨大讲堂，本期分享一个由Select * from dual引起的数据库性能瓶颈案例，深入分析其中的机制。详情报名请戳：

https://cs./event/56

一、角色

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 ~]$ psqlPassword for user postgres: psql (11.2)Type "help" for help.postgres=# CREATE ROLE LANSHAN LOGIN; 创建一个角色CREATE ROLEpostgres=# CREATE USER lanshan WITH PASSWORD 'admin123'; 可以看出pg中用户名不区分大小写ERROR: role "lanshan" already exists

postgres=# CREATE USER lanshan1 WITH PASSWORD 'admin123'; 创建一个用户，并设置密码CREATE ROLEpostgres=# CREATE ROLE lanshan2 WITH PASSWORD 'admin123'; 创建一个角色，并设置密码CREATE ROLEpostgres=# \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 ~]$ psqlpsql (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 ROLEpostgres=# \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 ROLEpostgres=# \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 -WPassword: 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 identhost 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 peerhost replication all 127.0.0.1/32 identhost replication all ::1/128 ident

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

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

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

Trust:不需要认证

把操作系统认证登录的认证方式改为MD5local 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.conflocal all all trust[postgres@abc data]$ pg_ctl reload [postgres@abc data]$ psqlpsql (11.2)Type "help" for help.postgres=# alter user postgres WITH PASSWORD 'admin123';ALTER ROLEpostgres=# exit改完密码之后，把认证方式改为md5,重新加载配置。[postgres@abc data]$ psqlPassword for user postgres: psql: FATAL: password authentication failed for user "postgres"[postgres@abc data]$ psqlPassword 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_specificationwhere 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 existpostgres=# 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 hollywoodpostgres-# CREATE TABLE films (title text, release date, awards text[])postgres-# CREATE VIEW winners ASpostgres-# SELECT title, release FROM films WHERE awards IS NOT NULL;CREATE SCHEMA等价于：先删除创建的对象：ERROR: cannot drop table hollywood.films because other objects depend on itDETAIL: view hollywood.winners depends on table hollywood.filmsHINT: Use DROP ... CASCADE to drop the dependent objects too.postgres=# DROP TABLE hollywood.films cascade;NOTICE: drop cascades to view hollywood.winnersDROP TABLE删除schemapostgres=# drop schema hollywood cascade;DROP SCHEMA可以看出跟oracle的命令还是很像的。postgres=# CREATE SCHEMA hollywood;CREATE SCHEMApostgres=# CREATE TABLE hollywood.films (title text, release date, awards text[]);CREATE TABLEpostgres=# CREATE VIEW hollywood.winners AS SELECT title, release FROM hollywood.films WHERE awards IS NOT NULL;CREATE VIEW加了分号之后，就不是子命令，必须指定schema名称。

原创：兰珊