Re: Get the list of permissions/privileges on schema

Okay..then could you please suggest me what could be the correct way? 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 mydb *schema
and stored procedures are defined with SECURITY DEFINER in postgres user
context. I have given execute permission to set of users to these stored
procedures to achieve my goal.

*I want only allowed users to create table with foreign key references.* ==>
This can be achieve using SET ROLE current user but *Postgresql doesn't
allow 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 will not have permission on mydb schema, 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.

Hence, I am asking how can I store the schema permissions list and restore
it back once store procedure execution completed.

Please let me know where I am going wrong here? I am trying to get my things
done out of what PostGreSQL supports.

It would be really nice if anyone could help me to achieve my requirements.

Please feel free to let me know if you have any questions.

Thanks a lot,
Dipti

On Fri, Mar 26, 2010 at 3:52 AM, John R Pierce <pierce(at)hogranch(dot)com> wrote:

> dipti shah wrote:
>
>> Thanks a lot guys but I am not looking for security definer function. I
>> know it. My requirements are very complicated and I have to nailed down the
>> stuffs by storing schema permissions somewhere, execute my store procedure,
>> and restored the stored schema permissions. Like this I would make sure that
>> thogh my store procedure manipulates schema permissions, at the end, users
>> will have their permissions intact.
>>
>>
>
> thats totally the wrong way to do things in SQL.
>
>
>