Access tables inside pl/pgsql functions

From: Michael Davis <mdavis(at)sevainc(dot)com>
To: "'pgsql-sql(at)postgresql(dot)org'" <pgsql-sql(at)postgresql(dot)org>
Subject: Access tables inside pl/pgsql functions
Date: 2001-03-08 19:36:34
Message-ID: 01C0A7CC.6A8AC460.mdavis@sevainc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Browse pgsql-sql by date

  From Date Subject
Next Message Thomas F. O'Connell 2001-03-08 20:09:13 Re: how to reload a function
Previous Message dev 2001-03-08 18:55:46 Re: Writing SQL functions in Postgres