Setting owner at CREATE TABLE

When you create a table in PostgreSQL, it gets assigned default permissions and a default owner. We can alter the default privileges using the very useful ALTER DEFAULT PRIVILEGES command (a PostgreSQL extension to the standard). However, there isn't much we can do about the owner, which will get set to the role that is currently active. That is, it's the main login role, or another role if the user has run the SET ROLE command before creating the table.

A fairly common scenario that is not well handled here is when a number of end-users are expected to cooperate on the tables in a schema all the way, including being able to create and drop them. And this is a scenario that is not very well handled by the built-in role support, due to the ownership handling. Fortunately, this is something where we can once again use an event trigger to make the system do what we need.

In trying to set up this scenario, we can get past the initial hurdle of permissions fairly quickly by issuing:

CREATE ROLE common_role;
GRANT common_role TO user1, user2, user3;
ALTER DEFAULT PRIVILEGES IN SCHEMA test GRANT ALL ON TABLES TO common_role;

With these commands, all users that are members of common_role (that is, user1, user2 and user3) will be able to access and modify all data in the tables once they are created. The only issue with this is if user1 creates a table, user2 won't be able to drop it:

You are now connected to database "postgres" as user "user1".
postgres=> CREATE TABLE test.t(a int);
CREATE TABLE
postgres=> \c postgres user2
You are now connected to database "postgres" as user "user2".
postgres=> DROP TABLE test.t;
ERROR:  must be owner of relation t

This is because the owner of the table in this case is user1. Of course we can work around this by requiring that user1 issue a SET ROLE common_role before creating the table:

You are now connected to database "postgres" as user "user1".
postgres=> SET ROLE common_role;
SET
postgres=> CREATE TABLE test.t(a int);
CREATE TABLE
postgres=> \c postgres user2
You are now connected to database "postgres" as user "user2".
postgres=> DROP TABLE test.t;
DROP TABLE

However, this isn't particularly user-friendly for the end users, and it's also quite easy to forget, which can then lead to issues down the road. So how do we fix this? Let's use an EVENT TRIGGER. We'll start by creating the function that will execute:

CREATE OR REPLACE FUNCTION trg_create_set_owner()
 RETURNS event_trigger
 LANGUAGE plpgsql
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 common_role', obj.object_identity);
  END LOOP;
END;
$$;

This function will loop over any objects create and issue an OWNER TO command for them, changing the owner from the default one to our hardcoded role common_role.

Then we connect this function to the event using an event trigger capturing all CREATE TABLE events:

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

Once this is in place, every new table created will have it's owner changed to the role common_role, and other members of the same role will be able to drop it.

You are now connected to database "postgres" as user "user1".
postgres=> create table test.t (a int);
CREATE TABLE
postgres=> \c postgres user2
You are now connected to database "postgres" as user "user2".
postgres=> drop table test.t;
DROP TABLE

Comments

I think it would be better to use the placeholder %I (capital i) instead of %s inside the format() function in order to properly deal with identifiers that would require quoting.

Posted on Jul 17, 2017 at 15:21 by Hans.

The identifier returned from object_identity is already quoted by the system, so you don't want it to be doubly-quoted.

Posted on Jul 17, 2017 at 15:25 by Magnus.

It's seems that event trigger will run several times on a create table command with primary/unique constraints. Adding WHEN tag IN ('CREATE TABLE') to definition of event trigger is not enough.

To restrict unnecessary executions we can retry this condition in the event trigger function. FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP IF obj.command_tag = 'CREATE TABLE' THEN EXECUTE format('ALTER TABLE %s OWNER TO common_role', obj.object_identity); END IF; END LOOP;

Posted on Jul 17, 2017 at 16:59 by Pavel Luzanov.

Nice catch!

However, I think the better solution is to just use a WHERE clause. I have updated the post to include that now.

Thanks!

Posted on Jul 17, 2017 at 17:03 by Magnus.

This is a great technique; thank you Magnus. I have seen this situation come up in non-prod environments with multiple developers and teams.

This technique makes it user-friendly for multiple people to be able to drop and create objects.

Posted on Jul 19, 2017 at 23:55 by Adam C. Scott.

Conferences

I speak at and organize conferences around Open Source in general and PostgreSQL in particular.

Upcoming

PGConf.EU 2017
Oct 24-27, 2017
Warsaw, Poland
PGConf.Asia
Dec 4-6, 2017
Tokyo, Japan

Past

Postgres Open 2017
Sep 6-8, 2017
San Francisco, USA
PGDay.RU
Jul 5-7, 2017
St Petersburg, Russia
PGDay.UK
Jul 4, 2017
London, UK
Amsterdam PUG
Jun 29, 2017
Amsterdam, Netherlands
PGCon 2017
May 23-26, 2017
Ottawa, Canada
More past conferences