Postgres Pro
Downloads
Home   >   mailing lists

Thread:

From
Max
Date:
Hello all.

I'm working on a kind of permissions management and I would like to
add restrictions on SELECT statements depending on certain fields of
a row.

tablename :
id,
perm_field1,
..
..
perm_field2,
data_field1,
data_field2,
...

Do you know if it is possible to write such things :

SELECT * FROM tablename WHERE permission(some parameters) == TRUE;

with the function 'permission' returning TRUE or FALSE after a check
on different perm_fields of the current row.

My problem is that I don't really know how to access to the fields of
the current row with an external function.

How could I write this ?

Thx for help.
Max

Re:

From
Jeff Davis
Date:
On Tue, 2006-08-15 at 18:05 +0200, Max wrote:

> Do you know if it is possible to write such things :
>
> SELECT * FROM tablename WHERE permission(some parameters) == TRUE;
>
> with the function 'permission' returning TRUE or FALSE after a check
> on different perm_fields of the current row.

Yes, you can do that. The best place to start is:

http://www.postgresql.org/docs/8.1/static/plpgsql.html

You want to create a pretty basic function that returns "boolean" and
takes a few parameters. Note that SQL does not have the C-style "=="
equality test. In SQL just write "= true". Better yet, since your
function returns boolean just do "WHERE permission(...)" with "..."
replaced by your parameters.

If you have trouble after trying out some examples at that link, post
back to the list with what you tried.

Regards,
    Jeff Davis

Re:

From
Max
Date:
Thx.
But I know how to write procedure and function, but my problem is to
know how to access the current row fields during a SELECT inside a
function:

So, in a function, can I write :

/* ... */ permission (/* ... */)
/* ... */
IF (ROW.perm_field1 = 1)
    statement
IF (some_operation(ROW.perm_field2))
    statement
/* ... */
RETURN TRUE or FALSE;
/* ... */

Or am I obligated to pass to my function the tablename and the id
field to execute a second query, then retrieving the row fields and
then computing permissions and then returning true or false ?

Thx for Help

Max


Le 15 août 06 à 18:27, Jeff Davis a écrit :

> On Tue, 2006-08-15 at 18:05 +0200, Max wrote:
>
>> Do you know if it is possible to write such things :
>>
>> SELECT * FROM tablename WHERE permission(some parameters) == TRUE;
>>
>> with the function 'permission' returning TRUE or FALSE after a check
>> on different perm_fields of the current row.
>
> Yes, you can do that. The best place to start is:
>
> http://www.postgresql.org/docs/8.1/static/plpgsql.html
>
> You want to create a pretty basic function that returns "boolean" and
> takes a few parameters. Note that SQL does not have the C-style "=="
> equality test. In SQL just write "= true". Better yet, since your
> function returns boolean just do "WHERE permission(...)" with "..."
> replaced by your parameters.
>
> If you have trouble after trying out some examples at that link, post
> back to the list with what you tried.
>
> Regards,
>     Jeff Davis
>
>
>

Re:

From
Jeff Davis
Date:
On Tue, 2006-08-15 at 18:46 +0200, Max wrote:
> Thx.
> But I know how to write procedure and function, but my problem is to
> know how to access the current row fields during a SELECT inside a
> function:
>
> So, in a function, can I write :
>
> /* ... */ permission (/* ... */)
> /* ... */
> IF (ROW.perm_field1 = 1)
>     statement
> IF (some_operation(ROW.perm_field2))
>     statement
> /* ... */
> RETURN TRUE or FALSE;
> /* ... */
>

What you want to do is pass each "perm_field" as a parameter.

So, you'd do something like:

CREATE OR REPLACE FUNCTION permission(perm_field1 int, perm_field2 int,
perm_field3 int) RETURNS BOOLEAN LANGUAGE plpgsql STABLE AS $$
BEGIN
IF perm_field1 = 2 THEN
    RETURN FALSE;
ELSIF perm_field2 = perm_field3 THEN
    RETURN TRUE;
ELSE
    RETURN FALSE;
END IF;
END;
$$;

And then:

SELECT * FROM tablename WHERE permission
(perm_field1,perm_field2,perm_field3);

Hope this helps,
    Jeff Davis

Re:

From
Tom Lane
Date:
Jeff Davis <pgsql@j-davis.com> writes:
> What you want to do is pass each "perm_field" as a parameter.

I think the OP is hoping for some sort of magic action-at-a-distance
behavior whereby his function can access stuff that wasn't passed to it.
Doesn't exist though ...

            regards, tom lane