Received: from mail01.phoenixdsl.com (mail.phoenixdsl.com [216.178.151.24]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f28Jajx88816 for ; Thu, 8 Mar 2001 14:36:45 -0500 (EST) (envelope-from mdavis@sevainc.com) Received: (qmail 67299 invoked by uid 89); 8 Mar 2001 19:36:38 -0000 Received: from unknown (HELO SALLY) (64.32.157.45) by 0 with SMTP; 8 Mar 2001 19:36:38 -0000 Received: by localhost with Microsoft MAPI; Thu, 8 Mar 2001 12:36:36 -0700 Message-ID: <01C0A7CC.6A8AC460.mdavis@sevainc.com> From: Michael Davis Reply-To: "mdavis@sevainc.com" To: "'pgsql-sql@postgresql.org'" Subject: Access tables inside pl/pgsql functions Date: Thu, 8 Mar 2001 12:36:34 -0700 Organization: Seva Inc. X-Mailer: Microsoft Internet E-mail/MAPI - 8.0.0.4211 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Archive-Number: 200103/183 X-Sequence-Number: 1836 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