Get the list of permissions/privileges on schema

You should look into the pg_class table : relacl attribute for the
permissions on any object.

--
Thanks & Regards,

Ashesh Vashi
EnterpriseDB INDIA: Enterprise Postgres Company<http://www.enterprisedb.com&gt;

On Thu, Mar 25, 2010 at 2:37 PM, dipti shah <shahdipti1980@gmail.com> wrote:

On 25 March 2010 12:14, Ashesh Vashi <ashesh.vashi@enterprisedb.com> wrote:

You should look into the pg_class table : relacl attribute for the
permissions on any object.

Is it correct to set it directly?

On Thu, Mar 25, 2010 at 2:37 PM, dipti shah <shahdipti1980@gmail.com> wrote:

Could any one please tell me how to get list of all the permissions on the
schema (or any postgresql objects), stored them somewhere before executing
stored procedure and then restore them?

May be you are looking for something like SECURITY DEFINER option?
http://www.postgresql.org/docs/8.4/interactive/sql-createfunction.html

--
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp / ICQ: 29353802

On Thu, Mar 25, 2010 at 2:54 PM, Sergey Konoplev <gray.ru@gmail.com> wrote:

On 25 March 2010 12:14, Ashesh Vashi <ashesh.vashi@enterprisedb.com>
wrote:

You should look into the pg_class table : relacl attribute for the
permissions on any object.

Is it correct to set it directly?

Of course not...
This is just way to fetch the list of permission on any object.

--
Thanks & Regards,

Ashesh Vashi
EnterpriseDB INDIA: Enterprise Postgres Company<http://www.enterprisedb.com&gt;

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.

Thanks,
Dipti

On Thu, Mar 25, 2010 at 2:59 PM, Ashesh Vashi <ashesh.vashi@enterprisedb.com

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.

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@hogranch.com> wrote:

Thanks Ashesh, I ran below command and it is listing all privileges of
objects under mydb schema. Actually, I want to know what are the permissions
"user1" has on mydb schema. Could you please tell me how to do this?

mydb=# select pc.relname, pc.relacl from pg_class pc, pg_namespace pn where
pc.relnamespace=pn.oid and pn.nspname='mydb';
relname | relacl
----------------------------------+-----------------------------------------------
mylog |
{postgres=arwdDxt/postgres,=arwdDxt/postgres}
techtable | {postgres=arwdDxt/postgres,=ar/postgres}
techtable_log |
hrtable | {postgres=arwdDxt/postgres,=ar/postgres}
hrtable_log |
(5 rows)

mydb=> select current_user;
current_user
--------------
user1
(1 row)

mydb=>

Thanks,
Dipti

On Thu, Mar 25, 2010 at 2:44 PM, Ashesh Vashi <ashesh.vashi@enterprisedb.com