Best way to monitor, control, or rewrite data definition commands?

On May 12, 2009, at 7:59 PM, Turner, Ian wrote:

CREATE TABLE foo (fooid integer);

Then I would also like to do

CREATE TABLE foo_audit (fooid integer, <other columns>);

along with the creation of some other triggers, rules, etc.

Is there any way to be notified when a user executes data definition
commands such as CREATE TABLE? It doesn't appear possible to apply
triggers or rules to the system tables, and the query rewrite engine
only seems to apply to SELECT, INSERT, and UPDATE. Thoughts?

Correct, there are no triggers on the system tables.

Maybe some type of cron process that ensures there is foo_audit for
table foo?

John DeSoi, Ph.D.

On Tue, 12 May 2009, Turner, Ian wrote:

Is there any way to be notified when a user executes data definition
commands such as CREATE TABLE? It doesn't appear possible to apply
triggers or rules to the system tables, and the query rewrite engine
only seems to apply to SELECT, INSERT, and UPDATE. Thoughts?

This topic really deserves a FAQ entry.

You can set "log_statement=ddl" and see a log of them that way:
http://www.postgresql.org/docs/8.3/static/runtime-config-logging.html#GUC-LOG-STATEMENT

Setup CSV format logs and you can even import that into a database table,
appropriate log rotation is needed to give you breakpoints to import at
though.

The other common idiom here to detect changes is to save the output from
"pgdump -s" regularly and look for changes via diff.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Thanks everyone for the many responses to this question. Asynchronous
schema updates may be how we'll have to proceed, but we'd really like to
avoid that. Instead, I'm currently studying the possibility of a
my_create_table() function that accepts a CREATE TABLE command, executes
the command, and also does some extra tasks. Some of these extra tasks
require elevated privileges, which can be accomplished by making the
function SECURITY DEFINER. But the CREATE TABLE command should still be
executed as the calling user.

So, my next question is: Is there some way to drop one's privileges
within a transaction, or to execute a command using another user's
privileges?

Thanks again for your thoughts.

--Ian Turner

On May 14, 2009, at 2:38 PM, Turner, Ian wrote:

Thanks everyone for the many responses to this question. Asynchronous
schema updates may be how we'll have to proceed, but we'd really
like to
avoid that. Instead, I'm currently studying the possibility of a
my_create_table() function that accepts a CREATE TABLE command,
executes
the command, and also does some extra tasks. Some of these extra tasks
require elevated privileges, which can be accomplished by making the
function SECURITY DEFINER. But the CREATE TABLE command should still
be
executed as the calling user.

So, my next question is: Is there some way to drop one's privileges
within a transaction, or to execute a command using another user's
privileges?

Thanks again for your thoughts.

That was going to be my suggestion. Drop your user's rights to create
tables directly and define a function that creates your tables and set
up the rules as SECURITY INVOKER with a role that still has create
table privileges. That way, when other users run the function, it'll
run with the privileges of the user who created it. No need to switch
roles directly.

However, so you know, you can execute SET ROLE <rolename>; to change
to a role that your existing role has membership in (or any to any
role for superuser roles).

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k

Erik,

Thanks for the advice. Unfortunately,
postgresql/src/camend/commands/variable.c contains the following remark:

/*
* Disallow SET ROLE inside a security definer context.
We need to do
* this because when we exit the context, GUC won't be
notified,
* leaving things out of sync. Note that this test is
arranged so
* that restoring a previously saved setting isn't
prevented.
*
* XXX it would be nice to allow this case in future,
with the
* behavior being that the SET ROLE's effects end when
the security
* definer context is exited.
*/

It's the same situation with SET SESSION AUTHORIZATION. Anyone have a
guess on how difficult this is to fix?

Cheers,

--Ian Turner