5

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.

Improve this question

3 Answers 3

Reset to default
6

You should grant the SELECT permission to user test:

GRANT SELECT ON login TO test;

If you 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.

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

10 Comments

i get the same error of permission denied for relation login
What is the result of the GRANT statement, and what is the user you're connected with?
With the GRANT statement i get: permission denied for relation login my terminal says psql -U test. how would i figure out the user im connected with?
test cannont grant permissions to a table that doesn't belong to him. You should connect as user postgres with psql -Upostgres -d<database name>. This should work if you're on the same server where the Postgres server is running.
I get ``` ERROR: syntax error at or near "psql" LINE 1: psql -Upostgres -d test; ```
|
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.

Improve this answer

Comments

0

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.

Improve this answer

2 Comments

While changing the role with that command is helpful for modifying the tables, it isn't a good idea to use postgres as an ordinary database user. Postgres has the permission to do everything like the root user on UNIX. As a postgres user you can easily and unintentionally destroy any schema elements.
Yes obviously it is not best practice. "test" user should be given privileges on the concerned table ideally.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.