Need help to strictly specify an ingester role with PostgreSQL

Question

For a data ingester service, I'd like to strictly restrict its access to the database in the following way. This ingester service must be able to:

• Create new tables to store new kind of data (i.e. a dynamic ingester able to store new type of data on-the-fly).
• Add a new column to one of the tables the ingester created (backward-forward schema compatibility only).
• Insert new rows in one of the tables the ingester created.

Another way to specify this ingester role is to look at what it shouldn't be able to do:

• DROP any table, including the tables it created
• SELECT, DELETE, UPDATE any rows in any table
• Remove or update a column in any table

I didn't find any way to satisfy all these constraints with PostgreSQL. For example, to Alter a table the ingester role need to own the table, but when you own a table you automatically have access to DELETE, SELECT, UPDATE, ... operations on this table. Ideally, I'd like all these tables owned by a database admin user. This admin user will be the only user allowed to execute any destructive operations.

Can we use a combination of trigger and function to satisfy those constraints or can we specify that only based on roles and grant operations?

Answer 1

Thanks Laurenz for your help.

I came up on the following solution based on a combination of EVENT TRIGGER and FUNCTION with SECURITY DEFINER owned by an admin user.

The EVENT TRIGGER detects any CREATE TABLE done by the ingester user and then call a function executed with the permissions of the admin user. This function changes the owner of the table and grants the insert and select permissions to the ingester and reporting users respectively. The ingester user is now able to create table and insert data into it. But this ingester is no longer able to read, update or drop this table. The reporting user is only able to read the table.

CREATE OR REPLACE FUNCTION trg_create_table_set_owner()
 RETURNS event_trigger
 LANGUAGE plpgsql 
 SECURITY DEFINER
AS $$
DECLARE
  obj record;
BEGIN
  FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() WHERE command_tag='CREATE TABLE' LOOP
    EXECUTE format('ALTER TABLE %s OWNER TO service_admin', obj.object_identity);
    EXECUTE format('GRANT INSERT ON TABLE %s TO ingester_role;', obj.object_identity);
    EXECUTE format('GRANT SELECT ON TABLE %s TO reporting_role;', obj.object_identity);
  END LOOP;
END;
$$;

CREATE EVENT TRIGGER trg_create_table_set_owner
 ON ddl_command_end
 WHEN tag IN ('CREATE TABLE')
 EXECUTE PROCEDURE trg_create_table_set_owner();

Answer 2

This is definitely impossible.

Whoever creates a table is the table owner and can alter and drop it.

Two ways forward:

• Event triggers like you suggest.

• Have the tables be owned by someone else and provide SECURITY DEFINER functions to manipulate them.