1

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();

2 Answers 2

1

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.

1

Write an event trigger that throws an error whenever the user tries to drop a table:

CREATE FUNCTION no_drop_table() RETURNS event_trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF current_user = 'myuser' THEN
      RAISE EXCEPTION 'you may not drop tables';
   END IF;
END;$$;

CREATE EVENT TRIGGER no_drop_table ON sql_drop WHEN TAG IN ('DROP TABLE')
   EXECUTE PROCEDURE no_drop_table();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.