PostgreSQL search_path 更改没有像宣传的那样工作
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7407404/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL search_path change not working as advertised
提问by talonsensei
I am using PostgreSQL 9.0.3 on RedHat. The database contains two schemas, publicand wh. I created a new role called django. I want this user to use the whschema as it's default.
我在 RedHat 上使用 PostgreSQL 9.0.3。该数据库包含两个模式，public和wh. 我创建了一个名为django. 我希望这个用户使用wh默认的架构。
Following the manual, I did:
按照手册，我做了：
ALTER USER django SET SEARCH_PATH TO wh, public;
This appears to work:
这似乎有效：
SHOW SEARCH_PATH;
search_path
-------------
wh, public
However, if I then do a \dt, only tables from the public schema are displayed. In the manual, changing the search path should have an immediate effect, and I should be able to access whtables without a prefix, but this is not the case. Logging in and out preserves the changes to search_pathbut does not show any change of behavior.
但是，如果我再执行 a \dt，则只会显示公共架构中的表。在手册中，更改搜索路径应该会立即生效，我应该可以访问wh没有前缀的表，但事实并非如此。登录和注销会保留对更改的更改，search_path但不会显示任何行为更改。
What am I missing?
我错过了什么？
回答by Erwin Brandstetter
This might solve your problem:
这可能会解决您的问题：
GRANT USAGE ON SCHEMA wh TO django;
(Or GRANT USAGE to any role which has django as a (direct or indirect) member.)
(Or GRANT ALL ... if that is what you want.)
（或 GRANT USAGE 给任何将 django 作为（直接或间接）成员的角色。）
（或 GRANT ALL ... 如果这是您想要的。）
Setting the search_pathinstructs Postgres to look for objects in the listed schemas. It does not grant permission to see what's there. If "django" does not have the necessary privileges, \dtmust not (and does not) show that information.
设置search_path指示 Postgres 在列出的模式中查找对象。它不允许查看那里的内容。如果“django”没有必要的权限，则\dt不得（也不）显示该信息。
On the other hand, if you have already tried as superuser (as per your comment on the previous suggestion), then this might not be it ...
另一方面，如果您已经尝试过超级用户（根据您对上一个建议的评论），那么这可能不是......
回答by Milen A. Radev
I just tested it on (just releases) 9.1 on Windows 64-bit and it worked as specified.
我刚刚在 Windows 64 位上的（刚刚发布）9.1 上对其进行了测试，并且它按规定工作。
Excerpt from the ALTER ROLEmanpage:
摘自ALTER ROLE联机帮助页：
The remaining variants change a role's session default for a configuration variable, either for all databases or, when the IN DATABASE clause is specified, only for sessions in the named database. Whenever the role subsequently starts a new session, the specified value becomes the session default, overriding whatever setting is present in postgresql.conf or has been received from the postgres command line. This only happens at login time; executing SET ROLE or SET SESSION AUTHORIZATION does not cause new configuration values to be set.
其余变体更改配置变量的角色会话默认值，无论是针对所有数据库，还是在指定 IN DATABASE 子句时，仅针对指定数据库中的会话。每当角色随后启动新会话时，指定的值将成为会话默认值，覆盖 postgresql.conf 中存在的任何设置或从 postgres 命令行接收的任何设置。这只发生在登录时；执行 SET ROLE 或 SET SESSION AUTHORIZATION 不会导致设置新的配置值。
(emphasis mine)
（强调我的）
回答by a_horse_with_no_name
That might be a limitation of the \dtcommand.
这可能是\dt命令的限制。
To verify that the search_path is working properly, try to run SELECT * FROM some_tablewhere some_tableis one that is located in the wh schema.
要验证 search_path 是否正常工作，请尝试运行wh 模式中的SELECT * FROM some_tablewhere some_tableis one。
回答by francs
For PostgreSQL , if a user connect a database and look for objects like a table , first it will looks for the schema just as the same name of user name ,if not found ,it will look for public schema, In your case, if you connect the database via django user, it will default looks for the schema django , but you want to the current schema is wh, so make the schema name and the role name the same, and than login the database as the role will sove your problem ,without typing the prefix, just have a try!
对于 PostgreSQL，如果用户连接数据库并查找表等对象，首先它将查找与用户名相同名称的模式，如果未找到，它将查找公共模式，在您的情况下，如果您通过 django 用户连接数据库，它会默认查找架构 django ，但您希望当前架构是 wh，因此使架构名称和角色名称相同，然后以角色身份登录数据库将解决您的问题, 不用输入前缀，试试吧！
回答by smithygreg
For me the problem was I was trying to set the search path in pgAdmin. For some reason it wasn't applying the changes to the search_path. (It kept setting the parameters on the database?)
I logged in via psql and ran the exact same commands and it worked. Maybe I did something wrong but this may help others if they are doing something wrong too :)
对我来说，问题是我试图在 pgAdmin 中设置搜索路径。出于某种原因，它没有将更改应用于 search_path。（它一直在数据库上设置参数？）
我通过 psql 登录并运行完全相同的命令并且它起作用了。也许我做错了什么，但如果他们也做错了，这可能会帮助其他人:)
