Re: Command Triggers

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Thu, Feb 16, 2012 at 4:21 PM, Dimitri Fontaine
> <dimitri(at)2ndquadrant(dot)fr> wrote:
> That's certainly the easiest option. If you don't feel passionate
> about spending a lot of energy figuring out how to make it secure,
> then I suggest we just restrict it to superusers until someone does.

Works for me.

>>> If I install a command trigger that prevents all DDL, how do I
>>> uninstall it?  Or say I'm the superuser and I want to undo something
>>> my disgruntled DBA did before he quit.
>>
>> Good catch, I guess we need to remove creating and dropping a command
>> trigger to the list of supported commands in the ANY COMMAND list.
>
> Another option would be to add a PGC_SUSET boolean GUC that can be
> used to disable command triggers. I think that might be more
> flexible, not to mention useful for recursion prevention.

Wait, we already have ALTER TRIGGER bob ON ANY COMMAND SET DISABLED;
which I had forgotten about in the previous answer, so I think we're
good as it is. That's how I prevented recursion in some of my tests
(not included in the regress tests, was using INSTEAD OF).

>>  DROP TRIGGER bob ON ALL COMMANDS;
>
> Uh, hold on. Creating a trigger on multiple commands ought to only
> create one entry in pg_cmdtrigger. If you drop it, you drop the whole
> thing. Changing which commands the trigger applies to would be the
> job for ALTER, not DROP. But note that we have no similar
> functionality for regular triggers, so I can't think we really need it
> here either.

Why would we do it that way (a single entry for multiple commands)? The
way it is now, it's only syntactic sugar, so I think it's easier to
implement, document and use.

>> So do you prefer lots of InitCommandContext() or adding another parameter
>> to almost all functions called by standard_ProcessUtility()?
>
> Blech. Maybe we should just have CommandFiresTriggers initialize it
> if not already done?

Thing is, in a vast number of cases (almost of ALTER OBJECT name,
namespace and owner) you don't have the Node * parse tree any more from
the place where you check for CommandFiresTriggers(), so you can't
initialize there. That's part of the fun.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support