【PgSQL日常管理】用户权限配置

创建开发库dpgsql 、测试库ppgsql、生产库ppgsql

创建开发库对象为例：

创建用户srv_qpgsql，控制连接数，赋予登录权限
create role srv_qpgsql with ENCRYPTED password 'srv_qpgsql' CONNECTION LIMIT 10 login;

可以限定链接数：alter user srv_qpgsql with CONNECTION LIMIT 20;#连接数限制
创建schema，并赋予所有者为用户srv_qpgsql
CREATE SCHEMA qpgsql authorization srv_qpgsql;

或者：ALTER SCHEMA qpgsql OWNER to srv_qpgsql;

当使用语句CREATE SCHEMA authorization srv_qpgsql; 此时的owner为创建schema的登录用户

创建schema表空间
mkdir -p /data/pgsql_5432/tbs_qpgsql
CREATE TABLESPACE tbs_qpgsql OWNER srv_qpgsql  LOCATION '/data/pgsql_5432/tbs_qpgsql/';
创建所有者是srv_qpgsql的数据库
CREATE DATABASE qpgsql WITH OWNER srv_qpgsql ENCODING UTF8 TEMPLATE template1 TABLESPACE tbs_qpgsql;

 

创建超级用户

CREATE ROLE dba superuser PASSWORD 'dba ' login;

创建用户

create role readonly with login password 'readonly';

根据业务需求，创建只读用户：

赋予用户连接数据库权限：

GRANT CONNECT ON DATABASE prod to r_user;

切换到指定业务数据库：

postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
prod　　  | srv_prod | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres   | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| | | | | postgres=CTc/postgres
(4 rows)

连接业务库

\c prod 

根据需求赋予相应的schema只读权限

prod=> \dn
List of schemas
Name | Owner
--------------------+----------
prod　　　　 | srv_prod
prod_log　　 | srv_prod
public 　　    | postgres

赋予用户表、序列查看权限：分别赋予prod,prod_log下对象只读权限

GRANT USAGE ON SCHEMA prod to r_user;

GRANT SELECT ON ALL SEQUENCES IN SCHEMA prod TO r_user;

GRANT SELECT ON ALL TABLES IN SCHEMA prod TO r_user;

 如果创建用户的时候，没有加login，需要增加赋权：ALTER USER R_USER LOGIN；

 

注意特殊用户：超级用户postgres和public

新建users1需要回收public，然后单独赋权

revoke usage on schema prod from public;

grant connect on database prod to user1;

给public赋权，是给所有人赋权

 

查询用户权限：

1、查看某用户的表权限
select * from information_schema.table_privileges where grantee='zeze';
2、查看usage权限表
select * from information_schema.usage_privileges where grantee='zeze';
3、查看存储过程函数相关权限表
select * from information_schema.routine_privileges where grantee='zeze';

 其他：

https://blog.csdn.net/ZSZSZSxh/article/details/103407800