테이블 생성시 자동을 신규 테이블에 grant하는 trigger

DB 생성 이후, 생성된 DB의 public schema에 적용

CREATE OR REPLACE FUNCTION grant_user()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
    DECLARE r RECORD;
BEGIN
    For r IN select * from pg_event_trigger_ddl_commands() LOOP
    if r.object_type = 'table'
    then
        EXECUTE FORMAT ('grant SELECT ON TABLE %s TO group_s',r.object_identity);
        EXECUTE FORMAT ('grant INSERT ON TABLE %s TO group_i',r.object_identity);
        EXECUTE FORMAT ('grant DELETE ON TABLE %s TO group_d',r.object_identity);
        EXECUTE FORMAT ('grant UPDATE ON TABLE %s TO group_u',r.object_identity);
    END IF;
    END LOOP;
end;
$$;
 
CREATE EVENT TRIGGER grant_when_create_table ON ddl_command_end
when tag in ('SELECT INTO','CREATE TABLE','CREATE TABLE AS')
EXECUTE PROCEDURE grant_user();