淺談PostgreSQL用戶許可權

問題

　　經常在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;