Postgres permission denied for relation <table>

Question

I'm trying to learn Postgres and Ive made two basic tables and I can't join them together.

here is my list Of relations:

 Schema |     Name     |   Type   |  Owner
--------+--------------+----------+----------
 public | login        | table    | postgres
 public | login_id_seq | sequence | postgres
 public | users        | table    | test
(3 rows)

When I use the command

SELECT * FROM users JOIN login ON users.name = login.name;

I get

ERROR: permission denied for relation login

I have no idea what to do or what I did wrong.

Answer 1

You should grant the SELECT permission to user test:

GRANT SELECT ON login TO test;

If if might allow test to modify login, you should grant other permissions as well:

GRANT SELECT, INSERT, UPDATE, DELETE ON login TO test;

You should execute these statements as database owner or as user postgres. In general, you can use

psql -Upostgres -dtest

if you're running this command on the same machine where the Postgres server is running.

You may also change the ownership of login to test:

ALTER TABLE login OWNER TO test;
ALTER SEQUENCE login_id_seq OWNER TO test;

But have to execute this as user postgres as well.

Edit: You can try to change the user with

SET ROLE 'postgres';

as suggested by @lat long.

Answer 2

So this is what I did to finally get it to work...I basically just went into the login properties on pgAdmin4, found the owner and switched it to test and ran: SELECT * FROM users JOIN login ON users.name = login.name; and finally got what I was looking for. Surprisingly a simple fix.

Answer 3

The "test" user doesn't have permission to login and use the related tables. Run the query with the "postgres" user:

SET ROLE 'postgres';

Then run your query.