Re: 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