問題
經(jīng)常在PG群里看到有人在問“為什么我對表賦予了權(quán)限；但是還是不能訪問表”
解析
若你看懂德哥這篇文章PostgreSQL邏輯結(jié)構(gòu)和權(quán)限體系介紹；上面對你就不是困擾你的問題
解決這個問題很簡單；在解決之前；我們要先了解PostgreSQL的邏輯結(jié)構(gòu)、以及與用戶之間的關(guān)系碴裙。
盜用德哥的圖;來詮釋下邏輯結(jié)構(gòu)仅讽；PostgreSQL邏輯結(jié)構(gòu)有4層：實例->數(shù)據(jù)庫->schema->數(shù)據(jù)庫對象
可以看出用戶不在PostgreSQL里面陶缺；是獨立之外的object；這個跟Oracle邏輯結(jié)構(gòu)不一致洁灵。它不屬于某個數(shù)據(jù)庫饱岸、或者某個schema。
若用戶不是數(shù)據(jù)庫屬主的用戶徽千；要訪問table1苫费；該怎么辦？有三步
- 首先你把數(shù)據(jù)庫connect的權(quán)限賦予用戶
- 再則你需要把table1所在的schema的使用權(quán)限賦予用戶
- 最后你需要把table的select的權(quán)限賦予
討論
現(xiàn)實驗環(huán)境
- 用戶：lottu1双抽、lottu2百框。
- 數(shù)據(jù)庫：db1
- schema：lottu1
- 表：tbl_lottu_01
# 創(chuàng)建用戶lottu1
postgres=# create user lottu1;
CREATE ROLE
# 創(chuàng)建用戶lottu2
postgres=# create user lottu2;
CREATE ROLE
# 創(chuàng)建數(shù)據(jù)庫db1；屬于lottu1
postgres=# create database db1 owner lottu1;
CREATE DATABASE
# 創(chuàng)建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
新建的數(shù)據(jù)庫對所有的用戶都有連接權(quán)限；不管是不是超級用戶慎菲、屬主用戶
db1=> \c db1 lottu2
You are now connected to database "db1" as user "lottu2".
針對這種情況嫁蛇；這樣是不是很不安全；非主用戶為啥不及可以連數(shù)據(jù)庫露该；還可以在對應(yīng)的public-schema下可以創(chuàng)建object睬棚。要實現(xiàn)隔離；我們可以回收數(shù)據(jù)庫權(quán)限解幼；只有超級用戶抑党、屬主用戶可以連。
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
現(xiàn)在實現(xiàn)用戶lottu2不能連接數(shù)據(jù)庫db1∧彀冢現(xiàn)在需求tbl_lottu_01給db2查詢底靠。而表tbl_lottu_01屬于數(shù)據(jù)庫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)
經(jīng)過一層層的賦權(quán)台汇；用戶lottu2可以select表tbl_lottu_01苛骨。若需求將schema-lottu1下所有的表都賦于給lottu2。將上面的修改下即可
grant select on ALL TABLES IN SCHEMA lottu1 to lottu2;
