Subject: Re: error in trigger creation

From: "David G. Johnston" <[email-masked]>
Date: Sun, 21 Apr 2024 01:25:35 -0700

On Sunday, April 21, 2024, yudhi s <[email-masked]> wrote:

> so that it will be able to assign the privilege, so we will be able to
> create the event trigger without need to run the event trigger script from
> super user itself?
>

Write a security-definer function owned by superuser and grant app_user
permission to execute it.

David J.

---

From: yudhi s <[email-masked]>
Date: Sun, 21 Apr 2024 14:25:44 +0530

> permission to execute it.

Thank You David.

 Are you saying something like below, in which we first create the function
from super user and then execute the grant? But doesn't that mean, each
time we want to create a new event trigger we have to be again dependent on
the "super user" to modify the security definer function?

CREATE OR REPLACE FUNCTION create_event_trigger_func()
  RETURNS void
  LANGUAGE plpgsql
  SECURITY DEFINER
  AS $$
  BEGIN
    EXECUTE 'CREATE EVENT TRIGGER event_trigger_name ON schema_name ...';
  END;
$$;

GRANT EXECUTE ON FUNCTION create_event_trigger_func() TO app_user;

---

From: "David G. Johnston" <[email-masked]>
Date: Sun, 21 Apr 2024 07:25:14 -0700

On Sunday, April 21, 2024, yudhi s <[email-masked]> wrote:

>  Are you saying something like below, in which we first create the
> function from super user and then execute the grant? But doesn't that mean,
> each time we want to create a new event trigger we have to be again
> dependent on the "super user" to modify the security definer function?
>

Dynamic SQL.  See “execute” in plpgsql.

David J.

---

From: Tom Lane <[email-masked]>
Date: Sun, 21 Apr 2024 10:43:26 -0400

> Dynamic SQL.  See “execute” in plpgsql.

You might as well just give that user superuser and be done with it.
It's foolish to imagine that you have any shred of security left
if you're letting a user that's not 100.00% trusted write event
triggers.  (Much less execute any SQL command whatsoever, which
is what it sounds like David is suggesting you create a function
to do.)

			regards, tom lane

---

From: yudhi s <[email-masked]>
Date: Sun, 21 Apr 2024 23:50:38 +0530

> is what it sounds like David is suggesting you create a function
So do you mean , we should not create the event trigger using the "security
definer" , rather have the super user do this each time we have to create
the event trigger?

Actually , I am not very much aware about the security part, but is it fine
to give the super user privilege to the application user(say app_user) from
which normally scripts/procedures get executed by the application, but
nobody(individual person) can login using that user.

Additionally in other databases, triggers are driven by some
specific privileges (say for example in oracle "create trigger" privilege).
And it doesn't need any super user and we were having many applications in
which the application user (which were used for app to app login) was
having these privileges, similar to "create table" privileges which comes
by default to the schema who owns the objects  etc. So in this case i was
wondering if "event trigger" can cause any additional threat and thus there
is no such privilege like "create trigger" exist in postgres and so it
should be treated cautiously?

---

From: "David G. Johnston" <[email-masked]>
Date: Sun, 21 Apr 2024 11:32:11 -0700

On Sun, Apr 21, 2024 at 11:20 AM yudhi s <[email-masked]>
wrote:

> So do you mean , we should not create the event trigger using the
> "security definer" , rather have the super user do this each time we have
> to create the event trigger?
>

I suggest you share a script that demonstrates exactly what you are trying
to accomplish.  Which event triggers you need to create from the
application and what the functions those triggers call do.

> Actually , I am not very much aware about the security part, but is it
> fine to give the super user privilege to the application user(say app_user)
> from which normally scripts/procedures get executed by the application, but

app_user should not be superuser nor own objects in the database.  The role
that performs schema migrations for the database should be able to become
superuser via set role so when doing migrations if there is a need to do
something as superuser it is possible but explicit.

It is during schema migrations that event triggers are expected to be
installed, not in response to some user hitting your website and having
your middleware execute some SQL while connected as the app_user role.

David J.

---

From: yudhi s <[email-masked]>
Date: Mon, 22 Apr 2024 00:27:44 +0530

> application and what the functions those triggers call do.
We are using pg_partman for automatic partition maintenance however as we
have foreign keys created on the tables, so the partition drop from parent
is taking longer as it scans all the partitions of the child table and also
locks the full child table for that duration(even SELECT query not allowed
during that period). So we are thinking of creating foreign keys on
partitions rather than on tables however there is no direct option for that
to happen through pg_partman.

So we are thinking of first creating the table without any foreign keys and
creating the partitions using pg_partman, then create the below event
trigger which will add the foreign key to the new partitions for all new
future partitions. And we are planning to create such an event trigger for
all such child tables that are partitioned and having FK's.

CREATE OR REPLACE FUNCTION add_partition_foreign_key()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
partition_table TEXT;
parent_table TEXT;
partition_name TEXT;
BEGIN
IF TG_TAG = 'CREATE TABLE' THEN
partition_table := TG_TABLE_NAME;
parent_table := 'parent_table_' || to_char(NEW.partition_key, 'YYYY_MM_DD');

EXECUTE format('ALTER TABLE %I ADD CONSTRAINT fk_%I_%I FOREIGN KEY
(partition_key, id) REFERENCES %I (partition_key, id)', partition_table,
partition_table, parent_table, parent_table);
END IF;
END;
$$;

CREATE EVENT TRIGGER add_partition_foreign_key_trigger
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE')
EXECUTE FUNCTION add_partition_foreign_key();