Postgres: Permission denied for schema even though grants were given

Question

I am running Postgres 10.4 and am currently baffled since I can't seem to grant access to a schema to another role.

What I want to do:

I have one role with one schema and want to access the schema and its tables from another role. So I did the usual (what worked with other schemas):

grant usage on schema myschema to newuser;

grant select on all tables in schema myschema to newuser;

Both of those statements were run as the owner of the schema. I didn't run into any errors while doing so.

When I log in as the newuser and try to select some data:

select * from myschema.table;

I get the error:

SQL Error [42501]: ERROR: permission denied for schema myschema

I can see that the newuser has the right privileges in the table "information_schema.role_table_grants"

It also worked with another role and another schema. I'm clueless.

Solution 1

Step 1
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA name_schema TO name_user;

Step 2
GRANT USAGE ON SCHEMA name_schema TO name_user;

Solution 2

It definitely works as posted in my question, the problem was that I didn't user the owner of the schema.

So always make sure you grant access to a schema from the owner role.

Comments

[Yunnosch]

Your post starting with "Try.." gives the impression of proposing an experiment to further analyse the problem, instead of actually solving it. Please either rephrase to turn this into an assertive answer, or create a conditional answer (like "Your problem might be caused by .... In that case the solution is to ...." or delete the answer and comment with a clarification question.

[ronit]

still facing the issue..Dont know whats happening!

[Tora Tora Tora]

Thanks, @Reaper_20. That worked for me.