PostgreSQL Public 模式的风险及安全迁移问题小结_MsSqlserver

问题起因

前几天有群友在群里面咨询

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 模式是不推荐的做法，原因如下

依赖性问题：许多扩展、插件和默认的 postgresql 设置都假定 public 模式存在。如果直接修改 public 的名称，会导致这些依赖出现问题。
升级问题：未来如果 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：