Re: Command Triggers

On Mon, Dec 12, 2011 at 11:49 AM, Andres Freund <andres(at)anarazel(dot)de> wrote:
>> I haven't yet thought about your specific proposal here in enough to
>> have a fully-formed opinion, but I am a little nervous that this may
>> turn out to be one of those cases where the obvious API ends up
>> working less well than might have been supposed.
> What are your thoughts about a "not-obvious api"?

It seems to me (and it may seem differently to other people) that what
most people who want to trap DDL events really want to do is either
(a) get control at the permissions-checking stage so that they can
override the system's default access control policy, either to allow
something that would normally be denied (as in Dimitri's sudo example)
or deny something that would normally be allowed (as with sepgsql, or
the example in the documentation for Dimitri's patch showing how to
enforce relation naming conventions) or else (b) perform some
additional steps after completing the operation (e.g. audit it,
replicate it, apply a security label to it). Much of the
selinux-related work that KaiGai Kohei and I have been doing over the
last couple of years has revolved around where to put those hooks and
what information to pass to them.

For example, we added some post-creation hooks where the system
basically says "we just created a <object-type> and its OID is
<newly-assigned-oid>". I'm not going to present this as a model of
usability (because it isn't). However, I think the underlying model
is worth considering. It's not a command trigger, because it doesn't
care what command the user executed that led to the object getting
created. It's more like an event system - whenever a table gets
created (no matter how exactly that happens), you get a callback. I
think that's probably closer to what people want for the
additional-steps-after-completing-the-operation use case. Typically,
you're not really interested in what the user typed: you want to know
what the system decided to do about it.

The "get control at the permissions checking stage" use case is a bit
harder. We've added some hooks for that as well, but really only for
DML thus far, and I'm not convinced we've got the right design even
there. Part of the complexity here is that there are actually a lot
of different permissions rules depending on exactly what operation you
are performing - you might think of REINDEX TABLE, ALTER TABLE ..
ALTER COLUMN .. RENAME and DROP TABLE as requiring the same
permissions (i.e. ownership) but in fact they're all slightly
different. It would be nice if the permissions checking machinery
were better separated from the code that actually does the work, so
that you could rip and replace just that part. Dimitri's idea of
using a security definer function as a DDL instead-of trigger is an
interesting one, but does that leave newly created objects owned by
the wrong user? Or what if you want user A to be able to do some but
not all of the things user B can do? Drilling an optimally-sized hole
through the existing permissions system is unfortunately not very
simple.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company