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, user_remote. I want to use this user,instead of the user_owner 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:
These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.
These two commands should do the trick:
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.