Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm getting the error ERROR: permission denied for relation users and have tried to update privileges using both

grant all privileges on all tables in schema ops to my_user GRANT ALL PRIVILEGES ON TABLE the_table TO my_user

In the Grants tab of DBVisualizer I can see that the my_user user has the privileges DELETE, INSERT, SELECT, TRUNCATE, and UPDATE. IS_GRANTABLE is set to NO for all of these. Yet I still get this error. What is there left to do.

share|improve this question
    
    
@Mihai That would just grant read only privileges, yes? Doesn't my GRANT ALL PRIVILEGES include that? –  thomas Jan 2 at 21:58
    
What's the statement that produces this permission denied error? –  Daniel Vérité Jan 2 at 23:04
    
@DanielVérité INSERT INTO my_schema.users ( user_id, username, email, role, fullname) VALUES (1, 'the_user', 'email@email.com', NULL, 'userfirst1 userlast1'), (2, 'the_use2r', 'email1@email.com', NULL, 'userfirst2 userlast2') –  thomas Jan 2 at 23:16
1  
OK. Now why do the GRANTs of the question target a different table (than users) and a different schema (than my_schema)? –  Daniel Vérité Jan 2 at 23:23
show 1 more comment

1 Answer 1

In addition to privileges on the TABLE, a role needs privileges for the SCHEMA and the DATABASE. The most likely candidate is the schema. If so, this additional GRANT would be the fix:

GRANT ALL ON SCHEMA my_schema TO my_user;

Or at least:

GRANT USAGE ON SCHEMA my_schema TO my_user;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.