Blocking drop table for specific schema using trigger

Question

I'm trying to prevent anyone from dropping tables on a specific schema "public" unless the user is an rds_superuser but the function I wrote is guarding on all schemas.

CREATE OR REPLACE FUNCTION guard_tables()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
IF TG_TABLE_SCHEMA = 'public' AND (SELECT COUNT(*) FROM pg_roles WHERE pg_has_role(CURRENT_USER, oid, 'member') AND rolname = 'rds_superuser') = 0 THEN
RAISE EXCEPTION 'command % is disabled for this table', tg_tag;
END IF;
END;
$$;

How do I get this to work?

Answer 1

TG_TABLE_SCHEMA is not set in event triggers.

You'll have to call pg_event_trigger_dropped_objects() to get information about the dropped objects. Among the result columns is schema_name, which contains the schema name of the affected object.

• eComEvo: Replacing that schema check part of the condition with this fixed it. Thanks! SELECT schema_name FROM pg_event_trigger_dropped_objects() LIMIT 1) = 'public'