淺談PostgreSQL用戶許可權
- 2020 年 5 月 19 日
- 筆記
- PostgreSQL
盜用德哥的圖;來詮釋下邏輯結構；PostgreSQL邏輯結構有4層：實例->資料庫->schema->資料庫對象
可以看出用戶不在PostgreSQL裡面；是獨立之外的object
# 創建用戶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;
