Thread: Best way to monitor, control, or rewrite data definition commands?
Hello list, I am trying to implement automatic audit log tracking for newly created tables. When a user creates a table, I would like to create a second table for the audit log, along with the relevant rules. So for example, if a user does 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? Cheers, --Ian Turner
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
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.
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@gregsmith.com] > Sent: Tuesday, May 12, 2009 9:50 PM > To: Turner, Ian > Cc: pgsql-general@postgresql.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@gregsmith.com http://www.gregsmith.com > Baltimore, MD >
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
> -----Original Message-----
> From: Erik Jones [mailto:ejones@engineyard.com]
> Sent: Friday, May 15, 2009 6:19 PM
> To: Turner, Ian
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Best way to monitor, control, or
> rewrite data definition commands?
>
>
> 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
>
>
>
>
>
>