From: Bo Victor Thomsen <bo.victor.thomsen@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Setting up a database with minimum access rights
Date: Sun, 26 Apr 2020 18:58:07 +0200
Message-ID: <b41dd282-c77f-1fe4-bb44-ef8308562c51@gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: bo.victor.thomsen@gmail.com
Subject: Re: Setting up a database with minimum access rights
In-Reply-To: <b41dd282-c77f-1fe4-bb44-ef8308562c51@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox