Re: Function and Tables Privilege

On Thu, 7 Aug 2003, Iande wrote:

> Im almost sure that this question has been asked before... but after a 2 days search, I couldnt find any solutions for
> it, Im a noob to postgres, but manage to install version-7.3.4 no prob, and working fine. So here goes:
>
> Im 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. Ive 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?