經(jīng)常在PG群里看到有人在問(wèn)“為什么我對(duì)表賦予了權(quán)限；但是還是不能訪問(wèn)表”
若你看懂德哥這篇文章PostgreSQL邏輯結(jié)構(gòu)和權(quán)限體系介紹；上面對(duì)你就不是困擾你的問(wèn)題
解決這個(gè)問(wèn)題很簡(jiǎn)單；在解決之前；我們要先了解PostgreSQL的邏輯結(jié)構(gòu)、以及與用戶之間的關(guān)系。盜用德哥的圖;來(lái)詮釋下邏輯結(jié)構(gòu)；PostgreSQL邏輯結(jié)構(gòu)有4層：實(shí)例->數(shù)據(jù)庫(kù)->schema->數(shù)據(jù)庫(kù)對(duì)象
可以看出用戶不在PostgreSQL里面；是獨(dú)立之外的object；這個(gè)跟Oracle邏輯結(jié)構(gòu)不一致。它不屬于某個(gè)數(shù)據(jù)庫(kù)、或者某個(gè)schema。
若用戶不是數(shù)據(jù)庫(kù)屬主的用戶；要訪問(wèn)table1；該怎么辦？有三步。這個(gè)類似走后門類似
現(xiàn)實(shí)驗(yàn)環(huán)境
# 創(chuàng)建用戶lottu1 postgres=# create user lottu1; CREATE ROLE # 創(chuàng)建用戶lottu2 postgres=# create user lottu2; CREATE ROLE # 創(chuàng)建數(shù)據(jù)庫(kù)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ù)庫(kù)對(duì)所有的用戶都有連接權(quán)限；且數(shù)據(jù)庫(kù)下public-schema對(duì)所有用戶都可以使用；不管是不是超級(jí)用戶、屬主用戶
db1=> \c db1 lottu2 You are now connected to database "db1" as user "lottu2".
針對(duì)這種情況；這樣是不是很不安全；非主用戶為啥可以連數(shù)據(jù)庫(kù)；非主用戶為啥不及可以連數(shù)據(jù)庫(kù)；還可以在對(duì)應(yīng)的public-schema下可以創(chuàng)建object。要實(shí)現(xiàn)隔離；我們可以回收數(shù)據(jù)庫(kù)權(quán)限；只有超級(jí)用戶、屬主用戶可以連。
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)在實(shí)現(xiàn)用戶lottu2不能連接數(shù)據(jù)庫(kù)db1?，F(xiàn)在需求tbl_lottu_01給db2查詢。而表tbl_lottu_01屬于數(shù)據(jù)庫(kù)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)過(guò)一層層的賦權(quán)；用戶lottu2可以select表tbl_lottu_01。若需求將schema-lottu1下所有的表都賦于給lottu2。將上面的修改下即可
grant select on ALL TABLES IN SCHEMA lottu1 to lottu2;
聯(lián)系客服
