PostgreSQL最佳实践：用户权限管理

作者：李晓光（黑哥）

昆明畅博科技有限公司

     问题的起因：QQ群里有人提问怎么不让用户拥有建表权限？创建一个新用户后，用该用户登录后可以创建表。

     用户默认情况下具有public权限，public默认具有创建和使用schema的权限，因此意味着可以在schema中创建对象（包括表）、列出schema中的对象，并在其权限允许时访问它们。

     所以创建数据库或schema完成后做的第一件事是

 1.1 创建数据库完成后

--回收schema的public权限

--revoke all on schema public from public; --正常情况使用这个

revoke all on schema public,tiger,tiger_data,topology from public; --使用postgis扩展使用这个

--从core数据库收回public的所有权限

revoke all on database core from public;
revoke all on database postgres from public;

--重新将schema授予数据库所有者

grant all on schema public,tiger,tiger_data,topology to 数据库所有者;

1.2 创建schema完成后

create schema history;
revoke all on schema history from public;

--重新将schema history授予数据库所有者

grant all on schema history to 数据库所有者;

     假设现在有一数据库core.

2.1 postgrese用户连接至core数据库

psql -h localhost -p 5432 -U postgres -d core

--创建一个名称sel的用户

create user sel with nosuperuser nocreatedb nocreaterole noinherit login noreplication nobypassrls password '123';

--允许连接至数据库core

grant connect on database core to sel;

--查看角色使用\duS+

\duS+

--或

\duS+ sel

--查看用户在schema的权限

\dp

--查看数据库的用户

\l+



**权限解释：**

r -- SELECT ("读")
w -- UPDATE ("写")
a -- INSERT ("追加")
d -- DELETE
D -- TRUNCATE
x -- REFERENCES
t -- TRIGGER
X -- EXECUTE
U -- USAGE
C -- CREATE
c -- CONNECT
T -- TEMPORARY
arwdDxt -- ALL PRIVILEGES （对于表，对其他对象会变化）

* -- 用于前述特权的授权选项

2.2 在另一个进程中运行

psql -h localhost -p 5432 -U sel -d core


drop table aaa;
create table aaa(
objectid integer
);
select * from features;

上面的sql报

ERROR:  table "aaa" does not exist
ERROR:  no schema has been selected to create in
ERROR:  relation "features" does not exist

2.3 用postgrese用户登录core数据库为sel用户授权

psql -h localhost -p 5432 -U postgres -d core

     您需要什么权限就授予什么权限，这里授予sel用户可以select public schema下的所有对象。

grant usage on schema public to sel;
grant select on all tables in schema public to sel;

2.4 再次转到在另一个进程中运行

psql -h localhost -p 5432 -U sel -d core


select * from features;
delete  from features;

     现在已经可以正常查询数据了，但是此用户只有查询权限。

3.1 使用postgrese用户连接至core数据库

     注意你授予用户什么权限必须在drop用户前先revoke，然后再删除，否则报依赖错。所以授权文件保存好以便将来删除用户，不然查询关联性非常麻烦。

psql -h localhost -p 5432 -U postgres -d core

--回收分配的权限并删除用户

revoke select on all tables in schema public from sel;
revoke usage on schema public from sel;
revoke connect on database core from sel;
drop role sel;

     另外，删除角色通常不仅仅是快速DROP ROLE的问题。角色拥有的任何对象都必须首先删除或重新分配给其他所有者；删除已用于拥有对象的角色的最一般方法是（见中文手册21.4）：

REASSIGN OWNED BY doomed_role TO successor_role;
DROP OWNED BY doomed_role;
DROP ROLE doomed_role;

I Love PG

关于我们
中国开源软件推进联盟PostgreSQL分会（简称：中国PG分会）于2017年成立，由国内多家PostgreSQL生态企业所共同发起，业务上接受工信部中国电子信息产业发展研究院指导。中国PG分会是一个非盈利行业协会组织。我们致力于在中国构建PostgreSQL产业生态，推动PostgreSQL产学研用发展。


欢迎投稿
做你的舞台，show出自己的才华 。
投稿邮箱：partner@postgresqlchina.com
                               
                                 ——愿能安放你不羁的灵魂

技术文章精彩回顾




PostgreSQL学习的九层宝塔
PostgreSQL职业发展与学习攻略
搞懂PostgreSQL数据库透明数据加密之加密算法介绍
一文读懂PostgreSQL-12分区表
一文搞懂PostgreSQL物化视图
PostgreSQL源码学习之：RegularLock
Postgresql源码学习之词法和语法分析
2019，年度数据库舍 PostgreSQL 其谁?
Postgres是最好的开源软件
PostgreSQL是世界上最好的数据库
从Oracle迁移到PostgreSQL的十大理由
从“非主流”到“潮流”，开源早已值得拥有

PG活动精彩回顾




创建PG全球生态！PostgresConf.CN2019大会盛大召开
首站起航！2019“让PG‘象’前行”上海站成功举行
走进蓉城丨2019“让PG‘象’前行”成都站成功举行
中国PG象牙塔计划发布，首批合作高校授牌仪式在天津举行
群英论道聚北京，共话PostgreSQL
相聚巴厘岛| PG Conf.Asia 2019  DAY0、DAY1简报
相知巴厘岛| PG Conf.Asia 2019 DAY2简报
独家|硅谷Postgres大会简报
PostgreSQL线上沙龙第一期精彩回顾
PostgreSQL线上沙龙第二期精彩回顾
PostgreSQL线上沙龙第三期精彩回顾
PostgreSQL线上沙龙第四期精彩回顾
PostgreSQL线上沙龙第五期精彩回顾
PostgreSQL线上沙龙第六期精彩回顾
直播回顾 | Bruce Momjian：原生分布式将在PG 14版本发布

PG培训认证精彩回顾




中国首批PGCA认证考试圆满结束，203位考生成功获得认证！
中国第二批PGCA认证考试圆满结束，115位考生喜获认证！
重要通知：三方共建，中国PostgreSQL认证权威升级！
近500人参与！首次PGCE中级、第三批次PGCA初级认证考试落幕！
通知：PostgreSQL技术能力电子证书上线！
2020年首批 | 中国PostgreSQL初级认证考试圆满结束