postgresql關於許可權的總結

1、每個例項可以多個db，每個db有自己的owner，每個db下可以建立多個schema，每個schema有自己的owner，每個schema下可以建立多張表，每張表都有自己的owner
2、db owner不一定能操作其下面的某個schema
3、schema owner不一定能操作其下面的某張表
4、授予某個使用者select on all tables in schema XX時，需要先對使用者授權usage訪問schema XX，否則會出現報錯Invalid operation: permission denied for schema XX;
grant usage on schema s9 to owner_2;
grant select on all tables in schema s9 to owner_2;
--授權owner_2可以查詢s9下面的所有表，這種方式僅對已經存在的表有效。以後建立的表不會自動有隻讀許可權
5、以上4僅使用者只能查詢該schema下已經存在的表，無法查詢該schema下新建的表，如果想對該schema下新建的表也獲得許可權，需要對該schema的owner授權給使用者
alter default privileges for user s9_owner in schema s9 grant select on tables to owner_2;
--以後schema s9的owner s9_owner在schema s9下新建的表,使用者owner_2都可以訪問
alter default privileges in schema s9 grant select on tables to owner_2;
--當前使用者執行如上語句後，此使用者在s9下新建的任何表，owner_2都可以訪問（其他使用者使用者建立的表，owner_2不能訪問）
--上述語句不是這個意思：對於任何使用者在s9下新建的表，owner_2都可以訪問
alter default privileges for user user1,user2 in schema s9 grant select on tables to owner_2;
--以後user1,user2在schema s9下新建的表,使用者owner_2都可以訪問
備註：目前postgresql沒有一種方法，可以使以後任何使用者在s9下新建的表，owner_2都可以訪問。
6、pg_hba.conf 的執行順序是從上到下的,也就是上面的生效。pg_hba.conf是一個客戶端的認證的檔案，他限制的並不是許可權，而是你是隻能來自於哪裡，必須使用什麼認證方式
7、有時發現superuser居然沒有createdb許可權,pg_user或\du可以看到使用者是superuser但是沒有create db許可權
8、對檢視的授權方法，和table一樣
grant select on table schemaname.viewname to user1
9、以下兩種報錯的解決思路，得出結論：postgresql只能查到物件許可權，無法查詢系統許可權
Invalid operation: user "user1" cannot be dropped because the user has a privilege on some object;
ERROR:  role "role1" cannot be dropped because some objects depend on it
select * from information_schema.table_privileges where grantee='XX';--查到XX對所有表的物件許可權,但是查不到select on all tables這樣的系統許可權，也查不到對檢視的查詢許可權，所以revoke這些物件許可權後，還是會報上面的錯誤，怎麼查使用者的所有物件許可權和系統許可權呢？
SELECT relname,relacl FROM pg_class WHERE relacl::TEXT LIKE '%user1%'
--物件許可權，獲取自pg_class.relacl，注意它只包含了在pg_class的物件（這裡只有表、檢視、序列、索引、物化檢視、複合型別、TOAST表、外部表）
--系統許可權，postgresql沒有存放系統許可權的系統表或系統檢視， 也是說postgresql不像oracle一樣有系統許可權的概念
那麼函式、型別、語言、資料庫、表空間等的許可權參見pg_proc.proacl , pg_type.typacl , pg_language.lanacl , pg_database.datacl , pg_tablespace.spcacl  
10、pg_user、pg_authid、pg_roles、pg_auth_members的區別
pg_user只儲存使用者資訊
pg_authid、pg_roles沒啥區別，pg_roles是建立在pg_authid上的系統檢視，儲存角色資訊
pg_auth_members儲存角色的成員關係，即某個角色組包含了哪些其他角色
備註：建立角色，賦予了login許可權，則相當於建立了使用者，如果沒有賦予login許可權，則這個角色只能在pg_roles裡面看到，而在pg_user裡面看不到

db owner不一定能操作其下面的某個schema
schema owner不一定能操作其下面的某張表

1、superuser建立3個使用者dbuser1、schemauser1、schemauser2，授權使用者dbuser1具備create db許可權
create user dbuser1 createdb password '123456';
create user schemauser1 password '123456';
create user schemauser2 password '123456';

2、dbuser1建立DB1，superuser授權schemauser1、schemauser2在db1上有建立schema的許可權
\c - dbuser1
create database db1;
\c - postgres
grant create on database db1 to schemauser1;
grant create on database db1 to schemauser2;

3、schemauser1、schemauser2分別在db1上建立schema1、schema2，並建立表schema1.table1、schema2.table2
\c db1
\c - schemauser1
create schema schema1;
create table schema1.table1 (hid int);
insert into  schema1.table1 values (1),(2);
select * from schema1.table1;
\c - schemauser2
create schema schema2;
create table schema2.table2 (hid int);
insert into  schema2.table2 values (1),(2);
select * from schema2.table2;

4、superuser在db1.schema1、db1.schema2上建立表supertable1，supertable2
\c - postgres
create table schema1.supertable1 (hid int);
insert into  schema1.supertable1 values (1),(2);
select * from schema1.supertable1;
create table schema2.supertable2 (hid int);
insert into  schema2.supertable2 values (1),(2);
select * from schema2.supertable2;

5、驗證
5.1、dbuser1是否可以查詢schema1.table1、schema2.table2、schema1.supertable1、schema2.supertable2
不可以
5.2、dbuser1是否可以在schema1、schema2上建立表schema1.dbtable1、schema2.dbtable2     
不可以  
5.3、schemauser1是否可以查詢schema1.supertable1、schema2.table2、schema2.supertable2
不可以
5.4、schemauser2是否可以查詢schema2.supertable2、schema1.table1、schema1.supertable1
不可以

\c - dbuser1
db1=> select * from  pg_tables WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql_%' ORDER BY tablename;
 schemaname |  tablename  | tableowner  | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-------------+-------------+------------+------------+----------+-------------+-------------
 schema1    | supertable1 | postgre2    |            | f          | f        | f           | f
 schema2    | supertable2 | postgre2    |            | f          | f        | f           | f
 schema1    | table1      | schemauser1 |            | f          | f        | f           | f
 schema2    | table2      | schemauser2 |            | f          | f        | f           | f
(4 rows)

db1=> select * from schema1.table1;
ERROR:  permission denied for schema schema1
LINE 1: select * from schema1.table1;
db1=> select * from schema1.supertable1;
ERROR:  permission denied for schema schema1
LINE 1: select * from schema1.supertable1;

db1=> create table schema1.dbtable1 (hid int);
ERROR:  permission denied for schema schema1
LINE 1: create table schema1.dbtable1 (hid int);
db1=> create table schema2.dbtable2 (hid int);
ERROR:  permission denied for schema schema2
LINE 1: create table schema2.dbtable2 (hid int);














光授權select on all tables in schema，而沒有授權usage on schema，使用者無法查詢schema下的表

postgres=# create user testuser1 password '123456';
CREATE ROLE
postgres=# create user testuser2 password '123456';
CREATE ROLE

db1=# grant select on all tables in schema schema1 to testuser1;
GRANT
db1=# \c - testuser1
You are now connected to database "db1" as user "testuser1".
db1=> select count(*) from schema1.table1;
ERROR:  permission denied for schema schema1
LINE 1: select * from schema1.table1;
db1=> \c - postgres
db1=# grant usage on schema schema1 to testuser1;
GRANT
db1=# \c - testuser1
You are now connected to database "db1" as user "testuser1".
db1=> select count(*) from schema1.table1;
 count
-------
     2
(1 row)


db1=# grant usage on schema schema1 to testuser2;
GRANT
db1=# grant select on all tables in schema schema1 to testuser2;
GRANT
db1=# \c - testuser2
You are now connected to database "db1" as user "testuser2".
db1=> select count(*) from schema1.table1;
 count
-------
     2
(1 row)





schema下新建的表也能被授權使用者查詢，需要對該schema的owner授權給使用者,如下testuser1和testuser2都具備select on all tables in schema schema1，schema1的owner是schemauser1，schemauser1的許可權授給了testuser2，所以schemauser1在schema1新建的表，testuser2可以查詢，但是testuser1無法查詢

db1=> \c - postgres
db1=# alter default privileges for user schemauser1 in schema schema1 grant select on tables to testuser2;
db1=# \c - schemauser1
db1=> select * into schema1.table3 from schema1.table1;
db1=> \c - testuser1
You are now connected to database "db1" as user "testuser1".
db1=> select * from schema1.table3;
ERROR:  permission denied for table table3
db1=> \c - testuser2
You are now connected to database "db1" as user "testuser2".
db1=> select * from schema1.table3;
 hid
-----
   1
   2
(2 rows)



沒有createdb許可權，則無法建立database，有了createdb許可權還可以在自己建立的db下建立schema
postgres=# \c - testuser1
You are now connected to database "postgres" as user "testuser1".
postgres=> create database testdb;
ERROR:  permission denied to create database
postgres=>\c - postgres
postgres=# alter user testuser1 createdb;
postgres=# \c - testuser1
postgres=> create database testdb;
CREATE DATABASE
postgres=> \c testdb
You are now connected to database "testdb" as user "testuser1".
testdb=> create schema tests1;
CREATE SCHEMA


在其他db_ower的db下，沒有授權CREATE on database許可權的話，使用者無法建立schema，有了create許可權後，在自己建立的schema下可以建立表
testdb=> \c db1
You are now connected to database "db1" as user "testuser1".
db1=> create schema tests2;
ERROR:  permission denied for database db1
testdb=>\c - postgres
db1=# grant CREATE on database db1 to testuser1;
db1=# \c - testuser1
db1=> create schema tests2;
db1=> create table tests2.table1 (hid int);


在其他schema_owner的schema下，沒有CREATE on schema許可權的話，使用者無法建立表
db1=> \c - postgres
db1=# create schema tests3;
db1=# \c - testuser1
db1=> create table tests3.table (hid int);
ERROR:  permission denied for schema tests3
LINE 1: create table tests3.table (hid int);
db1=> \c - postgres
db1=# grant CREATE on schema tests3 to testuser1;
db1=> create table tests3.table (hid int);
CREATE TABLE




pg_hba.conf 上面的生效
pg_hba.conf 內容如下，則systemctl restart postgresql-11後，本地psql命令需要密碼
local   all             all                                     md5
local   all             all                                     trust

pg_hba.conf 內容如下，則systemctl restart postgresql-11後，本地psql命令不需要密碼
local   all             all                                     trust
local   all             all                                     md5