pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Davis <mdavis@sevainc.com>
To: 'pgsql-sql@postgresql.org' <pgsql-sql@postgresql.org>
Subject: Access tables inside pl/pgsql functions
Date: Thu, 8 Mar 2001 12:36:34 -0700
Message-ID: <01C0A7CC.6A8AC460.mdavis@sevainc.com> (raw)

I would like to create a pl/pgsql function that can select from a table 
even though users can't select from the table directly.  For example, 
create a table and function that hits the table as the postgres user.  Log 
in as another user and select function_name();.  This fails because the 
user does not have permissions to select from the table.  The issues is 
that I don't want the users to be able to select from the table but I would 
like to allow the user to call a stored procedure than can select from the 
table.  Any idea how to do this?  Any help is greatly appreciated.

Thanks, Michael

Here is an example.  Create the following table and function as the 
postgres user:

CREATE TABLE tst
(
   tmp_relname name,
   id smallint
);

CREATE FUNCTION test() RETURNS int AS '
BEGIN
      DELETE FROM tst;
       return 1;
   END;'  LANGUAGE 'plpgsql';

Login as another user

Select test();  -- this will fail






Message-ID: <01C0A7CC.6A8AC460.mdavis@sevainc.com>
Permalink:  ../01C0A7CC.6A8AC460.mdavis@sevainc.com/
Also on:    postgresql.org/message-id/01C0A7CC.6A8AC460.mdavis@sevainc.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: mdavis@sevainc.com
  Subject: Re: Access tables inside pl/pgsql functions
  In-Reply-To: <01C0A7CC.6A8AC460.mdavis@sevainc.com>
  References: <01C0A7CC.6A8AC460.mdavis@sevainc.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox