Thread: Function and Tables Privilege
Hi guys, I´m almost sure that this question has been asked before... but after a 2 days search, I couldn´t find any solutions for it, I´m a noob to postgres, but manage to install version-7.3.4 no prob, and working fine. So here goes: I´m looking for a way to store procedures on the db so that i can only access data through those, and only grand permissions to the user to access the procedures and not the tables. I´ve read some stuff about setuid in this mailing list but could not get it to work. Basically what i need is to give privilege to the function to access the tables that will be used and is not granted to the user that executed the function. Any help will be very much appreciated, even if there is no way of doing so :) Thanks Iandé
So you want to pull conifential info from a table that has everyone's confidential info, with no chance of leaking someone else's?
Sounds like a view (or a function and a view) is in order here... it might be a pain to manage though if you have a alot of changing SELECTers.
-----Original Message-----
From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Iande
Sent: Thursday, August 07, 2003 3:33 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] Function and Tables PrivilegeHi guys,
I´m almost sure that this question has been asked before... but after a 2 days search, I couldn´t find any solutions for it, I´m a noob to postgres, but manage to install version-7.3.4 no prob, and working fine. So here goes:
I´m looking for a way to store procedures on the db so that i can only access data through those, and only grand permissions to the user to access the procedures and not the tables. I´ve read some stuff about setuid in this mailing list but could not get it to work. Basically what i need is to give privilege to the function to access the tables that will be used and is not granted to the user that executed the function.
Any help will be very much appreciated, even if there is no way of doing so :)
Thanks
Iandé
On Thu, 7 Aug 2003, Iande wrote: > I�m almost sure that this question has been asked before... but after a 2 days search, I couldn�t find any solutionsfor > it, I�m a noob to postgres, but manage to install version-7.3.4 no prob, and working fine. So here goes: > > I�m looking for a way to store procedures on the db so that i can only access data through those, and only grand permissions > to the user to access the procedures and not the tables. I�ve read some stuff about setuid in this mailing list but couldnot > get it to work. Basically what i need is to give privilege to the function to access the tables that will be used and isnot > granted to the user that executed the function. > > Any help will be very much appreciated, even if there is no way of doing so :) Well, a view is the easiest thing, grant permissions to the view and revoke them from the base table. However, if you actually want functions, you should be able to say something like: create table testtable(a text, b int); create function gettesttable() returns setof testtable as ' select * from testtable where a = SESSION_USER;' language 'SQL' security definer; This example is one that really would make more sense as a view, but imagine that the function was plpgsql and actually did something interesting. What have you tried so far?
Thanks for the reply, I think I wasn´t too clear on wot I was intending to do... ` here is an example, I hope this helps.. Thanks for the reply, I think I wasn´t too clear on wot I was intending to do... ` here is an example, I hope this helps.. I have a Table let´s say tb_test with admin as the onwer and the only user with all privilege for that table, I want to create a function that user usr1 has permission to execute. Within that function all i want to do is to update tb_test, but i get permission denied for that table when i try to execute the function as usr1. Basically I want to insert or update a table via a function restricting insert and update privilege to that table. I´ve heard that there is a way of setting the privileges to the level of the onwer of the function, can anyone give be a pratical example? thanks again Iandé <BLOCKQUOTE dir=ltr style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px"> ----- Original Message ----- <DIV style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black">From: <A title=jhihn@paytimepayroll.com href="mailto:jhihn@paytimepayroll.com">Jason Hihn To: <A title=iande@br.inter.net href="mailto:iande@br.inter.net">Iande ; <A title=pgsql-novice@postgresql.org href="mailto:pgsql-novice@postgresql.org">pgsql-novice@postgresql.org Sent: Thursday, August 07, 2003 4:37 PM Subject: RE: [NOVICE] Function and Tables Privilege So you want to pull conifential info from a table that has everyone's confidential info, with no chance of leaking someone else's? <SPAN class=509413519-07082003> <SPAN class=509413519-07082003>Sounds like a view (or a function and a view) is in order here... it might be a pain to manage though if you have a alot of changing SELECTers. <SPAN class=509413519-07082003> <BLOCKQUOTE style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #0000ff 2px solid; MARGIN-RIGHT: 0px"> <FONT face=Tahoma size=2>-----Original Message-----From: <A href="mailto:pgsql-novice-owner@postgresql.org">pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of IandeSent: Thursday, August 07, 2003 3:33 PMTo: pgsql-novice@postgresql.orgSubject: [NOVICE] Function and Tables Privilege Hi guys, I´m almost sure that this question has been asked before... but after a 2 days search, I couldn´t find any solutions for it, I´m a noob to postgres, but manage to install version-7.3.4 no prob, and working fine. So here goes: I´m looking for a way to store procedures on the db so that i can only access data through those, and only grand permissions to the user to access the procedures and not the tables. I´ve read some stuff about setuid in this mailing list but could not get it to work. Basically what i need is to give privilege to the function to access the tables that will be used and is not granted to the user that executed the function. Any help will be very much appreciated, even if there is no way of doing so :) Thanks Iandé
Well, a view is the easiest thing, grant permissions to the view andrevoke them from the base table.However, if you actually want functions, you should be able to saysomething like:create table testtable(a text, b int);create function gettesttable() returns setof testtable as ' select * fromtesttable where a = SESSION_USER;' language 'SQL' security definer;This example is one that really would make more sense as a view, butimagine that the function was plpgsql and actually did somethinginteresting.What have you tried so far?---------------------- What I was thinking, would be something on the line of enable privilege at the begining of the function and disable privilege and the end to restore the currente_user privileges, from wot i understand, this way the function would execute with it´s onwer´s privileges. would it not be advisable not to use a view?.. i want to insert into a table via a function, so that, i can only access the especific table through the functions parameters. is this any clear? thanks once again Iandé
If I understand your question correctly, what you are trying to achieve is to have the function execute with the creator permission and not the user who executes it. If this is the case then it is easy. Use security definer when you create your function as in the following example: create or replace function func_name(parameters) returns ... security definer as ' declare ... As long as the creator has permission to modify data in the table, so would the user who executes this function. Avi On Thursday, Aug 7, 2003, at 15:26 America/Chicago, Iande wrote: > Thanks for the reply, I think I wasn´t too clear on wot I was > intending to do... ` > here is an example, I hope this helps.. > > Thanks for the reply, I think I wasn´t too clear on wot I was > intending to do... ` > here is an example, I hope this helps.. > > I have a Table let´s say tb_test with admin as the onwer and the only > user with all privilege for that table, I want to create a function > that user usr1 has permission to execute. Within that function all i > want to do is to update tb_test, but i get permission denied for that > table when i try to execute the function as usr1. Basically I want to > insert or update a table via a function restricting insert and > update privilege to that table. I´ve heard that there is a way of > setting the privileges to the level of the onwer of the function, can > anyone give be a pratical example? > thanks again
An immediate hack comes to mind. Post it to a temp table, and have a super-user level trigger copy it out and update the real table. How the one functions gets to be super user, is beyond my knowlege. :-( <SPAN class=576332920-07082003> <SPAN class=576332920-07082003>---------- <SPAN class=576332920-07082003> <SPAN class=576332920-07082003>Interesting, but i supose i´m left with the same security problem, i don´t want the user to have direct access on update to that table, i wanted to restrict it to functions. From where i see it, this temp table would efectly give permission to update the real table, please correct me if i´m wrong. <SPAN class=576332920-07082003>and about the functions gets to be super user, i was thinking more around the possibility of getting the onwer´s permission, can this be done? :-( <SPAN class=576332920-07082003> <SPAN class=576332920-07082003>thx, <SPAN class=576332920-07082003> <SPAN class=576332920-07082003>Iandé <SPAN class=576332920-07082003>