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:
Show quoted text
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