To: pgsql-admin@postgresql.org
From: Bo Victor Thomsen <bo.victor.thomsen@gmail.com>
Subject: Setting up a database with minimum access rights
Message-ID: <b41dd282-c77f-1fe4-bb44-ef8308562c51@gmail.com>
Date: Sun, 26 Apr 2020 18:58:07 +0200
User-Agent: Mozilla/5.0 (Windows NT 6.3; WOW64; rv:68.0) Gecko/20100101
 Thunderbird/68.7.0
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-GB
Precedence: bulk

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