一、PostgreSQL 模式與權(quán)限管理

1、什么是模式？

在 PostgreSQL 中，模式（schema） 是數(shù)據(jù)庫(kù)內(nèi)的命名空間，用于組織表、序列、視圖等對(duì)象。每個(gè)數(shù)據(jù)庫(kù)可以包含多個(gè)模式，模式幫助隔離不同應(yīng)用或模塊的數(shù)據(jù)。例如，map 模式可能存儲(chǔ)地圖相關(guān)數(shù)據(jù)，而 spider 模式存儲(chǔ)爬蟲數(shù)據(jù)。

2、權(quán)限類型

PostgreSQL 的權(quán)限分為以下幾類：

• 模式權(quán)限：如 USAGE（訪問模式中的對(duì)象）和 CREATE（在模式中創(chuàng)建對(duì)象）。

• 表權(quán)限：如 SELECT（查詢）、INSERT（插入）、UPDATE（更新）、DELETE（刪除）等。

• 序列權(quán)限：如 USAGE（使用 nextval 或 setval）、SELECT（查詢 currval）。

權(quán)限可以通過(guò) GRANT 命令分配給角色（用戶或組），角色可以繼承權(quán)限，簡(jiǎn)化管理。

3、權(quán)限授予的基本流程

1、創(chuàng)建用戶或角色：

CREATE ROLE dzdtmap WITH LOGIN PASSWORD 'your_password';

2、授予模式權(quán)限以訪問對(duì)象。

3、授予對(duì)象權(quán)限（如表或序列）以執(zhí)行具體操作

二、授予權(quán)限

1、授予模式訪問權(quán)限

GRANT USAGE ON SCHEMA map TO dzdtmap;

• 作用：授予用戶 dzdtmap 對(duì) map 模式的 USAGE 權(quán)限。

• 意義：允許 dzdtmap 引用 map 模式中的對(duì)象（如 map.some_table），但不授予對(duì)這些對(duì)象的操作權(quán)限。

• 使用場(chǎng)景：當(dāng)用戶需要訪問模式中的表或序列，但具體操作權(quán)限需單獨(dú)授予。

• 注意：若無(wú) USAGE 權(quán)限，用戶嘗試訪問 map 模式對(duì)象會(huì)拋出 ERROR: permission denied for schema map。

2、授予表權(quán)限

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA map TO dzdtmap;

• 作用：授予 dzdtmap 對(duì) map 模式中所有現(xiàn)有表的全部權(quán)限，包括 SELECT、INSERT、UPDATE、DELETE 等。

• 意義：允許 dzdtmap 對(duì)這些表執(zhí)行任何操作，適合開發(fā)或測(cè)試環(huán)境。

• 限制：僅適用于執(zhí)行時(shí)已存在的表。新創(chuàng)建的表不會(huì)自動(dòng)繼承這些權(quán)限。

• 解決新表權(quán)限問題：使用 ALTER DEFAULT PRIVILEGES 為未來(lái)表設(shè)置默認(rèn)權(quán)限：

ALTER DEFAULT PRIVILEGES FOR ROLE map_owner IN SCHEMA map GRANT ALL PRIVILEGES ON TABLES TO dzdtmap;

其中 map_owner 是創(chuàng)建表的角色（通常是模式擁有者）。

3、授予序列權(quán)限

GRANT USAGE, SELECT ON SEQUENCE spider.zjzx_menu_id_seq TO dzdtmap;

• 作用：授予 dzdtmap 對(duì) spider 模式中序列 zjzx_menu_id_seq 的 USAGE 和 SELECT 權(quán)限。

• 權(quán)限說(shuō)明：

  • USAGE：允許使用 nextval()（獲取下一個(gè)值）或 setval()（設(shè)置值）。

  • SELECT：允許使用 currval()（查詢當(dāng)前值）。

• 使用場(chǎng)景：當(dāng) dzdtmap 需要為表生成唯一標(biāo)識(shí)符（如插入數(shù)據(jù)時(shí)使用序列）或查詢序列狀態(tài)。

• 注意：序列位于 spider 模式，而非 map，表明 dzdtmap 可能跨模式操作。

三、設(shè)置自增主鍵

1、創(chuàng)建序列

CREATE SEQUENCE jzzx_weather_forecast0717_id_seq
START WITH 4
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

• 作用：創(chuàng)建名為 jzzx_weather_forecast0717_id_seq 的序列。

• 參數(shù)說(shuō)明：

  • START WITH 4：序列從 4 開始。

  • INCREMENT BY 1：每次遞增 1。

  • NO MINVALUE 和 NO MAXVALUE：無(wú)最小值和最大值限制。

  • CACHE 1：每次只緩存 1 個(gè)值，適合低并發(fā)場(chǎng)景。

• 使用場(chǎng)景：為 jzzx_weather_forecast 表的 id 列生成唯一值。

2、 綁定序列到表列

alter table jzzx_weather_forecast alter column id set default nextval('jzzx_weather_forecast0717_id_seq');

• 作用：將 id 列的默認(rèn)值設(shè)置為序列的下一個(gè)值（nextval）。

• 意義：新插入的行將自動(dòng)獲取序列的下一個(gè)值，實(shí)現(xiàn)主鍵自增。

注意事項(xiàng)

權(quán)限授予操作完成后建議驗(yàn)證用戶權(quán)限是否生效，可以通過(guò)查詢系統(tǒng)表確認(rèn)權(quán)限狀態(tài)。

自增序列創(chuàng)建時(shí)需要注意當(dāng)前表的最大ID值，確保START WITH參數(shù)大于現(xiàn)有最大ID以避免沖突。序列名稱建議包含表名和日期標(biāo)識(shí)以便維護(hù)。

常用權(quán)限類型說(shuō)明

• USAGE: 允許使用模式中的對(duì)象
• SELECT: 查詢數(shù)據(jù)權(quán)限
• ALL PRIVILEGES: 所有操作權(quán)限

數(shù)據(jù)庫(kù)權(quán)限管理是系統(tǒng)安全的重要環(huán)節(jié)，應(yīng)根據(jù)最小權(quán)限原則分配用戶權(quán)限。自增序列是PostgreSQL實(shí)現(xiàn)自動(dòng)遞增主鍵的常用方法，比SERIAL類型更靈活可控。

總結(jié)

到此這篇關(guān)于PostgreSQL數(shù)據(jù)庫(kù)授權(quán)與自增序列操作的文章就介紹到這了,更多相關(guān)pgSQL數(shù)據(jù)庫(kù)授權(quán)與自增序列內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論