10

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.

kidman01
  • 835
  • 5
  • 17
  • 32
  • Maybe you are connected to different databases or different machines? Does `\dn+ myschema` (run from `psql`) yield the same output on both machines? Can you add the result to the question? – Laurenz Albe Oct 16 '18 at 08:14
  • 1
    Hi @LaurenzAlbe this pointed me to my mistake. The owner for the schema was another role. I messed up the schema before it seems. Granting USAGE to "newuser" from the actual owner of the schema worked. – kidman01 Oct 16 '18 at 09:12

2 Answers2

12

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;

Reaper_20
  • 131
  • 2
  • 6
  • 1
    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. – Yunnosch Mar 29 '20 at 05:47
  • 1
    still facing the issue..Dont know whats happening! – ronit May 13 '20 at 07:53
  • Thanks, @Reaper_20. That worked for me. – Tora Tora Tora Feb 09 '22 at 18:09
  • Who could ever know that granting all privileges wouldn't be enough... I may confirm, that `GRANT USAGE` is obligatory, after `GRANT ALL PRIVILEGES`, to let your user perform even `SELECT` – Václav Apr 07 '23 at 13:31
4

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.

kidman01
  • 835
  • 5
  • 17
  • 32