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

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(at)engineyard(dot)com]
> Sent: Friday, May 15, 2009 6:19 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 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
>
>
>
>
>
>