PostgreSQL_通過schema控制使用者許可權

wait4friend發表於2019-05-11
SQL

背景環境

schema的特點

  • schema概念像名稱空間
  • schema下不能再有schema巢狀
  • 各個物件比如表,函式等存放在各個schema下
  • 同一個schema下不能有重複的物件名字,但在不同schema下可以重複

使用schema的作用

  • 方便管理多個使用者共享一個資料庫,但是又可以互相獨立.
  • 方便管理眾多物件,更有邏輯性
  • 方便相容某些第三方應用程式,建立物件時是有schema的

比如要設計一個複雜系統，由眾多模組構成，有時候模組間又需要有獨立性。各模組存放單獨的資料庫顯然是不合適的。 這時候使用schema來分類各模組間的物件，再對使用者進行適當的許可權控制，這樣邏輯也非常清晰。

常用資料庫中的schema異同

大多數資料庫都有schema或者同等意義的概念，但是含義和具體操作不同。

  • PostgreSQL中，一個database下可以有多個schema。可以給schema指定一個owner，如果沒有指定，那麼當前使用者就是schema的預設owner。
  • 在Oracle資料庫中不能直接新建一個schema，系統在建立一個使用者的同時為這個使用者建立一個同名的schem並作為該使用者的預設shcema。即schema的個數同user的個數相同，而且schema名字同user名字一一 對應並且相同。
  • 在MySQL中沒有schema，所以建立一個database的效果和建立一個schema是相同的。我們可以簡單的理解為，MySQL中的database就是schema。

本次測試軟體環境如下

CentOS 7 x64

PostgreSQL 11.1

許可權矩陣

我們需要在PostgreSQL中建立一個database，並且在這個db下建立多個schema。每個schema有自己的owner，並且db owner可以操作所有schema。

簡單的許可權關係矩陣如下

user \ schema S00 S01 S02
db_demo_owner （db owner 主使用者） Y Y Y
schema_owner_01（子使用者） N Y N
schema_owner_02（子使用者） N N Y

初始化資料庫

Superuser建立使用者和資料庫

-- 使用superuser登入(admin是提前建立的superuser，避免直接使用postgres)
psql --username=admin --dbname=postgres --password
複製程式碼
-- 新建使用者，這個使用者將成為Master使用者
drop user if exists db_demo_owner;
create user db_demo_owner with password 'xxx';

-- 新建一個子使用者
drop user if exists schema_owner_01;
create user schema_owner_01 with password 'xxx';

-- 通過設定許可權組的方式讓主使用者擁有子使用者的許可權	
-- db_demo_owner is member of schema_owner_01，即主使用者db_demo_owner擁有schema_owner_01的許可權
grant schema_owner_01 to db_demo_owner;

-- 另一個子使用者
drop user if exists schema_owner_02;
create user schema_owner_02 with password 'xxx';
grant schema_owner_02 to db_demo_owner;
複製程式碼
-- 新建一個測試用DB並分配給主使用者
drop database if exists db_demo;
create database db_demo with encoding='utf8' owner=db_demo_owner;
複製程式碼

主使用者建立schema（重點）

-- 主使用者登入
psql --username=db_demo_owner --dbname=db_demo --password
複製程式碼
-- 不指定schema owner，預設是當前使用者(主使用者)
drop schema s00 cascade;
create schema s00 ;	

-- 用主使用者建立schema並設定子使用者為owner
drop schema s01 cascade;
create schema s01 authorization schema_owner_01 ;

drop schema s02 cascade;
create schema s02 authorization schema_owner_02 ;
複製程式碼

主使用者建表

-- 主使用者登入
psql --username=db_demo_owner --dbname=db_demo --password

-- 主使用者在每個schema中建表t00
create table s00.t00(id int);
insert into s00.t00 values(1);	

create table s01.t00(id int);
insert into s01.t00 values(1);

create table s02.t00(id int);
insert into s02.t00 values(1);
複製程式碼

子使用者建表

不同子使用者在自己的schema中建表

-- 子使用者1 登入
psql --username=schema_owner_01 --dbname=db_demo --password	

create table s01.t01(id int);
insert into s01.t01 values(1);
複製程式碼
-- 子使用者2 登入
psql --username=schema_owner_02 --dbname=db_demo --password	

create table s02.t02(id int);
insert into s02.t02 values(1);
複製程式碼

驗證Owner

確認db owner

-- 主使用者是DB Owner
postgres=# \l db_demo
                                 List of databases
  Name   |     Owner     | Encoding |   Collate   |    Ctype    | Access privileges
---------+---------------+----------+-------------+-------------+-------------------
 db_demo | db_demo_owner | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(1 row)

-- 主使用者在子使用者組裡面，即已經擁有子使用者的許可權
postgres=# \du *owner*
                          List of roles
    Role name    | Attributes |             Member of
-----------------+------------+-----------------------------------
 db_demo_owner   |            | {schema_owner_01,schema_owner_02}
 schema_owner_01 |            | {}
 schema_owner_02 |            | {}
複製程式碼

確認schema owner

-- 三個schema分屬於不同使用者
db_demo=> \dn s*
    List of schemas
 Name |      Owner
------+-----------------
 s00  | db_demo_owner
 s01  | schema_owner_01
 s02  | schema_owner_02
(3 rows)
複製程式碼

確認表owner

-- 五張表分屬於不同的使用者
db_demo=> \dt s*.
            List of relations
 Schema | Name | Type  |      Owner
--------+------+-------+-----------------
 s00    | t00  | table | db_demo_owner
 s01    | t00  | table | db_demo_owner
 s01    | t01  | table | schema_owner_01
 s02    | t00  | table | db_demo_owner
 s02    | t02  | table | schema_owner_02
(5 rows)
複製程式碼

驗證訪問許可權

子使用者

沒有單獨授權的時候，子使用者僅能訪問自己的表

-- 子使用者1 登入
psql --username=schema_owner_01 --dbname=db_demo --password	

-- 無許可權
db_demo=> select * from s00.t00;
ERROR:  permission denied for schema s00

db_demo=> select * from s02.t02;
ERROR:  permission denied for schema s02

-- 有許可權
db_demo=> select * from s01.t01;
 id
----
  1
(1 row)
複製程式碼

主使用者

主使用者能夠訪問所有表

-- 主使用者登入
psql --username=db_demo_owner --dbname=db_demo --password

-- 都有許可權
db_demo=> select * from s00.t00;
 id
----
  1
(1 row)

db_demo=> select * from s01.t01;
 id
----
  1
(1 row)

db_demo=> select * from s02.t02;
 id
----
  1
(1 row)
複製程式碼

變更schema owner

s02原來屬於owner02，現在變更為owner01

-- 主使用者登入
psql --username=db_demo_owner --dbname=db_demo --password

-- 變更前
db_demo-> \dn s02
    List of schemas
 Name |      Owner
------+-----------------
 s02  | schema_owner_02
(1 row)

-- 變更schema的owner到另一個子使用者
alter schema s02 owner to schema_owner_01;

-- 變更後
db_demo=> \dn s02
    List of schemas
 Name |      Owner
------+-----------------
 s02  | schema_owner_01
(1 row)
複製程式碼

重要提示：僅僅變更owner並不能修改已有表的許可權，必須通過顯式賦權

-- 變更指定表owner
alter table s02.t02 owner to schema_owner_01;

-- 或者在不變更owner的情況下，批量賦權schema下的所有表
grant all on all tables in schema s02 to schema_owner_01;
複製程式碼

只讀許可權

經過前面的操作，子使用者02已經沒有任何訪問許可權了。我們希望這個子使用者在所有schema都有隻讀許可權。

-- 主使用者登入
psql --username=db_demo_owner --dbname=db_demo --password

-- 重要提示：這種方式僅對已經存在的表有效。以後建立的表不會自動有隻讀許可權
grant usage on schema s00, s01, s02 to schema_owner_02;
grant select on all tables in schema s00, s01, s02 to schema_owner_02;
複製程式碼

如果我們希望以後建立的所有新表都可以自動獲得只讀許可權（對已經存在的表無效），可以使用如下語句。

官方說明在這裡

-- 對子使用者01，02以後在schema s00，s01，s02下新建的表都有效
alter default privileges 
	for user schema_owner_01, schema_owner_02 
	in schema s00, s01, s02 
	grant select on tables to schema_owner_02;
複製程式碼

相關文章