Re: How to restrict select from table with external validation? - Mailing list pgsql-novice

Vladimir A. Petrov wrote:
> Hello!
>
> I have table like
>
> CREATE TABLE stats
> (
>   username varchar(256) NOT NULL,
>   "time" int8 NOT NULL,
>   duration int4 NOT NULL,
>   phonenumber varchar(20) NOT NULL,
>   and so on ...
> )
>
> I have function like
>
> CREATE OR REPLACE FUNCTION auth("varchar", "varchar")
>   RETURNS bool AS '
> ...
> ' LANGUAGE 'plperlu' VOLATILE;
>
> where first argument is username and second is password. This function
> returns true if username and password validated successfully or false
> otherwise.
>
> I have a user which must do only selects from table "stats".
>
> My questions is how to restrict access on table "stats" to this user in
> way where this user will be able to select only limited set of columns
> from table "stats" and only rows with usernames for which this user
> knows correct passwords validated via auth() function call?

You could create another function that returns a set of rows from stats.
  That function could check the auth function and then select only those
rows that are appropriate.  In other words, look at a set-returning
function that returns a set of rows that are appropriate for the given user.

Sean