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

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

> -----Original Message-----
> From: Greg Smith [mailto:gsmith(at)gregsmith(dot)com]
> Sent: Tuesday, May 12, 2009 9:50 PM
> To: Turner, Ian
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Best way to monitor, control, or
> rewrite data definition commands?
>
> 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-loggi
> ng.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(at)gregsmith(dot)com http://www.gregsmith.com
> Baltimore, MD
>