postgres - two roles working on the same objects

Question

I have a DB with many objects. All are owned by a role called app. This role is currently used by everyone and everything (manual users, rollout scripts, app itself ...).

The goal now is to have a shorter idle_in_transaction_session_timeout for manual users than for the rollout scripts.

I was told that creating a new role for manual users is the way to go. For simplicity, let's say there is only one manual user frank.

The goal would be that frank can create/alter/drop every object he or app created/owns and that app can create/alter/drop every object he or frank created/owns.

This seems difficult in postgres due to the way it works (creator = owner, only owner can alter/drop etc...).

Searching the internet for a solution only brings partial and quite complicated solutions such as event trigger on create table or not forgetting to issue set role always.

What is the best practice here?

*** Update: one suggestion below is to grant app to frank, but that does not do the trick. It causes the scenario that app can not modify objects created by frank:

CREATE ROLE "app" LOGIN;

CREATE ROLE "frank" LOGIN;

GRANT "app" TO "frank";

set role app;

create table created_by_app (id int);

set role frank;

create table created_by_frank (id int);

drop table created_by_app; -- works

set role app;

drop table created_by_frank; -- does not work

-- SQL Error [42501]: ERROR: must be owner of table created_by_frank

Frank Heikens · Accepted Answer · 2023-02-14 11:30:25Z

0

Use a single owner, like the "app" role, and let other inherit this role. Something like this:

CREATE ROLE "app" LOGIN;

CREATE ROLE "frank" LOGIN;

GRANT "app" TO "frank";

When "app" is the owner of a table, "frank" can still make changes to this table or even drop this table.

An event trigger could solve the issue that user might forget to set the ownership of a table. The function and the trigger could be something like this:

CREATE OR REPLACE FUNCTION tr_set_table_owner()
RETURNS event_trigger
LANGUAGE plpgsql
AS
$$
DECLARE
    _table_name     TEXT;
    _schema_name    TEXT;
    _ddl_owner      TEXT;
BEGIN
        SELECT 
              objid::regclass
            ,   schema_name
        FROM    pg_event_trigger_ddl_commands()
        WHERE   object_type = 'table'
        AND     command_tag = 'CREATE TABLE'
        INTO _table_name, _schema_name;
        
        IF FOUND THEN
            _ddl_owner := format('ALTER TABLE %I.%I OWNER TO app;', _schema_name, _table_name);
            
            RAISE NOTICE 'DDL: %', _ddl_owner;
            
            EXECUTE  _ddl_owner;
        END IF;
END;
$$;


CREATE EVENT TRIGGER name
    ON ddl_command_end
    EXECUTE FUNCTION tr_set_table_owner();

Any new table created in your database, should now be assigned to the role "app". If you want this for other objects as well, just change the code in the trigger function.

edited Feb 14, 2023 at 11:30

answered Feb 14, 2023 at 10:26

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Peter Over a year ago

unfortunately this does not work, see my update above.

Frank Heikens Over a year ago

Role "frank" created a table, but didn't set the ownership at all, just used the defaults. An owner can assign an object to a different owner.

Peter Over a year ago

ty. but that would mean that my users like "frank" must never forget to assign ownership to app of everything they create. so this is asking for trouble ^^

Frank Heikens Over a year ago

The entire set up is imho asking for trouble, I would never ever have a bunch of users randomly create, alter or drop tables and other objects. You could create a trigger that sets the ownership, but that would solve just a small part of your problem.

Frank Heikens Over a year ago

@Peter: See the event trigger, this avoids the issue that a user might forget the ownership of a table.

Collectives™ on Stack Overflow

postgres - two roles working on the same objects

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related