Thread: Wanted: Want to hide data by using PL/PGSQL functions

Hello,
I have following problem:

A user "xy" shouldn't have any rights to a table,
but needs data from the content of the table.
My idea was to setup a PL/PGSQL procedure to fetch the
data from the table, so that the user only is allowed to
access the procedure. I also tried using a SQL function,
but this doesn't work, too.
Working with views may be a solution - or are e.g. cursors
in pl/pgsql the solution ??
The problem i run into is, that although i can read the data
and return it, I can not return more than one row each
function call. Is it possible to return a whole resultset ?

My last try was:

drop function test(int);
create function test(int) returns table_name as '
select * from table_name where column_name1>= $1
order by column_name1;
' language sql;
select * from test(1) ;

The pl/pgsql variant:


drop function test();
CREATE FUNCTION test() RETURNS text AS '
declare
 target table_name%ROWTYPE;
begin
select * into target from table_name ;
return target.column_name1 || target.column_name2;
end;
' LANGUAGE plpgsql;
select test();
But in PL/pgsql i am not able to return a cursor or something like this
and I am not able to return more than one row.

So i have got 2 maybe solutions, but none of them works.

Has anyone a hint, how to "hide" original tables and making their data
selectable to some users ? The result really should be a
select a.* , b.* from a,b where a.state!="imgonewild" ....

Thanks in advance,
Andre

To return a result set use SETOF, like so

CREATE FUNCTION test() RETURNS SETOF text AS '

To allow access to the tables only through a function, take a look at
declaring your functions with SECURITY DEFINER

CREATE FUNCTION test() RETURNS SETOF text SECURITY DEFINER AS '

.. Inside the function you will now have permissions of the user that
created the function. See here for details

http://www.postgresql.org/docs/7.4/interactive/sql-createfunction.html

Also this may be useful

http://techdocs.postgresql.org/guides/SetReturningFunctions

HTH

adam


> Hello,
> I have following problem:
>
> A user "xy" shouldn't have any rights to a table,
> but needs data from the content of the table.
> My idea was to setup a PL/PGSQL procedure to fetch the
> data from the table, so that the user only is allowed to
> access the procedure. I also tried using a SQL function,
> but this doesn't work, too.
> Working with views may be a solution - or are e.g. cursors
> in pl/pgsql the solution ??
> The problem i run into is, that although i can read the data
> and return it, I can not return more than one row each
> function call. Is it possible to return a whole resultset ?
>
> My last try was:
>
> drop function test(int);
> create function test(int) returns table_name as '
> select * from table_name where column_name1>= $1
> order by column_name1;
> ' language sql;
> select * from test(1) ;
>
> The pl/pgsql variant:
>
>
> drop function test();
> CREATE FUNCTION test() RETURNS text AS '
> declare
> target table_name%ROWTYPE;
> begin
> select * into target from table_name ;
> return target.column_name1 || target.column_name2;
> end;
> ' LANGUAGE plpgsql;
> select test();
> But in PL/pgsql i am not able to return a cursor or something like this
> and I am not able to return more than one row.
>
> So i have got 2 maybe solutions, but none of them works.
>
> Has anyone a hint, how to "hide" original tables and making their data
> selectable to some users ? The result really should be a
> select a.* , b.* from a,b where a.state!="imgonewild" ....
>
> Thanks in advance,
> Andre
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>     joining column's datatypes do not match


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

It sounds like you want a table function:

http://techdocs.postgresql.org/guides/SetReturningFunctions

On Wed, 21 Jul 2004, Gellert, Andre wrote:

> Hello,
> I have following problem:
>
> A user "xy" shouldn't have any rights to a table,
> but needs data from the content of the table.
> My idea was to setup a PL/PGSQL procedure to fetch the
> data from the table, so that the user only is allowed to
> access the procedure. I also tried using a SQL function,
> but this doesn't work, too.
> Working with views may be a solution - or are e.g. cursors
> in pl/pgsql the solution ??
> The problem i run into is, that although i can read the data
> and return it, I can not return more than one row each
> function call. Is it possible to return a whole resultset ?
>
> My last try was:
>
> drop function test(int);
> create function test(int) returns table_name as '
> select * from table_name where column_name1>= $1
> order by column_name1;
> ' language sql;
> select * from test(1) ;
>
> The pl/pgsql variant:
>
>
> drop function test();
> CREATE FUNCTION test() RETURNS text AS '
> declare
>  target table_name%ROWTYPE;
> begin
> select * into target from table_name ;
> return target.column_name1 || target.column_name2;
> end;
> ' LANGUAGE plpgsql;
> select test();
> But in PL/pgsql i am not able to return a cursor or something like this
> and I am not able to return more than one row.
>
> So i have got 2 maybe solutions, but none of them works.
>
> Has anyone a hint, how to "hide" original tables and making their data
> selectable to some users ? The result really should be a
> select a.* , b.* from a,b where a.state!="imgonewild" ....
>
> Thanks in advance,
> Andre
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
>