Обсуждение: How to restrict select from table with external validation?

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?

Any help will be appreciated.
--
Vladimir A. Petrov (aka vap)                    phone: (+7 8482) 420069
Infopac JSC. Head of the exploitation department.     http://infopac.ru
_______________________________________________________________________
^[:wq                                         ...sed libera nos a malo.

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

Vladimir,

Have you considered using a viw of table stats with the columns and
information your user is allowed to see?

John Purser

On Tue, 11 Jul 2006 09:37:19 +0500
"Vladimir A. Petrov" <vap@infopac.ru> 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?
>
> Any help will be appreciated.
> --
> Vladimir A. Petrov (aka vap)                    phone: (+7 8482)
> 420069 Infopac JSC. Head of the exploitation department.
> http://infopac.ru
> _______________________________________________________________________
> ^[:wq                                         ...sed libera nos a
> malo.
>
>
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 2: Don't 'kill -9' the
> postmaster


--
The time is right to make new friends.

John Purser <jmpurser@gmail.com> writes:
> Have you considered using a viw of table stats with the columns and
> information your user is allowed to see?

Yeah, a view is probably the best way.  Also take a look at the Veil
project:
http://pgfoundry.org/projects/veil/

            regards, tom lane

Tom Lane wrote:
> John Purser <jmpurser@gmail.com> writes:
>
>>Have you considered using a viw of table stats with the columns and
>>information your user is allowed to see?
>
>
> Yeah, a view is probably the best way.

I took from the OP that the the user and password were not necessarily
database users but might be stored in a database table structure.  If
that is indeed the case, then a view won't quite cut it, will it?  I
would be curious to see a clarification of what the "auth" function
does, just to see what the OP is doing for the authentication.

Sean

Sean Davis пишет:

> 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

Right that way I solved my problem yesterday. Another one important
thing is to set 'SECURITY DEFINER' attribute in function definition.

Thank you!
--
Vladimir A. Petrov (aka vap)                    phone: (+7 8482) 420069
Infopac JSC. Head of the exploitation department.     http://infopac.ru
_______________________________________________________________________
^[:wq                                         ...sed libera nos a malo.