Skip site navigation (1) Skip section navigation (2)

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

From: "Turner, Ian" <Ian(dot)Turner(at)deshaw(dot)com>
To: "Erik Jones" <ejones(at)engineyard(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: Best way to monitor, control, or rewrite data definition commands?
Date: 2009-05-27 20:33:14
Message-ID: 8744EE476651744882371AB948473F0B159EB2@mailnyc2.nyc.deshaw.com (view raw or flat)
Thread:
Lists: pgsql-general
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
> 
> 
> 
> 
> 
> 

In response to

pgsql-general by date

Next:From: Ben ChobotDate: 2009-05-27 20:35:20
Subject: Re: Favorite/Recommended ERD tools
Previous:From: raul.giucichDate: 2009-05-27 20:30:29
Subject: Re: Favorite/Recommended ERD tools

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group