Обсуждение: Get the list of permissions/privileges on schema
Hi,
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?
Thanks,
Dipti
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
On Thu, Mar 25, 2010 at 2:37 PM, dipti shah <shahdipti1980@gmail.com> wrote:
Hi,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?Thanks,Dipti
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:Is it correct to set it directly?
> You should look into the pg_class table : relacl attribute for the
> permissions on any object.
Of course not...
This is just way to fetch the list of permission on any object.
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> wrote:
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:Is it correct to set it directly?
> You should look into the pg_class table : relacl attribute for the
> permissions on any object.Of course not...This is just way to fetch the list of permission on any object.
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
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:
dipti shah wrote:thats totally the wrong way to do things in SQL.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 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
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> wrote:
You should look into the pg_class table : relacl attribute for the permissions on any object.--
Thanks & Regards,
Ashesh Vashi
EnterpriseDB INDIA: Enterprise Postgres CompanyOn Thu, Mar 25, 2010 at 2:37 PM, dipti shah <shahdipti1980@gmail.com> wrote:Hi,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?Thanks,Dipti