public inbox for [email protected]
help / color / mirror / Atom feedRe: error in trigger creation
7+ messages / 3 participants
[nested] [flat]
* Re: error in trigger creation
@ 2024-04-21 08:25 David G. Johnston <[email protected]>
2024-04-21 08:55 ` Re: error in trigger creation yudhi s <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2024-04-21 08:25 UTC (permalink / raw)
To: yudhi s <[email protected]>; +Cc: pgsql-general <[email protected]>
On Sunday, April 21, 2024, yudhi s <[email protected]> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: error in trigger creation
2024-04-21 08:25 Re: error in trigger creation David G. Johnston <[email protected]>
@ 2024-04-21 08:55 ` yudhi s <[email protected]>
2024-04-21 14:25 ` Re: error in trigger creation David G. Johnston <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: yudhi s @ 2024-04-21 08:55 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: pgsql-general <[email protected]>
On Sun, Apr 21, 2024 at 1:55 PM David G. Johnston <
[email protected]> wrote:
> On Sunday, April 21, 2024, yudhi s <[email protected]> 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;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: error in trigger creation
2024-04-21 08:25 Re: error in trigger creation David G. Johnston <[email protected]>
2024-04-21 08:55 ` Re: error in trigger creation yudhi s <[email protected]>
@ 2024-04-21 14:25 ` David G. Johnston <[email protected]>
2024-04-21 14:43 ` Re: error in trigger creation Tom Lane <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2024-04-21 14:25 UTC (permalink / raw)
To: yudhi s <[email protected]>; +Cc: pgsql-general <[email protected]>
On Sunday, April 21, 2024, yudhi s <[email protected]> wrote:
> On Sun, Apr 21, 2024 at 1:55 PM David G. Johnston <
> [email protected]> wrote:
>
>> On Sunday, April 21, 2024, yudhi s <[email protected]> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: error in trigger creation
2024-04-21 08:25 Re: error in trigger creation David G. Johnston <[email protected]>
2024-04-21 08:55 ` Re: error in trigger creation yudhi s <[email protected]>
2024-04-21 14:25 ` Re: error in trigger creation David G. Johnston <[email protected]>
@ 2024-04-21 14:43 ` Tom Lane <[email protected]>
2024-04-21 18:20 ` Re: error in trigger creation yudhi s <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2024-04-21 14:43 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: yudhi s <[email protected]>; pgsql-general <[email protected]>
"David G. Johnston" <[email protected]> writes:
> On Sunday, April 21, 2024, yudhi s <[email protected]> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: error in trigger creation
2024-04-21 08:25 Re: error in trigger creation David G. Johnston <[email protected]>
2024-04-21 08:55 ` Re: error in trigger creation yudhi s <[email protected]>
2024-04-21 14:25 ` Re: error in trigger creation David G. Johnston <[email protected]>
2024-04-21 14:43 ` Re: error in trigger creation Tom Lane <[email protected]>
@ 2024-04-21 18:20 ` yudhi s <[email protected]>
2024-04-21 18:32 ` Re: error in trigger creation David G. Johnston <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: yudhi s @ 2024-04-21 18:20 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: David G. Johnston <[email protected]>; pgsql-general <[email protected]>
On Sun, Apr 21, 2024 at 8:13 PM Tom Lane <[email protected]> wrote:
> "David G. Johnston" <[email protected]> writes:
> > On Sunday, April 21, 2024, yudhi s <[email protected]> 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?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: error in trigger creation
2024-04-21 08:25 Re: error in trigger creation David G. Johnston <[email protected]>
2024-04-21 08:55 ` Re: error in trigger creation yudhi s <[email protected]>
2024-04-21 14:25 ` Re: error in trigger creation David G. Johnston <[email protected]>
2024-04-21 14:43 ` Re: error in trigger creation Tom Lane <[email protected]>
2024-04-21 18:20 ` Re: error in trigger creation yudhi s <[email protected]>
@ 2024-04-21 18:32 ` David G. Johnston <[email protected]>
2024-04-21 18:57 ` Re: error in trigger creation yudhi s <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2024-04-21 18:32 UTC (permalink / raw)
To: yudhi s <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-general <[email protected]>
On Sun, Apr 21, 2024 at 11:20 AM yudhi s <[email protected]>
wrote:
>
> On Sun, Apr 21, 2024 at 8:13 PM Tom Lane <[email protected]> wrote:
>
>> "David G. Johnston" <[email protected]> writes:
>> > On Sunday, April 21, 2024, yudhi s <[email protected]> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: error in trigger creation
2024-04-21 08:25 Re: error in trigger creation David G. Johnston <[email protected]>
2024-04-21 08:55 ` Re: error in trigger creation yudhi s <[email protected]>
2024-04-21 14:25 ` Re: error in trigger creation David G. Johnston <[email protected]>
2024-04-21 14:43 ` Re: error in trigger creation Tom Lane <[email protected]>
2024-04-21 18:20 ` Re: error in trigger creation yudhi s <[email protected]>
2024-04-21 18:32 ` Re: error in trigger creation David G. Johnston <[email protected]>
@ 2024-04-21 18:57 ` yudhi s <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: yudhi s @ 2024-04-21 18:57 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-general <[email protected]>
On Mon, Apr 22, 2024 at 12:02 AM David G. Johnston <
[email protected]> 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();
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-04-21 18:57 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-04-21 08:25 Re: error in trigger creation David G. Johnston <[email protected]>
2024-04-21 08:55 ` yudhi s <[email protected]>
2024-04-21 14:25 ` David G. Johnston <[email protected]>
2024-04-21 14:43 ` Tom Lane <[email protected]>
2024-04-21 18:20 ` yudhi s <[email protected]>
2024-04-21 18:32 ` David G. Johnston <[email protected]>
2024-04-21 18:57 ` yudhi s <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox