agora inbox for pgsql-admin@postgresql.org  

help / color / mirror / Atom feed

event_trigger, 'DROP SCHEMA'
3+ messages / 2 participants
[nested] [flat]

* event_trigger, 'DROP SCHEMA'
@ 2024-07-26 17:32  kaido vaikla <kaido.vaikla@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: kaido vaikla @ 2024-07-26 17:32 UTC (permalink / raw)
  To: pgsql-admin

Hi,

Goal is, drop schema should trigger drop role.
It does not work if default privileges are altered.

*Function and event trigger:*

kvpg2=# CREATE or replace FUNCTION trigger_after_drop_schema() RETURNS
event_trigger
LANGUAGE plpgsql AS $$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT * FROM pg_event_trigger_dropped_objects()
LOOP
execute format ('drop role IF EXISTS r_%I', r.object_name);
END LOOP;
END;
$$;
CREATE FUNCTION

kvpg2=# CREATE EVENT TRIGGER after_drop_schema
ON sql_drop
WHEN TAG IN ('DROP SCHEMA')
EXECUTE PROCEDURE trigger_after_drop_schema();
CREATE EVENT TRIGGER

*Example 1* (just user)
kvpg2=# create schema test;
CREATE SCHEMA
kvpg2=# create role r_test;
CREATE ROLE
kvpg2=# drop schema test;
DROP SCHEMA
kvpg2=# \du r_test
     List of roles
 Role name | Attributes
-----------+------------

*Example 2* (user with usage on schema)
kvpg2=# create schema test;
CREATE SCHEMA
kvpg2=# create role r_test;
CREATE ROLE
kvpg2=# grant usage on schema test to r_test ;
GRANT
kvpg2=# drop schema test;
DROP SCHEMA
kvpg2=# \du r_test
     List of roles
 Role name | Attributes
-----------+------------

*Example 3* (user with usage on schema and altered default privileges)
kvpg2=# create schema test;
CREATE SCHEMA
kvpg2=# create role r_test;
CREATE ROLE
kvpg2=# grant usage on schema test to r_test ;
GRANT
kvpg2=# alter default privileges for role postgres in schema test grant
select on tables to r_test;
ALTER DEFAULT PRIVILEGES
kvpg2=# drop schema test;
ERROR:  null values cannot be formatted as an SQL identifier
CONTEXT:  PL/pgSQL function trigger_after_drop_schema() line 7 at EXECUTE


Question, what i do wrong?

br
Kaido

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: event_trigger, 'DROP SCHEMA'
@ 2024-07-26 17:44  Tom Lane <tgl@sss.pgh.pa.us>
  parent: kaido vaikla <kaido.vaikla@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2024-07-26 17:44 UTC (permalink / raw)
  To: kaido vaikla <kaido.vaikla@gmail.com>; +Cc: pgsql-admin

kaido vaikla <kaido.vaikla@gmail.com> writes:
> FOR r IN SELECT * FROM pg_event_trigger_dropped_objects()
> LOOP
> execute format ('drop role IF EXISTS r_%I', r.object_name);
> END LOOP;

> kvpg2=# drop schema test;
> ERROR:  null values cannot be formatted as an SQL identifier
> CONTEXT:  PL/pgSQL function trigger_after_drop_schema() line 7 at EXECUTE

> Question, what i do wrong?

Assume that every dropped object has a name, looks like.
You would probably do well to check object_type before
trying to drop the role.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: event_trigger, 'DROP SCHEMA'
@ 2024-07-26 18:12  kaido vaikla <kaido.vaikla@gmail.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: kaido vaikla @ 2024-07-26 18:12 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

ty tom, works now

kvpg2=# cREATE or replace FUNCTION trigger_after_drop_schema() RETURNS
event_trigger
LANGUAGE plpgsql AS $$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT * FROM pg_event_trigger_dropped_objects()
LOOP
IF r.object_type = 'schema' THEN
execute format ('drop role IF EXISTS r_%I', r.object_name);
end if;
END LOOP;
END;
$$;
CREATE FUNCTION
kvpg2=# drop schema test;
DROP SCHEMA
kvpg2=# \du r_test
     List of roles
 Role name | Attributes
-----------+------------


br
Kaido

On Fri, 26 Jul 2024 at 20:44, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> kaido vaikla <kaido.vaikla@gmail.com> writes:
> > FOR r IN SELECT * FROM pg_event_trigger_dropped_objects()
> > LOOP
> > execute format ('drop role IF EXISTS r_%I', r.object_name);
> > END LOOP;
>
> > kvpg2=# drop schema test;
> > ERROR:  null values cannot be formatted as an SQL identifier
> > CONTEXT:  PL/pgSQL function trigger_after_drop_schema() line 7 at EXECUTE
>
> > Question, what i do wrong?
>
> Assume that every dropped object has a name, looks like.
> You would probably do well to check object_type before
> trying to drop the role.
>
>                         regards, tom lane
>

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2024-07-26 18:12 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-07-26 17:32 event_trigger, 'DROP SCHEMA' kaido vaikla <kaido.vaikla@gmail.com>
2024-07-26 17:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-07-26 18:12   ` kaido vaikla <kaido.vaikla@gmail.com>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox