PostgreSQL 权限控制

用户头像
唯爱
关注
发布于: 2020 年 06 月 09 日
(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';



  1. 设置默认事务只读:

alter user readmodel set default_transaction_read_only=on;



  1. 赋予用户连接数据库v0010的权限:

grant CONNECT on DATABASE v0010 to readmodel;



  1. 切换到指定库db2:

postgres=# \c v0010
You are now connected to database "v0010" as user "postgres".



  1. 赋予用户表、序列查看权限,进入指定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





如图所示权限为正常。



二.删除

    将刚才赋予的权限一一回收



  1. 回收schema的usage权限



revoke USAGE ON SCHEMA public from readmodel;

  1. 回收public下所有表的查询权限:



revoke SELECT ON ALL TABLES IN SCHEMA public from readmodel;

  1. 回收public下所有序列的查询权限



revoke SELECT ON ALL SEQUENCES IN SCHEMA public from readmodel;

  1. 回收默认权限



ALTER DEFAULT PRIVILEGES IN SCHEMA public revoke SELECT ON TABLES from readmodel;

  1. 关闭数据库连接权限



revoke CONNECT ON DATABASE foo from readmodel;

  1. 关闭默认只读事务设置

alter user readmodel set defaulttransactionread_only=off;

7. 查看权限是否为空了

\ddp

8. 通过管理员删除readonly用户:

drop user read;

参考:

https://blog.csdn.net/weixin_36171533/java/article/details/90319423



用户头像

唯爱

关注

天行健,君子以自强不息~ 2018.03.22 加入

梦想还是要有的,万一实现了呢!

评论

发布
暂无评论
PostgreSQL权限控制