問題起因

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

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

另外一位群友說到

你還真不好動public。擴展的插件的函數大多默認都在public 下。

PG中默認的public模式帶來的問題

• 安全性問題

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

• 命名沖突

public 模式是所有用戶和所有擴展默認使用的模式，容易發生命名沖突。

• 可維護性和隔離性

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

• 版本和擴展的兼容性問題

許多 PostgreSQL 擴展默認使用 public 模式，如果修改 public 模式或刪除它，可能會導致擴展無法正常工作

能否重命名 public 模式

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

ALTER SCHEMA public RENAME TO you_schema;

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

1. 依賴性問題：許多擴展、插件和默認的 PostgreSQL 設置都假定 public 模式存在。如果直接修改 public 的名稱，會導致這些依賴出現問題。
2. 升級問題：未來如果 PostgreSQL 版本升級，系統或新安裝的擴展可能仍然依賴于 public 模式存在。

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

如何解決這個問題

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

具體步驟

第一步：創建新的模式

CREATE SCHEMA employee;

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

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

使用以下命令逐個遷移：

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

使用 SQL 動態語句和 PL/pgSQL 編寫一個循環來批量遷移 public 模式中的所有表、視圖、函數和存儲過程到 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;
    -- 遷移所有函數
    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 $$;

第三步：設置 search_path 通過調整 search_path 讓數據庫默認使用 employee 模式。

search_path 的設置順序非常重要。

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

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

SET search_path TO employee, public;

第四步：考慮擴展和插件

許多擴展和插件默認使用 public 模式，例如 PostGIS、pgcrypto 等。

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

為什么SQL Server 沒有這個問題

SQL Server 沒有像 PostgreSQL 那樣對 public 模式的強烈依賴，并且其設計理念與 PostgreSQL 的 public 模式存在一些關鍵區別。

1. 權限管理的不同

在 SQL Server 中，dbo 是默認的 schema，所有數據庫用戶默認情況下并不會擁有對 dbo 這個 schema 中對象的完全訪問權限。只有擁有 db_owner 角色的用戶才可以完全控制 dbo 這個 schema。

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

相比之下，PostgreSQL 的 public 這個 schema 在默認情況下是對所有用戶開放的。這意味著所有用戶都可以在 public 這個 schema 中創建對象，除非手動限制權限。

PostgreSQL的設計會增加意外權限授予和數據泄露的風險，因此在 PostgreSQL 中有時需要避免使用 public schema。

2. 模式設計理念的不同

在 PostgreSQL 中，public schema 設計為一個所有用戶共享的默認命名空間，因此經常發生命名沖突、權限管理不嚴等問題。

在 SQL Server 中，dbo 是為擁有數據庫完全控制權的用戶預留的默認命名空間，通常普通用戶和 DBA 可以自行創建自定義 schema 來組織和隔離各自的數據庫對象。

轉自https://www.cnblogs.com/lyhabc/p/18475655