Postgresql – Postgres: Permission denied for schema even though grants were given

grantpostgresql

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.

Best Answer

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;

Related Solutions

Postgresql – Grant all on a specific schema in the db to a group role in PostgreSQL

You found the shorthand to set privileges for all existing tables in the given schema. The manual clarifies:

(but note that ALL TABLES is considered to include views and foreign tables).

Bold emphasis mine. serial columns are implemented with nextval() on a sequence as column default and, quoting the manual:

For sequences, this privilege allows the use of the currval and nextval functions.

So if there are serial columns, you'll also want to grant USAGE (or ALL PRIVILEGES) on sequences

GRANT USAGE ON ALL SEQUENCES IN SCHEMA foo TO mygrp;

Note: identity columns in Postgres 10 or later use implicit sequences that don't require additional privileges. (Consider upgrading serial columns.)

What about new objects?

You'll also be interested in DEFAULT PRIVILEGES for users or schemas:

ALTER DEFAULT PRIVILEGES IN SCHEMA foo GRANT ALL PRIVILEGES ON TABLES TO staff;
ALTER DEFAULT PRIVILEGES IN SCHEMA foo GRANT USAGE          ON SEQUENCES TO staff;
ALTER DEFAULT PRIVILEGES IN SCHEMA foo REVOKE ...;

This sets privileges for objects created in the future automatically - but not for pre-existing objects.

Default privileges are only applied to objects created by the targeted user (FOR ROLE my_creating_role). If that clause is omitted, it defaults to the current user executing ALTER DEFAULT PRIVILEGES. To be explicit:

ALTER DEFAULT PRIVILEGES FOR ROLE my_creating_role IN SCHEMA foo GRANT ...;
ALTER DEFAULT PRIVILEGES FOR ROLE my_creating_role IN SCHEMA foo REVOKE ...;

Note also that all versions of pgAdmin III have a subtle bug and display default privileges in the SQL pane, even if they do not apply to the current role. Be sure to adjust the FOR ROLE clause manually when copying the SQL script.

Postgresql – Permission denied for relation

GRANT on the database is not what you need. Grant on the tables directly.

Granting privileges on the database mostly is used to grant or revoke connect privileges. This allows you to specify who may do stuff in the database if they have sufficient other permissions.

You want instead:

 GRANT ALL PRIVILEGES ON TABLE side_adzone TO jerry;

This will take care of this issue.

Best Answer

Related Solutions

Postgresql – Grant all on a specific schema in the db to a group role in PostgreSQL

What about new objects?

Postgresql – Permission denied for relation

Related Topic