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

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

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

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

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

grant connect on database core to sel;

\duS+

\duS+ sel

\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 （对于表，对其他对象会变化）

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


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

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

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

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

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


select * from features;
delete from features;

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;

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