Postgresql – How to create a user who could only create tables and can’t delete/drop them

permissionspostgresql

There's my problem. I'm using PostgreSQL and tried so many things to create a user who can create tables but can't drop them, it's like create a table but assign it to another user. I've tried one trigger but it can't solve my problem.

I've used a trigger that I found on another site, just changed the role where the owner of the table need to be replaced but it didn't work. When I try to create a table with another user it returns me a message

ERROR: must be a member of role "adm"

This is the trigger:

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 adm', obj.object_identity);
  END LOOP;
END;
$$;

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

Best Answer

As the manual says,

To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the table's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the table. However, a superuser can alter ownership of any table anyway.)

You need to either run execute CREATE TABLE as superuser, or create a SECURITY DEFINER function (again, as superuser) that creates such tables and assigns their owner roles, then grant the EXECUTE privilege on that function only to the user in question. See additional notes on security in this case.

Related Question