快捷導(dǎo)航

PostgreSQL Public 模式的風(fēng)險及安全遷移問題小結(jié)

更新時間：2024年10月31日 14:45:17 作者：樺仔

本文主要討論了PostgreSQL中public模式的問題和解決方案,public模式默認(rèn)對所有用戶開放訪問權(quán)限,容易發(fā)生命名沖突,且難以維護(hù)和隔離,修改或刪除它可能導(dǎo)致擴(kuò)展無法正常工作,為解決這問題,建議新建模式,將public模式下的所有業(yè)務(wù)對象遷移過去

問題起因

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

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

另外一位群友說到

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

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

安全性問題

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

命名沖突

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

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

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

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

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

能否重命名 public 模式

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

ALTER SCHEMA public RENAME TO you_schema;

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

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

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

如何解決這個問題

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

具體步驟

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

CREATE SCHEMA employee;

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

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

使用以下命令逐個遷移：

-- 遷移所有表
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;
-- 遷移所有存儲過程
ALTER PROCEDURE public.procedure_name SET SCHEMA employee;

使用 SQL 動態(tài)語句和 PL/pgSQL 編寫一個循環(huán)來批量遷移 public 模式中的所有表、視圖、函數(shù)和存儲過程到 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;
    -- 遷移所有存儲過程
    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ù)庫默認(rèn)使用 employee 模式。

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

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

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