Allowing John to Drop Triggers On Chad's Tables
Based on my research, There are the options I have identified to allow user
'John' to drop a trigger on a table owner by 'Chad' ..
1. Make John a superuser
2. grant chad to john ;
3. Have Chad create a wrapper definer-rights procedure to drop triggers &
grant execute to John.
4. Give John Chad's password!
None of these options appeal to me. If there is a 'grant trigger' why is
there not a 'grant drop trigger'?
Any other options since Chad has one table to allow John to drop triggers
on?
----------------------------------------
Thank you
On Mon, Mar 15, 2021 at 12:20 PM Fred Habash <fmhabash@gmail.com> wrote:
If there is a 'grant trigger' why is there not a 'grant drop trigger'?
Because creating a trigger from scratch doesn't let you affect other
triggers that you may not own (at least not directly). If drop permissions
were grantable the user could drop any trigger attached to said table. The
missing feature here is that triggers should probably have their own
ownership separate from a trigger and a trigger can only be attached to a
table where the trigger owner has trigger permissions. But the trigger
owner can always drop their owned triggers.
Any other options since Chad has one table to allow John to drop triggers
on?
Option 3 is the one that is expected to be used here. Well, aside from the
"John the person asks Chad the person to drop the trigger".
David J.
Fred Habash <fmhabash@gmail.com> writes:
Based on my research, There are the options I have identified to allow user
'John' to drop a trigger on a table owner by 'Chad' ..
I guess the big-picture question is why do you think you need that?
The separate TRIGGER permission is something we consider obsolescent.
It's difficult to make use of it in any way that isn't tantamount to
giving John the ability to execute arbitrary code as Chad (the next
time Chad does anything with his table). So while a case could be
made that granting TRIGGER should include the right to alter or drop
existing triggers, it's unlikely that anyone will want to expend effort
in that direction.
regards, tom lane
Thank you all for taking the time to respond. As always, great community
support.
On Mon, Mar 15, 2021 at 5:10 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Fred Habash <fmhabash@gmail.com> writes:
Based on my research, There are the options I have identified to allow
user
'John' to drop a trigger on a table owner by 'Chad' ..
I guess the big-picture question is why do you think you need that?
The separate TRIGGER permission is something we consider obsolescent.
It's difficult to make use of it in any way that isn't tantamount to
giving John the ability to execute arbitrary code as Chad (the next
time Chad does anything with his table). So while a case could be
made that granting TRIGGER should include the right to alter or drop
existing triggers, it's unlikely that anyone will want to expend effort
in that direction.regards, tom lane
--
----------------------------------------
Thank you