0

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?

3
  • grant usage on schema keyckloak to keycloak_reader;
    – Melkij
    Jun 1, 2023 at 15:19
  • thx, that is a step forward indeed @Melkij . However the search_path value ist not inherited apparently. Any idea, whether that is possible? if you post this as an answer, rather then a comment, I would be happy tp mark it as solution.
    – vrms
    Jun 1, 2023 at 15:30
  • seems you can not inherit the search_path (as per stackoverflow.com/questions/7810621/…)
    – vrms
    Jun 1, 2023 at 15:34

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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