PostgreSQL 权限解读

1 public权限解读

用户默认情况下具有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 数据库所有者;

2 测试

假设现在有一数据库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 删除用户

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;