PostgreSQL – Granting SELECT Inside SCHEMA

permissionspostgresql

I have created a database, role and schema with this script:

-- -----------------------------------------------------------------------------
-- set variables ---------------------------------------------------------------
-- -----------------------------------------------------------------------------
\set dbname keycloak
\set schemaname :dbname
\set rolename :dbname

-- -----------------------------------------------------------------------------
-- create ROLE and Database ----------------------------------------------------
-- -----------------------------------------------------------------------------
CREATE ROLE :rolename WITH LOGIN ;
CREATE DATABASE :dbname WITH OWNER :rolename ;

-- -----------------------------------------------------------------------------
-- connect to DB, create SCHEMA & set privileges -------------------------------
-- -----------------------------------------------------------------------------
\c :dbname
REVOKE ALL ON SCHEMA public FROM PUBLIC ;
CREATE SCHEMA :schemaname AUTHORIZATION :rolename ;
ALTER ROLE :rolename SET search_path=:schemaname ;
GRANT ALL ON ALL TABLES IN SCHEMA :schemaname TO :rolename ;
REVOKE ALL ON DATABASE :dbname FROM PUBLIC ;

This all seems to work in regards to what the keycloak ROLE can do on the keycloak Database.

Now I want to GRANT SELECT to a ROLE keycloak_reader ON ALL TABLES inside that SCHEMA keycloak. Further more that role should be granted to other roles (which should inherit those SELECT ability [ideally including the search_path]).

I tried

GRANT CONNECT ON DATABASE keycloak TO keycloak_reader ;
\c keycloak
GRANT SELECT ON ALL TABLES IN SCHEMA keycloak TO keycloak_reader ;
ALTER ROLE keycloak SET search_path=keyckloak ;

I can connect, but can not SELECT anything (permission denied). Neither as keycloak_reader, nor as any of the roles which a members of keycloak_reader group.

What am I doing wrong?

Best Answer

In addition to permissions at the level of an object in the database (table, sequence, function, etc.), the user should have USAGE privilege on this schema to actually use any object within schema.

So you need to add:

grant usage on schema keyckloak to keycloak_reader;

It's pretty easy to miss, the error message will only say you don't have permissions on schema if you specify the table name along with the schema (not relying on search_path).

Configuration settings for a role cannot be inherited. This is mentioned in the documentation here:

Note that role-specific defaults attached to roles without LOGIN privilege are fairly useless, since they will never be invoked.

Related Question