Thread: Setting up a database with minimum access rights
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
On Sun, Apr 26, 2020 at 12:57 PM Bo Victor Thomsen <bo.victor.thomsen@gmail.com> wrote:
...
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"
Were all the above steps executed as "postgres", or was just the CREATE TABLE statement done as that user?
If it is all done as "postgres" superuser then it works for me. Your example ALTER DEFAULT PRIVILEGES without specifying the FOR ROLE only applies to objects created by the same user who executed the ALTER DEFAULT PRIVILEGES.
Cheers,
Jeff
Hi Jeff -
Spot on !! (Right hand reaching for the Postgres manual, left hand searching for "Brown paper bag to pull over head" )
I was executing the script with user "postgres" and creating the table with another user "greenadm_adm".
With the missing qualifier "FOR USER.." in the "ADD DEFAULT PRIVILEGES.. " command, the command only works for tables created by user "postgres".
Using the same user for both scripting and creating tables works.
I owe you a large cold beer (or whatever beverage you prefer) If we ever meet in person :-)
-- Med venlig hilsen / Kind regards Bo Victor Thomsen
Den 26-04-2020 kl. 19:37 skrev Jeff Janes:
On Sun, Apr 26, 2020 at 12:57 PM Bo Victor Thomsen <bo.victor.thomsen@gmail.com> wrote:...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"Were all the above steps executed as "postgres", or was just the CREATE TABLE statement done as that user?If it is all done as "postgres" superuser then it works for me. Your example ALTER DEFAULT PRIVILEGES without specifying the FOR ROLE only applies to objects created by the same user who executed the ALTER DEFAULT PRIVILEGES.Cheers,Jeff