Re: [GENERAL] SET Role doesn't work from Security Definer Function...
You are correct Tom that I want to perform some portion of function as postgres user and other portion as current user. As per you suggestion I did refactor and separated the portion that needs to be executed as superuser to another function. But the thing is PostGreSQL recognize when I call this separated funtion from my original SECURITY DEFINER function and gives the same error. :( For your reference I did something like this: 1. Create Function foo1 (this is without SECURITY DEFINER where I am using SET ROLE to current user). 2. Create Function foo2 with SECURITY DEFINER ... spi_exe_query(select foo1()); == Here it throws the error. I am helpless now. Could you tell me what could be done in this situation? Thanks, Dipti On Tue, Feb 23, 2010 at 2:04 AM, Tom Lane t...@sss.pgh.pa.us wrote: dipti shah shahdipti1...@gmail.com writes: I have just noticed that SET ROLE doesn't work from security definer function. I don;t know why but it clearly gives the error that SET role doesn;t work in security definer context. This is intentional because allowing it creates security holes. If I create function in postgres user with Security Definer enabled, it will allow to create any table with any foreign references etc...So I am setting role to current_user in my function and then creating a table to make sure that user has the appropriate privilege. Well, if you are trying to set the role back to current, why don't you just not have the function be security definer in the first place? I suppose the answer to that is that you want it to do some things as superuser and some things not. In which case, you need to refactor so that those two classes of things are done by different functions. regards, tom lane
Re: [GENERAL] SET Role doesn't work from Security Definer Function...
dipti shah escribió: For your reference I did something like this: 1. Create Function foo1 (this is without SECURITY DEFINER where I am using SET ROLE to current user). 2. Create Function foo2 with SECURITY DEFINER ... spi_exe_query(select foo1()); == Here it throws the error. Shouldn't it be the other way around? The normal function calls the security-definer one. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SET Role doesn't work from Security Definer Function...
No, I tried that but that can't be done in my requirements because my function has to be run in super user context to create the table in schema where normal users have only USAGE permissions. If I remove SECURITY DEFINER then my stored procedure will be failed for all users by saying permission denied on schema myschema. Moreover, I want to run only create table code in normal user context and other things in stored procedure should be done in super user context. I tried all possible ways but couldn't find to get out of this yet. Thanks, Dipti On Tue, Feb 23, 2010 at 8:36 PM, Alvaro Herrera alvhe...@commandprompt.comwrote: dipti shah escribió: For your reference I did something like this: 1. Create Function foo1 (this is without SECURITY DEFINER where I am using SET ROLE to current user). 2. Create Function foo2 with SECURITY DEFINER ... spi_exe_query(select foo1()); == Here it throws the error. Shouldn't it be the other way around? The normal function calls the security-definer one. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Re: [GENERAL] SET Role doesn't work from Security Definer Function...
This issue is driving me crazy. Could any one please suggest me any workaround? For summary of issue, 1. I don't want any users to perform any action on mydb schema without using my stored procedure. So I revoke ALL permissions from mydb schema and assigned only USAGE permissions. 2. As my stored procedure allows creating table in mydb schema and users have only USAGE permissions on mydb schama, I have to defined my stored procedure with SECURITY DEFINER so that it allows to create table in mydb schema. 3. To prevent creating unauthenticated foreign references to other tables, I want to make sure that current user has the required permissions to create table before creating table. For this I have to use SET ROLE to current user but it is not allowed in SECURITY DEFINER context. Any help would be much appreciated. Thanks, Dipti On Tue, Feb 23, 2010 at 10:51 PM, dipti shah shahdipti1...@gmail.comwrote: No, I tried that but that can't be done in my requirements because my function has to be run in super user context to create the table in schema where normal users have only USAGE permissions. If I remove SECURITY DEFINER then my stored procedure will be failed for all users by saying permission denied on schema myschema. Moreover, I want to run only create table code in normal user context and other things in stored procedure should be done in super user context. I tried all possible ways but couldn't find to get out of this yet. Thanks, Dipti On Tue, Feb 23, 2010 at 8:36 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: dipti shah escribió: For your reference I did something like this: 1. Create Function foo1 (this is without SECURITY DEFINER where I am using SET ROLE to current user). 2. Create Function foo2 with SECURITY DEFINER ... spi_exe_query(select foo1()); == Here it throws the error. Shouldn't it be the other way around? The normal function calls the security-definer one. -- Alvaro Herrera http://www.CommandPrompt.com/ http://www.commandprompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Re: [GENERAL] SET Role doesn't work from Security Definer Function...
dipti shah shahdipti1...@gmail.com writes: I have just noticed that SET ROLE doesn't work from security definer function. I don;t know why but it clearly gives the error that SET role doesn;t work in security definer context. This is intentional because allowing it creates security holes. If I create function in postgres user with Security Definer enabled, it will allow to create any table with any foreign references etc...So I am setting role to current_user in my function and then creating a table to make sure that user has the appropriate privilege. Well, if you are trying to set the role back to current, why don't you just not have the function be security definer in the first place? I suppose the answer to that is that you want it to do some things as superuser and some things not. In which case, you need to refactor so that those two classes of things are done by different functions. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
