Re: execute permissions of stored procedures?

On Tue, Feb 19, 2002 at 02:29:05PM +0100, Helge Bahmann wrote:
>
> First you have to understand that the access control model is relational,
> not procedural -- after all Postgres is a relational database, and not an
> RPC service.

I still can't understand why everyone seems to think that it is something very
strange that I want. I simply want that if some user creates procedure, it owns
this procedure and that it runs as this user. And that this user can then grant
others to execute this procedure, just like this user can grant access to the
tables, views and sequences he creates. What is so strange about this? Both
Oracale and Solid have this and it never even occoured to me that PostgreSQL
might not have this basic feature.

> Nevertheless anything that could be checked in procedures can also be
> checked in query rewrite rules, although it may look unfamiliar if
> you have never done this. The standard way of doing this is the
> following:

Not really. A procedure can receive parameters that are not inserted in the
table, or it can even insert data into various tables the same time.

> -- as "privileged" user
> CREATE TABLE important_data (
> id serial primary key,
> public_data text,
> private_data text,
> ... );
>
> CREATE VIEW visible_data AS SELECT id, public_data FROM important_data
> WHERE <insert visibility restrictions here>
>
> CREATE RULE my_insert AS ON INSERT TO visible_data DO INSTEAD
> INSERT INTO important_data(public_data) VALUES(NEW.public_data)
> WHERE <insert access control restriction applying on insert here>
> CREATE RULE my_update AS ON UPDATE TO visible_data DO INSTEAD
> UPDATE important_data SET public_data=NEW.public_data
> WHERE id=OLD.id AND <insert access control restrictions
> applying on update here>
> CREATE RULE my_delete AS ON DELETE TO visible_data DO INSTEAD
> DELETE FROM important_data WHERE id=OLD.id AND
> <insert access control restrictions applying on delete>
>
> -- grant access to "unprivileged" user
> GRANT SELECT, INSERT, DELETE, UPDATE ON visible_data TO webuser;
>
> The table "important_data" is now inaccessible for webuser, and all
> accesses can only be performed through the view "visible_data". Oh,
> and you can of course grant "insert" or "update" rights without
> granting "select" rights.
>
> Hope this helps

Not really.

--
#!perl # Life ain't fair, but root passwords help.
# Eric Veldhuyzen eric(at)terra(dot)telemediair(dot)nl
$!=$;=$_+(++$_);($:,$~,$/,$^,$*,$@)=$!=~ # Perl Monger
/.(.)...(.)(.)....(.)..(.)..(.)/;`$^$~$/$: $^$*$(at)$~ $_>&$;`