postgresql關于權限的知識點有哪些

這篇文章主要介紹“postgresql關于權限的知識點有哪些”，在日常操作中，相信很多人在postgresql關于權限的知識點有哪些問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”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
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都可以訪問
6、pg_hba.conf 的執行順序是從上到下的,也就是上面的生效。pg_hba.conf是一個客戶端的認證的文件，他限制的并不是權限，而是你是只能來自于哪里，必須使用什么認證方式
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

到此，關于“postgresql關于權限的知識點有哪些”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！