（1）首先切换到postgres账户下，执行psql命令；
[root@appsrv3 ~]# su - postgres
-bash-4.1$ psql
psql (10.7)
Type "help" for help.
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
readmodel | | {}
sh3clear | | {}
（2）创建一个test1的用户，他可以创建数据库但是无法登陆；
postgres=# create role test1 createdb ;
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
readmodel | | {}
sh3clear | | {}
test1 | Create DB, Cannot login | {}
postgres=#
（3）创建一个test2的用户密码是abc@123，也是无法登陆的
postgres=# create role test2 createdb password 'abc@123';
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
readmodel | | {}
sh3clear | | {}
test1 | Create DB, Cannot login | {}
test2 | Create DB, Cannot login | {}
postgres=#
（4）赋值给已经存在的用户test1给登陆权限
postgres=# alter role test1 with login ;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
readmodel | | {}
sh3clear | | {}
test1 | Create DB | {}
test2 | Create DB, Cannot login | {}
postgres=#
（5）将建立角色的权限赋值给test2
postgres=# alter role test2 with createrole ;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
readmodel | | {}
sh3clear | | {}
test1 | Create DB | {}
test2 | Create role, Create DB, Cannot login | {}
postgres=#
（6）给角色修改密码
postgres=# alter role test2 with password 'xiaoxiao';
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
readmodel | | {}
sh3clear | | {}
test1 | Create DB | {}
test2 | Create role, Create DB, Cannot login | {}
postgres=#
（7）设置角色的有效期
postgres=# alter role test2 valid until '2020-6-10 12:00:00';
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
readmodel | | {}
sh3clear | | {}
test1 | Create DB | {}
test2 | Create role, Create DB, Cannot login +| {}
| Password valid until 2020-06-10 12:00:00+08 |
（8）创建角色与用户
创建test3 角色和tste4 用户
CREATE ROLE test3; //默认不带LOGIN属性
CREATE USER test4; //默认具有LOGIN属性
postgres=# create role test3;
CREATE ROLE
postgres=# create user test4;
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
readmodel | | {}
sh3clear | | {}
test1 | Create DB | {}
test2 | Create role, Create DB, Cannot login +| {}
| Password valid until 2020-06-10 12:00:00+08 |
test3 | Cannot login | {}
test4 | | {}
postgres=# alter role test3 with password 'xiaoxiao';
ALTER ROLE
postgres=# alter role test4 with password 'xiaoxiao';
ALTER ROLE
（9）验证LOGIN属性
连接数据库
psql –h IP -U test3; //不能登录
[root@admin1 ~]# psql -h 192.168.50.33 -U test3
Password for user test3:
psql: FATAL: role "test3" is not permitted to log in
psql –h IP -U test4; //能登录
[root@admin1 ~]# psql -h 192.168.50.33 -U test4
Password for user test4:
psql: FATAL: database "test4" does not exist
[root@admin1 ~]#
如果这样的话是可以登陆的
[root@admin1 ~]# psql -h 192.168.50.33 -U test4 -d postgres
[root@admin1 ~]# psql -h 192.168.50.33 -U test4 -d postgres
Password for user test4:
psql (8.4.13, server 10.7)
WARNING: psql version 8.4, server version 10.7.
Some psql features might not work.
Type "help" for help.
postgres=>
登陆test3就不可用登陆
[root@admin1 ~]# psql -h 192.168.50.33 -U test3 -d postgres
Password for user test3:
psql: FATAL: role "test3" is not permitted to log in
修改test3 的权限，增加LOGIN权限
postgres=# alter role test3 login ;
ALTER ROLE
postgres=#
再次尝试就可以了
[root@admin1 ~]# psql -h 192.168.50.33 -U test3 -d postgres
Password for user test3:
psql (8.4.13, server 10.7)
WARNING: psql version 8.4, server version 10.7.
Some psql features might not work.
Type "help" for help.
postgres=>
（10）角色属性（Role Attributes）
一个数据库角色可以有一系列属性，这些属性定义了他的权限。
属性说明：
login
只有具有 LOGIN 属性的角色可以用做数据库连接的初始角色名。
superuser
数据库超级用户
createdb
创建数据库权限
createrole
允许其创建或删除其他普通的用户角色(超级用户除外)
replication
做流复制的时候用到的一个用户属性，一般单独设定。
password
在登录时要求指定密码时才会起作用，比如md5或者password模式，跟客户端的连接认证方式有关
inherit
===
创建用户时赋予角色属性
从pg_roles表里查看到的信息，在上面创建的tets3用户时，默认没有创建数据库等权限。
ERROR: permission denied to create database
[root@admin1 ~]# psql -h 192.168.50.33 -U test3 -d postgres
Password for user test3:
psql: FATAL: role "test3" is not permitted to log in
[root@admin1 ~]# psql -h 192.168.50.33 -U test3 -d postgres
Password for user test3:
psql (8.4.13, server 10.7)
WARNING: psql version 8.4, server version 10.7.
Some psql features might not work.
Type "help" for help.
postgres=> CREATE DATABASE test3;
ERROR: permission denied to create database
postgres=>
如果要在创建角色时就赋予角色一些属性，可以使用下面的方法。
首先切换到postgres 用户。
创建角色test5 并赋予其CREATEDB 的权限。
CREATE ROLE test5 CREATEDB;
创建角色test5 并赋予其创建数据库及带有密码登录的属性。
postgres=# CREATE ROLE test5 CREATEDB PASSWORD 'abc123' LOGIN;
====
postgres=# create role test6 createdb password '123456' login;
开始验证：
[root@admin1 ~]# psql -h 192.168.50.33 -U test6 -d postgres
Password for user test3:
psql (8.4.13, server 10.7)
WARNING: psql version 8.4, server version 10.7.
Some psql features might not work.
Type "help" for help.
postgres=> create database db1;
CREATE DATABASE
===
角色赋权
在系统的角色管理中，通常会把多个角色赋予一个组，这样在设置权限时只需给该组设置即可，撤销权限时也是从该组撤销。在PostgreSQL中，首先需要创建一个代表组的角色，之后再将该角色的membership 权限赋给独立的角色即可。
创建组角色
CREATE ROLE father login nosuperuser nocreatedb nocreaterole noinherit encrypted password '123456';
给father 角色赋予数据库test 连接权限和相关表的查询权限。
GRANT CONNECT ON DATABASE test to father;
（11）PostgreSQL创建只读用户，带步骤和删除
现有数据库postgres、v0010，当前用户为root，schema为public，
范例：为创建对v0010库public下所有表只有查询权限的readmodel用户
一．创建
1. 创建只读用户：
create user readmodel with ENCRYPTED PASSWORD 'readonly';
设置默认事务只读：
alter user readmodel set default_transaction_read_only=on;
赋予用户连接数据库v0010的权限：
grant CONNECT on DATABASE v0010 to readmodel;
切换到指定库db2：
postgres=# \c v0010
You are now connected to database "v0010" as user "postgres".
赋予用户表、序列查看权限，进入指定db运行：
5.1. 把当前库现有的所有在public这个schema下的表的使用权限赋给用户readonly
v0010=# GRANT USAGE on SCHEMA public to readmodel;
GRANT
5.2. 默认把当前库之后新建在public这个schema下的表的使用权限赋给readonly
v0010=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readmodel;
5.3. 赋予用户readonly所有public下的序列的查看权
v0010=# grant SELECT on ALL sequences in schema public to readmodel;
GRANT
5.4. 赋予用户readonly所有public下的表的select权
v0010=# grant SELECT on ALL tables in schema public to readmodel;
GRANT
如图所示权限为正常。
二．删除
将刚才赋予的权限一一回收
回收schema的usage权限
revoke USAGE ON SCHEMA public from readmodel;
回收public下所有表的查询权限：
revoke SELECT ON ALL TABLES IN SCHEMA public from readmodel;
回收public下所有序列的查询权限
revoke SELECT ON ALL SEQUENCES IN SCHEMA public from readmodel;
回收默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public revoke SELECT ON TABLES from readmodel;
关闭数据库连接权限
revoke CONNECT ON DATABASE foo from readmodel;
关闭默认只读事务设置
alter user readmodel set defaulttransactionread_only=off;
7. 查看权限是否为空了
\ddp
8. 通过管理员删除readonly用户：
drop user read;
参考：
https://blog.csdn.net/weixin_36171533/java/article/details/90319423
评论