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:
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: