error in trigger creation

On Sunday, April 21, 2024, yudhi s <learnerdatabase99@gmail.com> 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.

On Sun, Apr 21, 2024 at 1:55 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Sunday, April 21, 2024, yudhi s <learnerdatabase99@gmail.com> 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.

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;

On Sunday, April 21, 2024, yudhi s <learnerdatabase99@gmail.com> wrote:

On Sun, Apr 21, 2024 at 1:55 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Sunday, April 21, 2024, yudhi s <learnerdatabase99@gmail.com> 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.

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?

Dynamic SQL. See “execute” in plpgsql.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Sunday, April 21, 2024, yudhi s <learnerdatabase99@gmail.com> 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.

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

On Sun, Apr 21, 2024 at 7:55 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Sunday, April 21, 2024, yudhi s <learnerdatabase99@gmail.com> wrote:

On Sun, Apr 21, 2024 at 1:55 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Sunday, April 21, 2024, yudhi s <learnerdatabase99@gmail.com> 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.

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?

Dynamic SQL. See “execute” in plpgsql.

David J.

Even if we create the event trigger using "security definer" function
embedding the "create event trigger" with in its body using dynamic
sql(something as below), and in future if we need to create another event
trigger , we need to again update the function and re-compile and for that
, we will need it it to be compiled using user "super user", is my
understanding correct here?
Or
it will just need the "super user" to create the function for the first
time , but after that the user who has the "execute grant" given (say
app_user) will be able to perform updates and compile to the function body?

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;

On Sun, Apr 21, 2024 at 11:10 AM yudhi s <learnerdatabase99@gmail.com>
wrote:

On Sun, Apr 21, 2024 at 7:55 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Sunday, April 21, 2024, yudhi s <learnerdatabase99@gmail.com> wrote:

On Sun, Apr 21, 2024 at 1:55 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Sunday, April 21, 2024, yudhi s <learnerdatabase99@gmail.com> 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.

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?

Dynamic SQL. See “execute” in plpgsql.

David J.

Even if we create the event trigger using "security definer" function
embedding the "create event trigger" with in its body using dynamic
sql(something as below), and in future if we need to create another event
trigger , we need to again update the function and re-compile and for that
, we will need it it to be compiled using user "super user", is my
understanding correct here?
Or
it will just need the "super user" to create the function for the first
time , but after that the user who has the "execute grant" given (say
app_user) will be able to perform updates and compile to the function body?

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;

If you don't allow the caller to pass in parameters then no, you likely
gain nothing from using a security definer function. It is a tool and I
don't have enough info or desire to write the internals of said function(s)
for your need. As Tom says, it very well may be impossible to accomplish
your goal even with a security definer function. But absent a predefined
role there is no other mechanism for the owners of objects or superusers to
delegate their non-grantable abilities to ordinary users.

David J.

On Sun, Apr 21, 2024 at 8:13 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Sunday, April 21, 2024, yudhi s <learnerdatabase99@gmail.com> 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.

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.)

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?

On Sun, Apr 21, 2024 at 11:20 AM yudhi s <learnerdatabase99@gmail.com>
wrote:

On Sun, Apr 21, 2024 at 8:13 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Sunday, April 21, 2024, yudhi s <learnerdatabase99@gmail.com> 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.

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.)

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
nobody(individual person) can login using that user.

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.

On Mon, Apr 22, 2024 at 12:02 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:

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.

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

On Sun, Apr 21, 2024 at 2:58 PM yudhi s <learnerdatabase99@gmail.com> wrote:

the partition drop from parent is taking longer as it scans all the
partitions of the child table

Does the relevant supporting index exist on the child table?

On Mon, 22 Apr, 2024, 1:34 am Ron Johnson, <ronljohnsonjr@gmail.com> wrote:

On Sun, Apr 21, 2024 at 2:58 PM yudhi s <learnerdatabase99@gmail.com>
wrote:

the partition drop from parent is taking longer as it scans all the
partitions of the child table

Does the relevant supporting index exist on the child table?

Yes all the child tables have foreign keys indexed.

Again I don't want to divert the main topic(trigger creation) . I will
provide the exact test case how it puts exclusive lock and runs longer as
scans all child partitions (which must be using indexes though) but as the
number of partitions increase the time of drop partitions increases.

On 4/21/24 11:20, yudhi s wrote:

On Sun, Apr 21, 2024 at 8:13 PM Tom Lane <tgl@sss.pgh.pa.us
<mailto:tgl@sss.pgh.pa.us>> 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?

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

Which Postgres has

https://www.postgresql.org/docs/current/ddl-priv.html

TRIGGER

Allows creation of a trigger on a table, view, etc.

but you are talking about event triggers

https://www.postgresql.org/docs/current/sql-createeventtrigger.html

where

"Only superusers can create event triggers."

To paraphrase Henry Ford, you can have any user for an event trigger as
long as the user is a superuser.

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?

An event trigger runs as a superuser and executes a function that in
turn can do many things, you do the math on the threat level.

--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 4/21/24 11:20, yudhi s wrote:

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?

An event trigger runs as a superuser and executes a function that in
turn can do many things, you do the math on the threat level.

As a trivial example: an event trigger could prevent the legitimate
superuser(s) from doing anything at all in that database, just by
blocking all their commands. This might not even require malicious
intent, merely faulty coding --- but the opportunity for malicious
intent is staggeringly large.

regards, tom lane

On 4/21/24 14:21, Tom Lane wrote:

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 4/21/24 11:20, yudhi s wrote:

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?

An event trigger runs as a superuser and executes a function that in
turn can do many things, you do the math on the threat level.

As a trivial example: an event trigger could prevent the legitimate
superuser(s) from doing anything at all in that database, just by
blocking all their commands. This might not even require malicious
intent, merely faulty coding --- but the opportunity for malicious
intent is staggeringly large.

As an FYI to above:

https://www.postgresql.org/docs/current/sql-createeventtrigger.html

"Event triggers are disabled in single-user mode (see postgres). If an
erroneous event trigger disables the database so much that you can't
even drop the trigger, restart in single-user mode and you'll be able to
do that."

regards, tom lane

--
Adrian Klaver
adrian.klaver@aklaver.com