PostgreSQL-关系[表]不存在 [英] PostgreSQL - relation [table] does not exist
问题描述
我刚刚创建了一个新用户,我希望可以访问有限数量的公共表。创建用户后,我现在将priv授予了一个公共表。然后,我以该用户身份登录数据库,并尝试在该用户应该能够访问的表上运行SELECT,但是我必须错过某个步骤或做错了什么,因为当我运行查询时,我得到了:
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:
关系[表]不存在
以下是我按顺序执行的步骤。
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行)
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".
推荐答案
即使我向pgb2b用户授予了特权,我忘了为该用户指定用法:
Even though I was granting privileges to my pgb2b user, I forgot to specify usage for that user:
GRANT usage on schema public to pgb2b;
此问题已解决。我发现此帖子关于设置PostgreSQL用户权限非常有帮助。
This fixed the issue. I found this post about setting up PostgreSQL user permissions very helpful.
这篇关于PostgreSQL-关系[表]不存在的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!