By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,945 Members | 1,692 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,945 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Nov 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Ben
It sounds like you want a table function:

http://techdocs.postgresql.org/guide...rningFunctions

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


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #2

P: n/a

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/i...efunction.html

Also this may be useful

http://techdocs.postgresql.org/guide...rningFunctions

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.
---------------------------(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

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.