這篇文章主要介紹“postgresql關(guān)于權(quán)限的知識(shí)點(diǎn)有哪些”，在日常操作中，相信很多人在postgresql關(guān)于權(quán)限的知識(shí)點(diǎn)有哪些問(wèn)題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”postgresql關(guān)于權(quán)限的知識(shí)點(diǎn)有哪些”的疑惑有所幫助！接下來(lái)，請(qǐng)跟著小編一起來(lái)學(xué)習(xí)吧！

創(chuàng)新互聯(lián)建站成立于2013年，我們提供高端網(wǎng)站建設(shè)公司、網(wǎng)站制作、成都網(wǎng)站設(shè)計(jì)、網(wǎng)站定制、成都營(yíng)銷網(wǎng)站建設(shè)、小程序制作、微信公眾號(hào)開(kāi)發(fā)、seo優(yōu)化排名服務(wù)，提供專業(yè)營(yíng)銷思路、內(nèi)容策劃、視覺(jué)設(shè)計(jì)、程序開(kāi)發(fā)來(lái)完成項(xiàng)目落地，為成都混凝土攪拌罐車企業(yè)提供源源不斷的流量和訂單咨詢。

1、每個(gè)實(shí)例可以多個(gè)db，每個(gè)db有自己的owner，每個(gè)db下可以建立多個(gè)schema，每個(gè)schema有自己的owner，每個(gè)schema下可以創(chuàng)建多張表，每張表都有自己的owner
2、db owner不一定能操作其下面的某個(gè)schema
3、schema owner不一定能操作其下面的某張表
4、授予某個(gè)用戶select on all tables in schema XX時(shí)，需要先對(duì)用戶授權(quán)usage訪問(wèn)schema XX
grant usage on schema s9 to owner_2;
grant select on all tables in schema s9 to owner_2;
--授權(quán)owner_2可以查詢s9下面的所有表，這種方式僅對(duì)已經(jīng)存在的表有效。以后建立的表不會(huì)自動(dòng)有只讀權(quán)限
5、以上4僅用戶只能查詢?cè)搒chema下已經(jīng)存在的表，無(wú)法查詢?cè)搒chema下新建的表，如果想對(duì)該schema下新建的表也獲得權(quán)限，需要對(duì)該schema的owner授權(quán)給用戶
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都可以訪問(wèn)
6、pg_hba.conf 的執(zhí)行順序是從上到下的,也就是上面的生效。pg_hba.conf是一個(gè)客戶端的認(rèn)證的文件，他限制的并不是權(quán)限，而是你是只能來(lái)自于哪里，必須使用什么認(rèn)證方式
db owner不一定能操作其下面的某個(gè)schema
schema owner不一定能操作其下面的某張表

1、superuser建立3個(gè)用戶dbuser1、schemauser1、schemauser2，授權(quán)用戶dbuser1具備create db權(quán)限
create user dbuser1 createdb password '123456';
create user schemauser1 password '123456';
create user schemauser2 password '123456';

2、dbuser1創(chuàng)建DB1，superuser授權(quán)schemauser1、schemauser2在db1上有創(chuàng)建schema的權(quán)限
\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上創(chuàng)建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、驗(yàn)證
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);光授權(quán)select on all tables in schema，而沒(méi)有授權(quán)usage on schema，用戶無(wú)法查詢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下新建的表也能被授權(quán)用戶查詢，需要對(duì)該schema的owner授權(quán)給用戶,如下testuser1和testuser2都具備select on all tables in schema schema1，schema1的owner是schemauser1，schemauser1的權(quán)限授給了testuser2，所以schemauser1在schema1新建的表，testuser2可以查詢，但是testuser1無(wú)法查詢

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)
沒(méi)有createdb權(quán)限，則無(wú)法創(chuàng)建database，有了createdb權(quán)限還可以在自己創(chuàng)建的db下創(chuàng)建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下，沒(méi)有授權(quán)CREATE on database權(quán)限的話，用戶無(wú)法創(chuàng)建schema，有了create權(quán)限后，在自己建立的schema下可以創(chuàng)建表
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下，沒(méi)有CREATE on schema權(quán)限的話，用戶無(wú)法創(chuàng)建表
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 內(nèi)容如下，則systemctl restart postgresql-11后，本地psql命令需要密碼
local   all             all                                     md5
local   all             all                                     trust

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

到此，關(guān)于“postgresql關(guān)于權(quán)限的知識(shí)點(diǎn)有哪些”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)?lái)更多實(shí)用的文章！