詳情 - sql,存儲過程,客户端,JavaScript,前端開發墨舞天涯博客

基於user模型

模式和用户同名

只有一個模式

# postgres用户連接
psql -U postgres -c "create user test password 'test';"
psql -U postgres -c "create database testdb with owner=test;" 
psql -U postgres -d testdb -c "CREATE SCHEMA test AUTHORIZATION test;"
#test用户連接創建自定義對象:
psql -U test -d testdb -c "create table test_t(id int)"

這樣的好處是，test用户連接testdb默認直接連接到了test模式，因為search_path默認值如下：

postgres=# select name,setting,unit,context from pg_settings where name ~* 'search_path';
    name     |     setting     | unit | context 
-------------+-----------------+------+---------
 search_path | "$user", public |      | user
(1 row)

test用户為讀寫用户，後期如果需要使用只讀用户，直接創建用户授權即可

-- 1、創建用户及指定密碼：
create user readonly with encrypted password 'readonly';
-- 2、設置用户默認事務只讀：
alter user readonly set default_transaction_read_only=on;
-- 3、賦予用户連接數據庫權限：
grant connect on database testdb to readonly;
-- 4、切換到指定數據庫：
\c testdb
-- 5、賦予用户權限，查看public模式下所有表：
grant usage on schema public to readonly;
alter default privileges in schema public grant select on tables to readonly;
-- 6、賦予指定模式下用户表、序列查看權限：
grant usage on schema test to readonly;
grant select on all sequences in schema test to readonly;
grant select on all tables in schema test to readonly;
alter default privileges in schema test grant select on tables to readonly;

有多個模式

創建數據庫

psql -U postgres -c "create user test password 'test';"
psql -U postgres -c "create database testdb with owner=test;"

創建業務用户和模式

psql -U postgres -c "create user user1 password 'user1';"
psql -U postgres -c "create user user2 password 'user2';"
psql -U postgres -d testdb -c "CREATE SCHEMA user1 AUTHORIZATION user1;"
psql -U postgres -d testdb -c "CREATE SCHEMA user2 AUTHORIZATION user2;"

給業務用户授權數據庫權限

psql -U postgres -c "grant all privileges on database testdb to user1;"
psql -U postgres -c "grant all privileges on database testdb to user2;"

需要幾個模式就創建幾個用户，用户和模式同名，用户只允許在和自己同名的模式下創建自定義對象，可以授權其他模式的讀權限

授權user2查詢user1模式下的查詢權限：

grant usage on schema user1 to user2;
grant select on all tables in schema user1 to user2;
alter default privileges in schema user1 grant select on tables to user2;

後期如果還需要創建只讀用户，和只有一個模式創建只讀用户的場景一樣

模式和用户不同名

-- 創建父角色（不能登錄，昨晚對象的屬主）
create role test;
-- 業務用户
create user test1 password 'test1';
create user test2 password 'test2';
grant test to test1,test2;
-- 創建數據庫
create database testdb with owner=test;
-- 創建模式
\c testdb
create schema test_schema authorization test;

業務用户test1連接testdb庫，創建自定義對象

-- psql -U test1 -d testdb
create table test_schema.test_t(id int);

注意此時test1用户創建的表test2用户無權訪問（表的屬主為test1）

可以把owner轉為test用户即可

reassign owned by test1 to test;

基於role模型

權限管理設計模型

該模型簡單有效，推薦絕大多數客户使用。

1個RDS PostgreSQL高權限賬號，具有所有權限，由少量資深DBA掌握。
項目或者團隊維度，1個資源owner賬號，2個基本Role：{project}_role_readwrite、{project}_role_readonly。
在Role的基礎上創建業務賬號：業務賬號user = role + login權限。
1個項目或團隊可以有多個Schema。權限分配儘量以Schema或Role為單位。
業務表請勿放到schema public中。因為PostgreSQL默認所有用户對schema public都有CREATE和USAGE權限。

權限規劃示例

本示例以項目維度進行權限管理示例，同樣也適用於團隊維度。

DBA擁有RDS PostgreSQL實例的高權限賬號，名稱是dbsuperuser。
業務項目名稱是rdspg，新建schema名稱是rdspg、rdspg_1。

項目中新增的資源owner賬號和Role規劃如下：

user/Role	schema中表權限	schema中存儲過程權限
rdspg_owner (user)，是唯一的項目資源owner賬號	DDL：CREATE、DROP、ALTER DQL：SELECT DML：UPDATE、INSERT、DELETE	DDL：CREATE、DROP、ALTER DQL：SELECT，調用存儲過程
rdspg_role_readwrite (role)	DQL：SELECT DML：UPDATE、INSERT、DELETE	DQL（SELECT，調用存儲過程），若存儲過程有DDL操作，會拋出權限相關錯誤。
rdspg_role_readonly (role)	DQL(SELECT)	DQL（SELECT，調用存儲過程），若存儲過程有DDL或者DML操作，會拋出權限相關錯誤。

新增業務賬號時，根據不同需求，採用如下管理模式創建：

rdspg_readwrite = rdspg_role_readwrite + login權限
rdspg_readonly = rdspg_role_readonly + login權限

配置步驟：

1、創建項目資源owner賬號rdspg_owner和項目Role。DBA使用dbsuperuser高權限賬號執行如下操作。

-- rdspg_owner 是項目管理賬號
CREATE USER rdspg_owner WITH LOGIN PASSWORD 'asdfy181BASDfadasdbfas';
-- 創建業務數據庫
create database testdb owner=rdspg_owner;
\c testdb

CREATE ROLE rdspg_role_readwrite;
CREATE ROLE rdspg_role_readonly;

-- 設置: 對於rdspg_owner 創建的表，rdspg_role_readwrite 有 DQL（SELECT）、DML（UPDATE、INSERT、DELETE）權限。
ALTER DEFAULT PRIVILEGES FOR ROLE rdspg_owner GRANT ALL ON TABLES TO rdspg_role_readwrite;

-- 設置: 對於rdspg_owner 創建的SEQUENCES，rdspg_role_readwrite 有 DQL（SELECT）、DML（UPDATE、INSERT、DELETE）權限。
ALTER DEFAULT PRIVILEGES FOR ROLE rdspg_owner GRANT ALL ON SEQUENCES TO rdspg_role_readwrite;

-- 設置: 對於 rdspg_owner 創建的表， rdspg_role_readonly 只有 DQL（SELECT）權限。
ALTER DEFAULT PRIVILEGES FOR ROLE rdspg_owner GRANT SELECT ON TABLES TO rdspg_role_readonly;

2、創建rdspg_readwrite、rdspg_readonly業務賬號。DBA使用dbsuperuser高權限賬號執行如下操作。

-- rdspg_readwrite只有 DQL（SELECT）、DML（UPDATE、INSERT、DELETE）權限。
CREATE USER rdspg_readwrite WITH LOGIN PASSWORD 'dfandfnapSDhf23hbEfabf';
GRANT rdspg_role_readwrite TO rdspg_readwrite;

-- rdspg_readonly只有 DQL（SELECT）權限。
CREATE USER rdspg_readonly WITH LOGIN PASSWORD 'F89h912badSHfadsd01zlk';
GRANT rdspg_role_readonly TO rdspg_readonly;

3、創建schema rdspg，並授權給項目Role。DBA使用dbsuperuser高權限賬號執行如下操作。

-- schema rdspg的owner是 rdspg_owner賬號
CREATE SCHEMA rdspg AUTHORIZATION rdspg_owner;

-- 授權ROLE相關SCHEMA訪問權限。
GRANT USAGE ON SCHEMA rdspg TO rdspg_role_readwrite;
GRANT USAGE ON SCHEMA rdspg TO rdspg_role_readonly;

**説明: **rdspg_readwrite和rdspg_readonly自動繼承了相關Role的權限變更，不需要再額外操作。

應用場景示例

場景1：使用rdspg_owner賬號：對schema rdspg中的表進行DDL（CREATE、DROP、ALTER）操作

\c testdb rdspg_owner
CREATE TABLE rdspg.test(id bigserial primary key, name text);
CREATE INDEX idx_test_name on rdspg.test(name);

場景2：使用 rdspg_readwrite/rdspg_readonly 賬號進行業務開發

業務開發遵循最小權限原則，儘量使用rdspg_readonly賬號，需要DML操作的地方才使用rdspg_readwrite賬號。這樣也方便在業務層做讀寫分離

説明：

業務層做讀寫分離，避免了自動讀寫分離中間件proxy帶來的額外成本和性能損耗。

即使目前還沒有使用只讀實例，也建議區分 readonly客户端、readwrite客户端，為使用只讀實例做準備。readonly客户端建議使用readonly賬號，最小權限原則，規避權限誤用。

readonly客户端，使用readonly賬號，設置JDBC URL：只讀實例1地址,只讀實例2地址,讀寫實例地址。

readwrite客户端，使用readwrite賬號，設置JDBC URL：讀寫實例地址。

使用rdspg_readwrite賬號，對schema rdspg中的表進行DQL（SELECT）、DML（UPDATE、INSERT、DELETE）操作：

\c testdb rdspg_readwrite
INSERT INTO rdspg.test (name) VALUES('name0'),('name1');
SELECT id,name FROM rdspg.test LIMIT 1;

-- rdspg_readwrite沒有 DDL（CREATE、DROP、ALTER）權限
CREATE TABLE rdspg.test2(id int);
ERROR:  permission denied for schema rdspg
LINE 1: create table rdspg.test2(id int);

DROP TABLE rdspg.test;
ERROR:  must be owner of table test

ALTER TABLE rdspg.test ADD id2 int;
ERROR:  must be owner of table test

CREATE INDEX idx_test_name on rdspg.test(name);
ERROR:  must be owner of table test

使用rdspg_readonly賬號，對schema rdspg中的表進行DQL（SELECT）操作：

\c testdb rdspg_readonly
INSERT INTO rdspg.test (name) VALUES('name0'),('name1');
ERROR:  permission denied for table test

SELECT id,name FROM rdspg.test LIMIT 1;
 id | name
----+-------
  1 | name0
(1 row)

場景3：不同項目交叉授權

有個新項目employee，創建role和user以及授權如下：

\c testdb dbsuperuser
CREATE ROLE employee_role_readwrite;
CREATE ROLE employee_role_readonly;

ALTER DEFAULT PRIVILEGES FOR ROLE rdspg_owner GRANT ALL ON TABLES TO employee_role_readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE rdspg_owner GRANT ALL ON SEQUENCES TO employee_role_readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE rdspg_owner GRANT SELECT ON TABLES TO employee_role_readonly;

CREATE USER employee_readwrite WITH LOGIN PASSWORD 'dfandfnapSDhf23hbEfabf';
GRANT employee_role_readwrite TO employee_readwrite;
CREATE USER employee_readonly WITH LOGIN PASSWORD 'F89h912badSHfadsd01zlk';
GRANT employee_role_readonly TO employee_readonly;

現在需要為賬號employee_readwrite增加rdspg項目的表只讀權限。DBA使用dbsuperuser高權限賬號做如下操作：

-- 給賬號 employee_readwrite 加上 rdspg_role_readonly 權限集合。
\c testdb dbsuperuser
GRANT rdspg_role_readonly TO employee_readwrite;

場景4：項目新增 schema rdspg_1，並授權給項目Role

rdspg_readwrite、rdspg_readonly、employee_readwrite賬號自動繼承了相關Role的權限變更，不需要再額外操作。DBA使用dbsuperuser 高權限賬號做如下操作：

\c testdb dbsuperuser
CREATE SCHEMA rdspg_1 AUTHORIZATION rdspg_owner;

-- 授權ROLE相關SCHEMA訪問權限。
-- CREATE 使得 rdspg_owner 對schema rdspg_1中的表有 DDL（CREATE、DROP、ALTER）權限。
GRANT USAGE ON SCHEMA rdspg_1 TO rdspg_role_readwrite;
GRANT USAGE ON SCHEMA rdspg_1 TO rdspg_role_readonly;

賬號權限查詢

psql客户端，使用元命令\du查看所有用户和擁有的角色：

從上述查詢結果示例中可以看出：employee_readwrite賬號的Member of列中，內容為rdspg_role_readonly,employee_role_readwrite，因此，此賬號對employee項目表具有DQL和DML權限，對rdspg項目表具有DQL權限。

使用SQL查詢

SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;

參考資料

https://help.aliyun.com/zh/rds/apsaradb-rds-for-postgresql/manage-permissions-in-an-apsaradb-rds-for-postgesql-instance

https://support.huaweicloud.com/bestpractice-rds-pg/rds_pg_0035.html

本文章為轉載內容，我們尊重原作者對文章享有的著作權。如有內容錯誤或侵權問題，歡迎原作者聯繫我們進行內容更正或刪除文章。

墨舞天涯博客

墨舞天涯博客

博客 / 詳情

基於user模型

模式和用户同名

只有一個模式

有多個模式

模式和用户不同名

基於role模型

權限管理設計模型

權限規劃示例

應用場景示例

賬號權限查詢

參考資料

發佈評論

Product

Company

Support

Company

博客 / 詳情

基於user模型

模式和用户同名

只有一個模式

有多個模式

模式和用户不同名

基於role模型

權限管理設計模型

權限規劃示例

應用場景示例

賬號權限查詢

參考資料

發佈 評論

發佈評論