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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
