PostgreSQL - relation [table] does not exist

Question

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:

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)

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".

You must've edited that log; you ran select * from users and got relation b_users does not exist. Can you please show original and unedited logs? If the above is in fact unedited, please show the output of \d+ users and \d+ b_users. — Craig Ringer
– Craig Ringer, Commented Jan 24, 2013 at 3:00
Were you in the correct database when you ran you grant stuff? — Bohemian
– Bohemian ♦, Commented Jan 24, 2013 at 13:01

Slinky · Accepted Answer · 2013-01-24 14:53:39Z

18

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;

This fixed the issue. I found this post about setting up PostgreSQL user permissions very helpful.

edited Jan 24, 2013 at 14:53

answered Jan 24, 2013 at 14:27

Slinky

5,83216 gold badges81 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lutaaya Huzaifah Idris Over a year ago

am getting this error ERROR: role "pgb2b" does not exist SQL state: 42704

Community · Accepted Answer · 2017-05-23 10:27:45Z

3

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

SET search_path TO my_schema, public;

Credit: Why does PostgreSQL's \dt show only public schema tables?

edited May 23, 2017 at 10:27

CommunityBot

11 silver badge

answered Jun 24, 2016 at 21:10

storm_m2138

2,5493 gold badges21 silver badges18 bronze badges

Collectives™ on Stack Overflow

PostgreSQL - relation [table] does not exist

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related