淺談PostgreSQL使用者許可權

lottu發表於2020-05-19
SQL

問題

　　經常在PG群裡看到有人在問“為什麼我對錶賦予了許可權；但是還是不能訪問表”

解析

　　若你看懂德哥這篇文章PostgreSQL邏輯結構和許可權體系介紹；上面對你就不是困擾你的問題

　　解決這個問題很簡單；在解決之前；我們要先了解PostgreSQL的邏輯結構、以及與使用者之間的關係。盜用德哥的圖;來詮釋下邏輯結構；PostgreSQL邏輯結構有4層：例項->資料庫->schema->資料庫物件

 

　　可以看出使用者不在PostgreSQL裡面；是獨立之外的object；這個跟Oracle邏輯結構不一致。它不屬於某個資料庫、或者某個schema。

　　若使用者不是資料庫屬主的使用者；要訪問table1；該怎麼辦？有三步

  • 首先你把資料庫select的許可權賦予使用者
  • 再則你需要把table1所在的schema的select許可權賦予使用者
  • 最後你需要把table的select的許可權賦予

討論

　　現實驗環境

  • 使用者：lottu1、lottu2。
  • 資料庫：db1
  • schema：lottu1
  • 表：tbl_lottu_01
# 建立使用者lottu1
postgres=# create user lottu1;
CREATE ROLE
# 建立使用者lottu2
postgres=# create user lottu2;
CREATE ROLE
# 建立資料庫db1；屬於lottu1
postgres=# create database db1 owner  lottu1;
CREATE DATABASE
# 建立schema、table、並插入記錄
postgres=# \c db1 lottu1;
You are now connected to database "db1" as user "lottu1".
db1=> create schema lottu1;
CREATE SCHEMA
db1=> create table tbl_lottu_01(id int, info text, reg_time timestamp);
CREATE TABLE
db1=> insert into tbl_lottu_01 select 1,'lottu',now();
INSERT 0 1

　　新建的資料庫對所有的使用者都有連線許可權；不管是不是超級使用者、屬主使用者

db1=> \c db1 lottu2
You are now connected to database "db1" as user "lottu2".

　　針對這種情況；這樣是不是很不安全；非主使用者為啥可以連資料庫；雖然它不可以做任何操作。但是覺得還是沒有完全隔離。要實現隔離；我們可以回收資料庫許可權；只有超級使用者、屬主使用者可以連。

db1=> \c db1 postgres
You are now connected to database "db1" as user "postgres".
db1=# revoke CONNECT ON DATABASE db1 from public;
REVOKE
db1=# \c db1 postgres
You are now connected to database "db1" as user "postgres".
db1=# \c db1 lottu1;
You are now connected to database "db1" as user "lottu1".
db1=> \c db1 lottu2;
FATAL:  permission denied for database "db1"
DETAIL:  User does not have CONNECT privilege.
Previous connection kept

　　現在實現使用者lottu2不能連線資料庫db1。現在需求tbl_lottu_01給db2查詢。而表tbl_lottu_01屬於資料庫db1下面schema-lottu1的。

db1=> grant CONNECT ON DATABASE db1 to lottu2;
GRANT
db1=> grant USAGE ON SCHEMA lottu1 to lottu2;
GRANT
db1=> grant select on TABLE tbl_lottu_01 to lottu2;
GRANT
db1=> \c db1 lottu2;
You are now connected to database "db1" as user "lottu2".
db1=> select * from lottu1.tbl_lottu_01;
 id | info  |          reg_time          
----+-------+----------------------------
  1 | lottu | 2020-05-19 10:50:15.206569
(1 row)

　　經過一層層的賦權；使用者lottu2可以select表tbl_lottu_01。若需求將schema-lottu1下所有的表都賦於給lottu2。將上面的修改下即可

grant select on ALL TABLES IN SCHEMA lottu1 to lottu2;

相關文章