永久设置PostgreSQL模式路径

Question

我需要在Postgres中设置模式路径，这样我就不必每次都指定模式点表，比如schema2.table。 设置模式路径：

SET SCHEMA PATH a,b,c

在 Mac 上，我只能在一个查询会话中成功设置路径变量。关闭查询窗口后，路径变量会自动恢复为默认值。

我该如何使其永久生效？

- Nakh

---

我认为答案应该是 SET search_path TO a, b,c; 而不是 SET SCHEMA PATH a,b,c; - Way Too Simple

如果您正在使用search_path，那么可能会有风险：https://blog.bigsmoke.us/2022/11/11/postgresql-schema-search_path简而言之：不要在SECURITY DEFINER例程中使用search_path，确保您控制search_path中的所有模式，并且在依赖于search_path的SECURITY INVOKER例程中要小心，特别是这些例程执行生成随机密码重置令牌等操作时。 - BigSmoke

Answer 1

(如果您没有服务器管理员访问权限)

ALTER ROLE <your_login_role> SET search_path TO a,b,c;

两个重要的事情需要知道：

1. 当模式名称不简单时，需要用双引号括起来。
2. 设置默认模式的顺序 a, b, c 很重要，因为它也是查找表时所用的模式顺序。因此，如果在默认模式之间有相同的表名，则不会产生歧义，服务器始终会使用您指定的第一个模式中的表作为您的 search_path。

- Milen A. Radev

---

值得注意的是，不要在a、b、c枚举周围使用引号。我已经捶了15分钟的额头了... - Jmoney38

@Jmoney38 不要使用单引号，非简单架构名称需要使用双引号。 - vitaly-t

是的，那些是标识符，而不是字符串。 - Ludovic Kuty

对我来说，重新连接到数据库是必要的。在ALTER ROLE之后立即执行SHOW SEARCH_PATH似乎一开始没有起作用。 - erik-stengel

我不建议使用这个命令，因为它会修改该角色可以访问的所有数据库的搜索路径。 我建议使用ALTER DATABASE <data_base> set search_path TO a,b,c; - Cosmin Gruian

重新连接到 psql 也是必要的。 - erik-stengel

Answer 2

您可以在数据库级别设置默认的 search_path ：

ALTER DATABASE <database_name> SET search_path TO schema1,schema2;

或者在用户或角色级别上：

ALTER ROLE <role_name> SET search_path TO schema1,schema2;

或者在角色+数据库级别进行操作（感谢Chris指出！）：

ALTER ROLE <role_name> IN DATABASE <database_name> SET search_path TO schema1,schema2;

如果你在所有的数据库中都有一个共同的默认模式，那么你可以使用search_path选项在配置文件中设置系统范围内的默认值。

当创建数据库时，默认情况下它是从名为template1的隐藏“模板”数据库创建的，你可以更改该数据库以指定所有以后创建的数据库的新默认搜索路径。你也可以创建另一个模板数据库，并使用CREATE DATABASE <database_name> TEMPLATE <template_name>来创建你的数据库。

- joshperry

---

对于那些想知道如何从psql命令行列出模式的人，可以通过\dn命令来实现。 - BenKoshy

需要断开会话并重新连接以使设置生效。 - isapir

哦，祝福你。我从SQL Server转到PG，所以这对我来说都是新的（而且奇怪的）。 - Nate Anderson

文档将其标记为user_name或role_name。但我使用role_name（groupname）创建时无法正常工作，它给出错误提示：ERROR：权限拒绝，公共模式。我已经授予了公共使用和默认权限，但角色权限仍然无法正常工作。请问有任何想法或帮助吗？ - user1456618

Answer 3

Josh是正确的，但他忽略了一个变化：

ALTER ROLE <role_name> IN DATABASE <db_name> SET search_path TO schema1,schema2;

设置用户在一个特定数据库中的搜索路径。

- Chris Johnson

---

非常有用。谢谢。 - Alejandro Teixeira Muñoz

Answer 4

如果您在一台计算机上只使用一个数据库，可以将默认模式保存到~/.psqlrc中：

set search_path to a,b,c

这里有更多关于此的信息。如果你想要存储默认的连接信息，以便每次可以简单地运行psql，你需要使用环境变量：

export PGHOST=localhost
export PGPORT=5432
export PGDATABASE=database
export PGUSER=username

- Pluto