ToB企服应用市场:ToB评测及商务社交产业平台

标题: 别再忽视！PostgreSQL Public 模式的风险以及安全迁徙 [打印本页]
作者: 丝    时间: 2024-10-31 06:50
标题: 别再忽视！PostgreSQL Public 模式的风险以及安全迁徙
别再忽视！PostgreSQL  Public 模式的风险以及安全迁徙

作者：桦仔 
10余年DBA工作履历
微信：debolop
QQ交流群：740052625
公众号：数据库实战派
问题起因

前几天有群友在群内里咨询
PG12，13，14，public模式是否可以删除或改名?
由于这位群友的公司的PG规范做了修改，不让利用public模式存放数据，但是遗留问题没办法。
另外一位群友说到
你还真不好动public。扩展的插件的函数大多默认都在public 下。


PG中默认的public模式带来的问题

public 模式默认对所有数据库用户都开放访问权限。换句话说，所有毗连到数据库的用户默认都可以访问 public 模式中的对象（除非你手动修改权限）。
public 模式是所有效户和所有扩展默认利用的模式，容易发生定名辩说。
利用 public 模式进行业务操作会使数据库的架构设计显得杂乱无章，随着时间推移，尤其是在大型项目或多个项目共享数据库时，public模式中的对象数目会急剧增加
很多 PostgreSQL 扩展默认利用 public 模式，如果修改 public 模式或删除它，可能会导致扩展无法正常工作

能否重定名 public 模式

我们能不能通过下面下令对public 模式名重定名 ？
  1. ALTER SCHEMA public RENAME TO you_schema;
复制代码
现实上重定名 public 模式是不推荐的做法，原因如下
因此，最好的做法是生存 public 模式，但不在业务中利用它。

如何办理这个问题

现实上，我们可以利用迁徙的方式，新建一个模式，然后把public模式下的所有业务对象迁徙到新建模式下
具体步骤
第一步：创建新的模式
  1. CREATE SCHEMA employee;
复制代码
第二步：迁徙所有对象：对表、视图、函数、存储过程等对象分别执行 SET SCHEMA 操作，将它们从 public 模式迁徙到 employee 模式。
迁徙对象时小心依赖关系，如外键、索引、函数依赖等，迁徙时需要确保这些依赖关系不被粉碎
利用以下下令逐个迁徙：
  1. -- 迁移所有表
  2. ALTER TABLE public.table_name SET SCHEMA employee;
  4. -- 迁移所有视图
  5. ALTER VIEW public.view_name SET SCHEMA employee;
  7. -- 迁移所有函数
  8. ALTER FUNCTION public.function_name SET SCHEMA employee;
  10. -- 迁移所有存储过程
  11. ALTER PROCEDURE public.procedure_name SET SCHEMA employee;
复制代码
利用 SQL 动态语句和 PL/pgSQL 编写一个循环来批量迁徙 public 模式中的所有表、视图、函数和存储过程到 employee 模式。
  1. DO $$ 
  2. DECLARE
  3.     obj record;
  4. BEGIN
  5.     -- 迁移所有表
  6.     FOR obj IN
  7.         SELECT tablename
  8.         FROM pg_tables
  9.         WHERE schemaname = 'public'
  10.     LOOP
  11.         EXECUTE format('ALTER TABLE public.%I SET SCHEMA employee;', obj.tablename);
  12.     END LOOP;
  14.     -- 迁移所有视图
  15.     FOR obj IN
  16.         SELECT viewname
  17.         FROM pg_views
  18.         WHERE schemaname = 'public'
  19.     LOOP
  20.         EXECUTE format('ALTER VIEW public.%I SET SCHEMA employee;', obj.viewname);
  21.     END LOOP;
  23.     -- 迁移所有函数
  24.     FOR obj IN
  25.         SELECT routine_name, routine_schema
  26.         FROM information_schema.routines
  27.         WHERE specific_schema = 'public'
  28.     LOOP
  29.         EXECUTE format('ALTER FUNCTION public.%I() SET SCHEMA employee;', obj.routine_name);
  30.     END LOOP;
  32.     -- 迁移所有存储过程
  33.     FOR obj IN
  34.         SELECT routine_name, routine_schema
  35.         FROM information_schema.routines
  36.         WHERE specific_schema = 'public' AND routine_type = 'PROCEDURE'
  37.     LOOP
  38.         EXECUTE format('ALTER PROCEDURE public.%I() SET SCHEMA employee;', obj.routine_name);
  39.     END LOOP;
  41. END $$;
复制代码
 
第三步：设置 search_path 通过调整 search_path 让数据库默认利用 employee 模式。
search_path 的设置顺序非常重要。
将 employee 模式放在前面，确保在业务操作时优先查找 employee 模式的对象，而 public 作为备选模式生存（方便扩展和插件的利用）。
可以修改 PostgreSQL 的 postgresql.conf 文件，或者在会话级别设置 search_path：
  1. SET search_path TO employee, public;
复制代码
 第四步：思量扩展和插件
很多扩展和插件默认利用 public 模式，例如 PostGIS、pgcrypto 等。
为了避免问题，最好不要修改 public 模式，而是保持其作为扩展利用的默认模式。

为什么SQL Server 没有这个问题

SQL Server 没有像 PostgreSQL 那样对 public 模式的猛烈依赖，而且其设计理念与 PostgreSQL 的 public 模式存在一些关键区别。
在 SQL Server 中，dbo 是默认的 schema，所有数据库用户默认环境下并不会拥有对 dbo 这个 schema 中对象的完全访问权限。只有拥有 db_owner 角色的用户才可以完全控制 dbo 这个 schema。
也就是说，除非用户显式授予对 dbo 中对象的访问或修改权限，否则，平凡用户是不能随意访问或修改 dbo 这个 schema 下的对象的。
相比之下，PostgreSQL 的 public 这个 schema 在默认环境下是对所有效户开放的。这意味着所有效户都可以在 public 这个 schema 中创建对象，除非手动限制权限。
PostgreSQL的设计会增加意外权限授予和数据泄露的风险，因此在 PostgreSQL 中有时需要避免利用 public schema。

在 PostgreSQL 中，public schema 设计为一个所有效户共享的默认定名空间，因此经常发生定名辩说、权限管理不严等问题。
在 SQL Server 中，dbo 是为拥有数据库完全控制权的用户预留的默认定名空间，通常平凡用户和 DBA 可以自行创建自定义 schema 来组织和隔离各自的数据库对象。
 
 

 
参考文章
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


 

本文版权归作者所有，未经作者同意不得转载。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4