PostgreSQL权限控制

最新推荐文章于 2025-03-31 10:51:08 发布

JESSE_JAVA

最新推荐文章于 2025-03-31 10:51:08 发布

阅读量4.4k

点赞数 3

分类专栏：运维数据库

本文链接：https://blog.csdn.net/weixin_36171533/article/details/90319423

版权

运维同时被 2 个专栏收录

74 篇文章

订阅专栏

数据库

18 篇文章

订阅专栏

postgres=# \du
                             角色列表
 角色名称 |                    属性                    | 成员属于 
----------+--------------------------------------------+----------
 postgres | 超级用户, 建立角色, 建立 DB, 复制, 绕过RLS | {}

创建一个test1的用户，他可以创建数据库但是无法登陆

postgres=# create role test1 createdb;
CREATE ROLE
postgres=# \du
                             角色列表
 角色名称 |                    属性                    | 成员属于 
----------+--------------------------------------------+----------
 postgres | 超级用户, 建立角色, 建立 DB, 复制, 绕过RLS | {}
 test1    | 建立 DB, 无法登录                          | {}

创建一个test2的用户密码是123456，也是无法登陆的

postgres=# create role test2 createdb password '123456';
CREATE ROLE
postgres=# \du
                             角色列表
 角色名称 |                    属性                    | 成员属于 
----------+--------------------------------------------+----------
 postgres | 超级用户, 建立角色, 建立 DB, 复制, 绕过RLS | {}
 test1    | 建立 DB, 无法登录                          | {}
 test2    | 建立 DB, 无法登录                          | {}

赋值给已经存在的用户test1给登陆权限

postgres=# alter role test1 with login;
ALTER ROLE
postgres=# \du
                             角色列表
 角色名称 |                    属性                    | 成员属于 
----------+--------------------------------------------+----------
 postgres | 超级用户, 建立角色, 建立 DB, 复制, 绕过RLS | {}
 test1    | 建立 DB                                    | {}
 test2    | 建立 DB, 无法登录                          | {}

将建立角色的权限赋值给test2

postgres=# alter role test2 with createrole;
 ALTER ROLE
 postgres=# \du
                             角色列表
 角色名称 |                    属性                    | 成员属于 
----------+--------------------------------------------+----------
 postgres | 超级用户, 建立角色, 建立 DB, 复制, 绕过RLS | {}
 test1    | 建立 DB                                    | {}
 test2    | 建立角色, 建立 DB, 无法登录                | {}

给角色修改密码：

postgres=# ALTER ROLE test2 WITH PASSWORD '654321';/*修改密码*/  
postgres=# ALTER ROLE test2 WITH PASSWORD '654321';
ALTER ROLE
postgres=# \du
                             角色列表
 角色名称 |                    属性                    | 成员属于 
----------+--------------------------------------------+----------
 postgres | 超级用户, 建立角色, 建立 DB, 复制, 绕过RLS | {}
 test1    | 建立 DB                                    | {}
 test2    | 建立角色, 建立 DB, 无法登录                | {}

设置角色的有效期：

postgres=# ALTER ROLE test2 VALID UNTIL 'JUL 7 14:00:00 2012 +8';
ALTER ROLE
postgres=# \du
                             角色列表
 角色名称 |                    属性                    | 成员属于 
----------+--------------------------------------------+----------
 postgres | 超级用户, 建立角色, 建立 DB, 复制, 绕过RLS | {}
 test1    | 建立 DB                                    | {}
 test2    | 建立角色, 建立 DB, 无法登录               +| {}
          | 密码有效直至2012-07-07 14:00:00+08         |

创建角色与用户

创建test3 角色和tste4 用户
CREATE ROLE test3;　　//默认不带LOGIN属性
CREATE USER test4;　　//默认具有LOGIN属性

postgres=# create role tets3;
ALTER ROLE tets3 with password '123456';
CREATE ROLE
postgres=# create user tets4;
ALTER ROLE tets4 with password '123456';
CREATE ROLE

postgres=# \du
                             角色列表
 角色名称 |                    属性                    | 成员属于 
----------+--------------------------------------------+----------
 postgres | 超级用户, 建立角色, 建立 DB, 复制, 绕过RLS | {}
 test1    | 建立 DB                                    | {}
 test2    | 建立角色, 建立 DB, 无法登录               +| {}
          | 密码有效直至2012-07-07 14:00:00+08         | 
 tets3    | 无法登录                                   | {}
 tets4    |                                            | {}

验证LOGIN属性

连接数据库

psql –h IP  -U tets3;    //不能登录
  ===
  root@debian:~# psql -h 127.0.0.1 -U tets3
  用户 tets3 的口令：
  psql: 致命错误:  不允许角色"tets3" 进行登录
  ===
  psql –h IP  -U tests;   //能登录
  ===
  root@debian:~# psql -h 127.0.0.1 -U tets4
  用户 tets4 的口令：
  psql: 致命错误:  数据库 "tets4" 不存在
  ===

  如果这样的话是可以登陆的
  root@debian:~# psql -h 127.0.0.1 -U tets4 -d postgres

  登陆tets3就不可用登陆
  root@debian:~# psql -h 127.0.0.1 -U tets3 -d postgres
  用户 tets3 的口令：
  psql: 致命错误:  不允许角色"tets3" 进行登录

修改test3 的权限，增加LOGIN权限

ALTER ROLE tets3 LOGIN ;
   ===
   postgres=# ALTER ROLE tets3 LOGIN;
   ALTER ROLE
   ===
   再次尝试就可以了
   root@debian:~# psql -h 127.0.0.1 -U tets3 -d postgres
   用户 tets3 的口令：
   psql (9.6.13)
   SSL 连接（协议：TLSv1.2，密码：ECDHE-RSA-AES256-GCM-SHA384，密钥位：256，压缩：关闭)
   输入 "help" 来获取帮助信息.

   ===

角色属性（Role Attributes）

	一个数据库角色可以有一系列属性，这些属性定义了他的权限。

	属性

	说明

	login

	只有具有 LOGIN 属性的角色可以用做数据库连接的初始角色名。

	superuser

	数据库超级用户

	createdb

	创建数据库权限

	createrole      

	允许其创建或删除其他普通的用户角色(超级用户除外)

	replication

	做流复制的时候用到的一个用户属性，一般单独设定。

	password

	在登录时要求指定密码时才会起作用，比如md5或者password模式，跟客户端的连接认证方式有关

	inherit
	===

创建用户时赋予角色属性

从pg_roles表里查看到的信息，在上面创建的tets3用户时，默认没有创建数据库等权限。

	psql -h 127.0.0.1 -U tets3 -d postgres

	CREATE DATABASE tets3;

	ERROR: permission denied to create database  没有权限创建数据库

	如果要在创建角色时就赋予角色一些属性，可以使用下面的方法。 
	首先切换到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@debian:~# psql -h 127.0.0.1 -U test6 -d postgres
	用户 test6 的口令：
	psql (9.6.13)
	SSL 连接（协议：TLSv1.2，密码：ECDHE-RSA-AES256-GCM-SHA384，密钥位：256，压缩：关闭)
	输入 "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;

PostgreSQL创建只读用户，带步骤和删除

现有数据postgres，db2，当前用户为root，schema为public，范例为创建对test2库public下所有表只有查询权限的readonly用户
一．创建
1. 创建只读用户：

CREATE USER readonly WITH ENCRYPTED PASSWORD 'readonly';

2. 设置默认事务只读：

alter user readonly set default_transaction_read_only=on;

3. 赋予用户连接数据库db2的权限：

GRANT CONNECT ON DATABASE db2 to readonly;

4. 切换到指定库db2：

\c db2

5. 赋予用户表、序列查看权限，进入指定db运行：
5.1. 把当前库现有的所有在public这个schema下的表的使用权限赋给用户readonly

GRANT USAGE ON SCHEMA public to readonly;

5.2. 默认把当前库之后新建在public这个schema下的表的使用权限赋给readonly

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

5.3. 赋予用户readonly所有public下的序列的查看权

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;

5.4. 赋予用户readonly所有public下的表的select权

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

二．删除
将刚才赋予的权限一一回收

1. 回收schema的usage权限

revoke USAGE ON SCHEMA public from readonly;

2. 回收public下所有表的查询权限：

revoke SELECT ON ALL TABLES IN SCHEMA public from readonly;

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

revoke SELECT ON ALL SEQUENCES IN SCHEMA public from readonly;

4. 回收默认权限

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

5. 关闭数据库连接权限

revoke CONNECT ON DATABASE foo from readonly;

6. 关闭默认只读事务设置
	alter user readonly set default_transaction_read_only=off;
	7. 查看权限是否为空了
	\ddp
	8. 通过管理员删除readonly用户：
	drop user readonly;

参考：
https://blog.csdn.net/qq_37568918/article/details/85339277
https://blog.csdn.net/qq_37568918/article/details/85339906
https://blog.csdn.net/eagle89/article/details/80363365