Postgres(10.3) permission issue

May 4, 2018 899 views
PostgreSQL Ubuntu 18.04

I have a PosgresQL(10.3) database(django). The database is created by user_owner, an has a complete structure but without rows data.

I want to access the database remotely. With the user_owner I can access remotely the database with almost no issues.

But, I created a secondary user, userremote. I want to use this user,instead of the userowner because I will give him temporary remote access, and the user_owner is used for backend code.

For this user I give the privileges:

 GRANT ALL PRIVILEGES ON DATABASE name_db TO remote_user;
 GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO remote_user;

When I try to connect with user_remote, remote , I can connect to the database, but If I try to do something else, like SELECT, I get an error:

[42501] ERROR: permission denied for relation table_name

If I connect in the database and give a table privileges, with:


GRANT ALL ON table_name TO user_remote;

works.

But, I have many tables, and what I want to achieve:

1) give access to userremote access to all tables in one/few steps, not table by table
2) I don't want user
remote to have all privileges, but just to do row operations(drop,update,insert)

1 Answer

These two commands should do the trick:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema_name> TO <username>;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <schema_name> TO <username>;

If you run into problems I would recommend removing the user you are working with and starting by creating a fresh user account and running these commands.

Have another answer? Share your knowledge.