基于PostgreSQL 權(quán)限解讀

--回收schema的public權(quán)限
--revoke all on schema public from public; --正常情況使用這個
revoke all on schema public,tiger,tiger_data,topology from public; --使用postgis擴展使用這個
--從core數(shù)據(jù)庫收回public的所有權(quán)限
revoke all on database core from public;
revoke all on database postgres from public;
--重新將schema授予數(shù)據(jù)庫所有者
grant all on schema public,tiger,tiger_data,topology to 數(shù)據(jù)庫所有者;

create schema history;
revoke all on schema history from public;
--重新將schema history授予數(shù)據(jù)庫所有者
grant all on schema history to 數(shù)據(jù)庫所有者;

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

--創(chuàng)建一個名稱sel的用戶
create user sel with nosuperuser nocreatedb nocreaterole noinherit login noreplication nobypassrls password '123';
--允許連接至數(shù)據(jù)庫core
grant connect on database core to sel;

--查看角色使用\duS+
\duS+
--或
\duS+ sel

--查看用戶在schema的權(quán)限
\dp

--查看數(shù)據(jù)庫的用戶
\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 （對于表，對其他對象會變化）
* -- 用于前述特權(quán)的授權(quán)選項

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

--回收分配的權(quán)限并刪除用戶
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;

psql -U username -d database_name -h host -W

su username
psql

createuser username 

CREATE ROLE rolename;

CREATE USER username;

CREATE ROLE role_name WITH optional_permissions;

CREATE ROLE username WITH LOGIN;

ALTER ROLE username WITH attribute_options;

ALTER ROLE username WITH NOLOGIN;

GRANT permission_type ON table_name TO role_name;

GRANT UPDATE ON demo TO demo_role; --賦予demo_role demo表的update權(quán)限
GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC to demo_role; --賦予demo_role所有表的SELECT權(quán)限

GRANT ALL ON demo TO demo_role; --賦給用戶所有權(quán)限
GRANT SELECT ON demo TO PUBLIC; --將SELECT權(quán)限賦給所有用戶

REVOKE permission_type ON table_name FROM user_name;

CREATE ROLE temporary_users;
GRANT temporary_users TO demo_role;
GRANT temporary_users TO test_user;

SET ROLE role_name; --切換到role_name用戶
RESET ROLE; --切換回最初的role

ALTER ROLE test_user INHERIT;

DROP ROLE role_name;
DROP ROLE IF EXISTS role_name;