Setting up a database with minimum access rights

From: Bo Victor Thomsen <bo(dot)victor(dot)thomsen(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Setting up a database with minimum access rights
Date: 2020-04-26 16:58:07
Message-ID: b41dd282-c77f-1fe4-bb44-ef8308562c51@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message richard coleman 2020-04-26 17:08:28 Bug #3787 ???
Previous Message Ashok kumar Mani 2020-04-26 14:10:23 how to cross check the wal log count from primary and standby ?