Setting up a database with minimum access rights
От | Bo Victor Thomsen |
---|---|
Тема | Setting up a database with minimum access rights |
Дата | |
Msg-id | b41dd282-c77f-1fe4-bb44-ef8308562c51@gmail.com обсуждение исходный текст |
Ответы |
Re: Setting up a database with minimum access rights
|
Список | pgsql-admin |
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
В списке pgsql-admin по дате отправления: