Could anyone please suggest me how to deal with my following requirements.
So far, I have done following to meet my requirements:
I want users to use only stored procedures to create, alter, delete tables
in mydb schema*. ==> For this, I have *revoked all permissions from
and stored procedures are defined with SECURITY DEFINER in postgres user
context. I have given execute permission to set of users on these stored
procedures to achieve my goal.
*I want only privileged users to create table with foreign key
references.*==> This can be achieved using SET ROLE current user
before executing create
command but *Postgresql doesn't allow running SET ROLE in SECURITY DEFINER
function* *context* so I have created a my_sudo function which gets invoked
from my stored procedure. This sudo function creates a temporary SECURITY
DEFINER function and changes *owner to the current user before executing
create table command.
Now, as sudo function runs actual create command as current user context and
he/she does not have permission on mydb schema(as revoked all permission to
meet first requirement), I have to grant the ALL permissions on mydb schema
to current user temporary and then restore his/her actual privileges back to
make sure that users actual permission doesn't change.
*Problem:* Could anyone tell me how to get the schema permissions list for
current user and restore it back once store procedure execution completed.
Please feel free to let me know if you have any questions.
Thanks a lot,
pgsql-novice by date
|Next:||From: L. Loewe||Date: 2010-03-29 00:19:04|
|Subject: slow plan on join when adding where clause|
|Previous:||From: Vitali Xevet||Date: 2010-03-28 04:30:25|
|Subject: Clustering/fail-over solution|
pgsql-general by date
|Next:||From: Alban Hertroys||Date: 2010-03-28 11:22:27|
|Subject: Re: Why index occupy less amount of space than the table with same structure.|
|Previous:||From: Tadipathri Raghu||Date: 2010-03-28 08:05:48|
|Subject: Why index occupy less amount of space than the table with same structure.|