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:
This textbox defaults to using Markdown to format your answer.
You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!
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.
Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.
Full documentation for every DigitalOcean product.
The Wave has everything you need to know about building a business, from raising funding to marketing your product.
Stay up to date by signing up for DigitalOcean’s Infrastructure as a Newsletter.
New accounts only. By submitting your email you agree to our Privacy Policy
Scale up as you grow — whether you're running one virtual machine or ten thousand.
Sign up and get $200 in credit for your first 60 days with DigitalOcean.*
*This promotional offer applies to new accounts only.