PostgreSQL - 关系 [表] 不存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14489105/
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 - relation [table] does not exist
提问by Slinky
I just created a new user that I want to have access to a limited number of our public tables. The user is created and I granted privs to one public table for now. I then logged into the DB as that user and tried to run a SELECT on the table that the user should be able to get to but I must of missed a step or did something wrong because when I run the query I get:
我刚刚创建了一个新用户，希望能够访问有限数量的公共表。用户已创建，我暂时授予了一张公共表的权限。然后我以该用户身份登录数据库并尝试在用户应该能够访问的表上运行 SELECT 但我必须错过一个步骤或做错了什么，因为当我运行查询时我得到：
relation [table] does not exist
关系 [表] 不存在
Below are the steps I took, in order.
以下是我采取的步骤，按顺序。
CREATE USER pqb2b WITH PASSWORD 'foo'
select * from pg_user;
usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig
----------+----------+-------------+----------+-----------+----------+---- |
postgres | 10 | t | t | t | ******** | |
pgb2b | 17267767 | t | f | f | ******** | infinity |
(1 rows)
(1 行)
GRANT ALL on b_users to pgb2b;
SELECT
schemaname||'.'||tablename
FROM
pg_tables
WHERE
has_table_privilege (
'pgb2b',
schemaname||'.'||tablename,
'select'
)
AND
schemaname NOT IN (
'pg_catalog',
'information_schema'
);
public.b_users
(1 row)
~ postgres$ psql -U pgb2b mydb
psql (9.0.3)
Type "help" for help.
mydb=> select * from b_users;
ERROR: relation "b_users" does not exist
LINE 1: select * from b_users;
mydb=> \d+ b_users
Did not find any relation named "b_users".
回答by Slinky
Even though I was granting privileges to my pgb2b user, I forgot to specify usage for that user:
尽管我向 pgb2b 用户授予了权限，但我忘记为该用户指定用法：
GRANT usage on schema public to pgb2b;
This fixed the issue. I found this postabout setting up PostgreSQL user permissions very helpful.
这解决了这个问题。我发现这篇关于设置 PostgreSQL 用户权限的帖子非常有帮助。
回答by storm_m2138
Note for others who may see this, though it may not apply to this particular question --
请注意可能会看到此内容的其他人，尽管它可能不适用于此特定问题-
If you are not using the public schema, you need to update the search_path
如果您没有使用公共架构，则需要更新 search_path
SET search_path TO my_schema, public;
Credit: Why does PostgreSQL's \dt show only public schema tables?
