問題起因

前幾天有群友在群里面咨詢

PG12，13，14，public模式是否可以刪除或改名?
因?yàn)檫@位群友的公司的PG規(guī)范做了修改，不讓使用public模式存放數(shù)據(jù)，但是遺留問題沒辦法。

另外一位群友說到

你還真不好動(dòng)public。擴(kuò)展的插件的函數(shù)大多默認(rèn)都在public 下。

PG中默認(rèn)的public模式帶來的問題

• 安全性問題

public 模式默認(rèn)對(duì)所有數(shù)據(jù)庫(kù)用戶都開放訪問權(quán)限。換句話說，所有連接到數(shù)據(jù)庫(kù)的用戶默認(rèn)都可以訪問 public 模式中的對(duì)象（除非你手動(dòng)修改權(quán)限）。

• 命名沖突

public 模式是所有用戶和所有擴(kuò)展默認(rèn)使用的模式，容易發(fā)生命名沖突。

• 可維護(hù)性和隔離性

使用 public 模式進(jìn)行業(yè)務(wù)操作會(huì)使數(shù)據(jù)庫(kù)的架構(gòu)設(shè)計(jì)顯得雜亂無章，隨著時(shí)間推移，尤其是在大型項(xiàng)目或多個(gè)項(xiàng)目共享數(shù)據(jù)庫(kù)時(shí)，public模式中的對(duì)象數(shù)量會(huì)急劇增加

• 版本和擴(kuò)展的兼容性問題

許多 PostgreSQL 擴(kuò)展默認(rèn)使用 public 模式，如果修改 public 模式或刪除它，可能會(huì)導(dǎo)致擴(kuò)展無法正常工作

能否重命名 public 模式

我們能不能通過下面命令對(duì)public 模式名重命名 ？

ALTER SCHEMA public RENAME TO you_schema;

實(shí)際上重命名 public 模式是不推薦的做法，原因如下

• 依賴性問題：許多擴(kuò)展、插件和默認(rèn)的 PostgreSQL 設(shè)置都假定 public 模式存在。如果直接修改 public 的名稱，會(huì)導(dǎo)致這些依賴出現(xiàn)問題。
• 升級(jí)問題：未來如果 PostgreSQL 版本升級(jí)，系統(tǒng)或新安裝的擴(kuò)展可能仍然依賴于 public 模式存在。

因此，最好的做法是保留 public 模式，但不在業(yè)務(wù)中使用它。

如何解決這個(gè)問題

實(shí)際上，我們可以使用遷移的方式，新建一個(gè)模式，然后把public模式下的所有業(yè)務(wù)對(duì)象遷移到新建模式下

具體步驟

第一步：創(chuàng)建新的模式

CREATE SCHEMA employee;

第二步：遷移所有對(duì)象：對(duì)表、視圖、函數(shù)、存儲(chǔ)過程等對(duì)象分別執(zhí)行 SET SCHEMA 操作，將它們從 public 模式遷移到 employee 模式。

遷移對(duì)象時(shí)小心依賴關(guān)系，如外鍵、索引、函數(shù)依賴等，遷移時(shí)需要確保這些依賴關(guān)系不被破壞

使用以下命令逐個(gè)遷移：

-- 遷移所有表
ALTER TABLE public.table_name SET SCHEMA employee;
-- 遷移所有視圖
ALTER VIEW public.view_name SET SCHEMA employee;
-- 遷移所有函數(shù)
ALTER FUNCTION public.function_name SET SCHEMA employee;
-- 遷移所有存儲(chǔ)過程
ALTER PROCEDURE public.procedure_name SET SCHEMA employee;

使用 SQL 動(dòng)態(tài)語(yǔ)句和 PL/pgSQL 編寫一個(gè)循環(huán)來批量遷移 public 模式中的所有表、視圖、函數(shù)和存儲(chǔ)過程到 employee 模式。

DO $$ 
DECLARE
    obj record;
BEGIN
    -- 遷移所有表
    FOR obj IN
        SELECT tablename
        FROM pg_tables
        WHERE schemaname = 'public'
    LOOP
        EXECUTE format('ALTER TABLE public.%I SET SCHEMA employee;', obj.tablename);
    END LOOP;
    -- 遷移所有視圖
    FOR obj IN
        SELECT viewname
        FROM pg_views
        WHERE schemaname = 'public'
    LOOP
        EXECUTE format('ALTER VIEW public.%I SET SCHEMA employee;', obj.viewname);
    END LOOP;
    -- 遷移所有函數(shù)
    FOR obj IN
        SELECT routine_name, routine_schema
        FROM information_schema.routines
        WHERE specific_schema = 'public'
    LOOP
        EXECUTE format('ALTER FUNCTION public.%I() SET SCHEMA employee;', obj.routine_name);
    END LOOP;
    -- 遷移所有存儲(chǔ)過程
    FOR obj IN
        SELECT routine_name, routine_schema
        FROM information_schema.routines
        WHERE specific_schema = 'public' AND routine_type = 'PROCEDURE'
    LOOP
        EXECUTE format('ALTER PROCEDURE public.%I() SET SCHEMA employee;', obj.routine_name);
    END LOOP;
END $$;

第三步：設(shè)置 search_path 通過調(diào)整 search_path 讓數(shù)據(jù)庫(kù)默認(rèn)使用 employee 模式。

search_path 的設(shè)置順序非常重要。

將 employee 模式放在前面，確保在業(yè)務(wù)操作時(shí)優(yōu)先查找 employee 模式的對(duì)象，而 public 作為備選模式保留（方便擴(kuò)展和插件的使用）。

可以修改 PostgreSQL 的 postgresql.conf 文件，或者在會(huì)話級(jí)別設(shè)置 search_path：

SET search_path TO employee, public;

第四步：考慮擴(kuò)展和插件

許多擴(kuò)展和插件默認(rèn)使用 public 模式，例如 PostGIS、pgcrypto 等。

為了避免問題，最好不要修改 public 模式，而是保持其作為擴(kuò)展使用的默認(rèn)模式。

為什么SQL Server 沒有這個(gè)問題

SQL Server 沒有像 PostgreSQL 那樣對(duì) public 模式的強(qiáng)烈依賴，并且其設(shè)計(jì)理念與 PostgreSQL 的 public 模式存在一些關(guān)鍵區(qū)別。

• 權(quán)限管理的不同

在 SQL Server 中，dbo 是默認(rèn)的 schema，所有數(shù)據(jù)庫(kù)用戶默認(rèn)情況下并不會(huì)擁有對(duì) dbo 這個(gè) schema 中對(duì)象的完全訪問權(quán)限。只有擁有 db_owner 角色的用戶才可以完全控制 dbo 這個(gè) schema。

也就是說，除非用戶顯式授予對(duì) dbo 中對(duì)象的訪問或修改權(quán)限，否則，普通用戶是不能隨意訪問或修改 dbo 這個(gè) schema 下的對(duì)象的。

相比之下，PostgreSQL 的 public 這個(gè) schema 在默認(rèn)情況下是對(duì)所有用戶開放的。這意味著所有用戶都可以在 public 這個(gè) schema 中創(chuàng)建對(duì)象，除非手動(dòng)限制權(quán)限。

PostgreSQL的設(shè)計(jì)會(huì)增加意外權(quán)限授予和數(shù)據(jù)泄露的風(fēng)險(xiǎn)，因此在 PostgreSQL 中有時(shí)需要避免使用 public schema。

• 模式設(shè)計(jì)理念的不同

在 PostgreSQL 中，public schema 設(shè)計(jì)為一個(gè)所有用戶共享的默認(rèn)命名空間，因此經(jīng)常發(fā)生命名沖突、權(quán)限管理不嚴(yán)等問題。

在 SQL Server 中，dbo 是為擁有數(shù)據(jù)庫(kù)完全控制權(quán)的用戶預(yù)留的默認(rèn)命名空間，通常普通用戶和 DBA 可以自行創(chuàng)建自定義 schema 來組織和隔離各自的數(shù)據(jù)庫(kù)對(duì)象。

參考文章

https://sdwh.dev/posts/2021/03/SQL-Server-What-Is-dbo/

https://www.ibm.com/support/pages/microsoft-sql-server-tables-get-generated-dbo-schema

https://www.postgresql.org/docs/current/ddl-schemas.html

https://www.crunchydata.com/blog/be-ready-public-schema-changes-in-postgres-15

到此這篇關(guān)于PostgreSQL Public 模式的風(fēng)險(xiǎn)以及安全遷移的文章就介紹到這了,更多相關(guān)PostgreSQL Public 模式遷移內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論