Dear all -
I have a problem with the setup of access rights based on schemas.
The objective is to have a schema with default access rights to tables
assigned to the schema. And to strip the "public" user group for all
rights.
So if you create a table in a certain schema ("data_schema") it will be
generated with the rights defined using a "ALTER DEFAULT ..." command to
the schema. But I can't get it to work...
Here is the pertinent commands used:
-- Remove all access rights for "public"
REVOKE ALL ON SCHEMA public, data_schema FROM PUBLIC;
REVOKE ALL ON DATABASE greenadm FROM PUBLIC;
-- Create necessary ressource role
CREATE ROLE greenadm_read;
-- Access to database "greenadm" for role "greenadm_read"
GRANT CONNECT, TEMP ON DATABASE greenadm TO greenadm_read;
-- Access to schema for the ressource role
GRANT USAGE ON SCHEMA public, data_schema TO greenadm_read;
-- Default read access rights for ressource role
ALTER DEFAULT PRIVILEGES IN SCHEMA public, data_schema GRANT SELECT ON
TABLES TO greenadm_read;
ALTER DEFAULT PRIVILEGES IN SCHEMA public, data_schema GRANT SELECT ON
SEQUENCES TO greenadm_read;
ALTER DEFAULT PRIVILEGES IN SCHEMA public, data_schema GRANT EXECUTE ON
FUNCTIONS TO greenadm_read;
-- And a login user...
CREATE ROLE loki WITH LOGIN PASSWORD 'sneaky' VALID UNTIL '2021-01-01'
INHERIT;
GRANT greenadm_read TO loki;
After execution of the above commands, I create a table in schema
"data_schema" using the "postgres" superuser. But I can't access the
created table with user "loki"
However, if I execute these command after the table creation:
GRANT SELECT ON ALL TABLES IN SCHEMA public, data_schema TO
greenadm_read;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public, data_schema TO
greenadm_read;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public, data_schema TO
greenadm_read;
it works as expected with read acces to user "loki". it seem that the
problem is related to the "ALTER DEFAULT..." commands
I simply can't phantom what I'm doing wrong. (it's probably some newbie
error :-/ )
--
Med venlig hilsen / Kind regards
Bo Victor Thomsen