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é

Search Discussions

  • Jason Hihn at Aug 7, 2003 at 7:38 pm
    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
    On Behalf Of Iande
    Sent: Thursday, August 07, 2003 3:33 PM
    To: pgsql-novice@postgresql.org
    Subject: [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é
  • Stephan Szabo at Aug 7, 2003 at 8:14 pm

    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 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 :)
    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?
  • Iande at Aug 7, 2003 at 8:27 pm
    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é -----
    Original Message ----- From: Jason Hihn To: Iande ; 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? 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
    On Behalf Of Iande
    Sent: Thursday, August 07, 2003 3:33 PM
    To: pgsql-novice@postgresql.org
    Subject: [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é
  • Avi Schwartz at Aug 7, 2003 at 8:41 pm
    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
  • Iande at Aug 7, 2003 at 8:35 pm
    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?
    ----------------------
    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é
  • Iande at Aug 7, 2003 at 8:49 pm
    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. :-( ---------- 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. 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? :-(
    thx, Iandé

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedAug 7, '03 at 7:34p
activeAug 7, '03 at 8:49p
posts7
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase