原文:
https://www.enmotech.com/web/detail/1/774/1.html (复制链接,打开浏览器即可查看)
导读:本文为大家讲述了PostgreSQL数据库的用户及角色,希望对刚接触PostgreSQL数据库的朋友们有帮助。
一、角色
PostgreSQL使用角色的概念:管理数据库访问权限。根据角色自身的设置不同,一个角色可以看做是一个数据库用户,或者一组数据库用户。角色可以拥有数据库对象(比如表)以及可以把这些对象上的权限赋予其它角色,以控制谁拥有访问哪些对象的权限。
1. 创建角色
语法:
CREATE ROLE name [ [ WITH ] option [ ... ] ]where option can be: 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) 角色属性:数据库角色有一系列的属性,这些属性定义了他们的权限。
2) 角色关系:通常情况下会把很多角色分成同一组去管理。比如把权限授予一个组,或者回收一个组的权限。一般加入组的用户不会授予LOGIN权限。
2. 其他选项
测试:
[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 existspostgres=# 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 inpostgres=# CREATE USER lanshan2 WITH PASSWORD 'admin123'; 用户和角色不能重名ERROR: role "lanshan2" already exists
二、创建用户
其实用户和角色都是角色,只是用户是具有登录权限的角色。
语法:
CREATE USER name [ [ WITH ] option [ ... ] ]where option can be: SUPERUSER | NOSUPERUSER ---dba用户/普通用户 | CREATEDB | NOCREATEDB ---创建db的权限 | CREATEROLE | NOCREATEROLE ---创建role | 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: 在系统命令行中使用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: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名称。
原创:兰珊
想了解更多关于数据库、云技术的内容吗?
快来关注“数据和云”公众号、“云和恩墨”官方网站,我们期待与大家一同学习和进步!
(扫描上方二维码,关注“数据和云”公众号,即可查看更多科技文章)