Skip site navigation (1) Skip section navigation (2)

Permissions on Stored Procedures

From: jmoschet(at)netscape(dot)net
To: pgsql-admin(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: Permissions on Stored Procedures
Date: 2000-12-26 17:24:08
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-adminpgsql-hackers
I've been searching the lists and found a similar question but there have no 

What I would like to be able to do in Postgres is give users/groups the 
ability to execute stored procedures on a per procedure basis.

I am looking into the Rules system, thinking that this is acheiveable through 
the use of query rewriting, dummy tables and _insert, _delete, _update rules. 

I see in the (docs directory) file there is an example (2.5.8 / 
pg 49-50) of creating a dummy table and a rule that gets executed when 
someone issues a SELECT on that dummy table. 

The rule defines the actual query which is executed, so the *user* is none 
the wiser.  That's a start.

It seems that keeping track of *dummy* tables could get hairy and somone 
cleaning up could accidentally remove a dummy table and disable a whole mess 
of things inadvertenty. 

It would appear to be possible to set ACLs on a dummy table to only allow 
certain users/groups to execute the select, insert, update or delete on the 
dummy table itself. Thereby using the built-in postgres security to intervene 
and before the rules are ever executed. 

There is an example of the use of a rule to prevent an insert on a table that 
uses a special naming convention, <tablename>_insert that automatically gets 
executed when the INSERT is issued. (and similarly for the 
<tablename>_select, <tablename>_update and <tablename>_delete)
(PostgreSQL: Introduction and Concepts / Momjian, pg. 149)

Now if I'm way off and these scenarios are not possible, it would be nice to 
have ACL's on entries in the pg_proc table or an additional entry in pg_class 
for each stored proc/function (but that seems unlikely as it seems this would 
affect other parts of the postgres subsystem) and have the 'traffic-cop' 
check the permissions on the functions for us.


John Moschetto
attendee OSDN/OSDB Summit 
Oct 30-31 2000 - Hayes Mansion
San Jose, California

Get your own FREE, personal Netscape Webmail account today at

pgsql-hackers by date

Next:From: Thomas LockhartDate: 2000-12-26 18:08:47
Subject: Re: Re: GNU readline and BSD license
Previous:From: Tom LaneDate: 2000-12-26 16:26:14
Subject: Tuple-valued datums on Alpha (was Re: 7.1 on DEC/Alpha)

pgsql-admin by date

Next:From: R DDate: 2000-12-27 07:59:09
Subject: Re: Is that a bug?
Previous:From: Tom LaneDate: 2000-12-25 17:47:11
Subject: Re: [BUGS] postgres bombs for continous selects....

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group