473,721 Members | 2,180 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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%ROWT YPE;
begin
select * into target from table_name ;
return target.column_n ame1 || target.column_n ame2;
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!="imgon ewild" ....

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
2 1627
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%ROWT YPE;
begin
select * into target from table_name ;
return target.column_n ame1 || target.column_n ame2;
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!="imgon ewild" ....

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

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%ROWT YPE;
begin
select * into target from table_name ;
return target.column_n ame1 || target.column_n ame2;
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!="imgon ewild" ....

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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
2810
by: Dorthe Luebbert | last post by:
Hi, we have to convert a quite large ISO-application (Mysql4, PHP5) to UTF8. We found out so far that there is no collation for MySQL which is able to sort all character sets correctly. So this has to be done. And there might be some problems with string functions as not all string functions have multibyte aquivalents. What are your experiences with this topic? Any hints?
2
3153
by: Clay Luther | last post by:
I had been having good luck writing stored procs in C++ with Postgres. But, today, I needed to include the SPI interface to help a trigger along, aka #include "executor/spi.h" and this is completely ungrokable by GNU C++. Am I stuck, or can I solve this problem (tried extern "C" {}, no go).
9
5536
by: Thomas LeBlanc | last post by:
Is there a tool for designing databases(ERD to data Design) and being able to generate scripts or databases from the tool. Visio does a good job, but does not have a driver for PostgreSQL. The scripts have to be modified before executing. Thanks, Thomas LeBlanc _________________________________________________________________
2
8274
by: Jenny Zhang | last post by:
I am running OSDL-dbt1 - an e-commerce workload (http://www.osdl.org/lab_activities/kernel_testing/osdl_database_test_suite/osdl_dbt-1/) against PostgreSQL: 7.3.3. During the test, I saw a lot of messages in the database log file: NOTICE: _sc_id=0, _add_flag=0, _itemcount=0, _i_id=597, _pp_i_id=159, c_id=32760 ERROR: deadlock detected WARNING: Error occurred while executing PL/pgSQL function shopping_cart WARNING: line 311 at SQL...
11
2560
by: Capstar | last post by:
Hi, I am working on an application, which will run embedded without an OS. The app is build up out of a couple of well defined parts. At first I wanted to keep those parts seperated and use opaque data types to transfer information in between them. At some stage I was stuck and needed to make a variable global, and I also needed to make the struct declaration public to some other parts. Looking through the code I found out that lots...
4
5183
by: Együd Csaba | last post by:
Hi All, I'd like to "compress" the following two filter expressions into one - assuming that it makes sense regarding query execution performance. .... where (adate LIKE "2004.01.10 __:30" or adate LIKE "2004.01.10 __:15") .... into something like this: .... where adate LIKE "2004.01.10 __:(30/15)" ...
8
1983
by: Steve Atkins | last post by:
I have an application where each user session opens and maintains a long-lived connection to the postgresql backend database. I need to keep a small amount of information (things like a unique session identifier, the application - as opposed to database - username and so on) that is local to each database session. It needs to be visible from within plpgsql trigger functions and will be used on a large fraction of updates. I can see a...
0
1115
by: Google Mike | last post by:
I had RH9 Linux. It came with pgSQL, but I couldn't seem to figure out how to get PL/pgSQL going. I read the HTML documentation that came with it and was confused until I tried a few different variations and guessed about some things. I've finally got it working and here's what the SQL looks like to enable and test it: -- ON YOUR SYSTEM, LOOK FOR WHERE plpgsql.so FILE EXISTS AND SUBSTITUTE THAT -- PATH WITH THE ONE I HAVE BELOW.
0
8860
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8738
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9376
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9086
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6678
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5994
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4501
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4762
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
2146
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.