Re: execute permissions of stored procedures? - Mailing list pgsql-general

On Tue, 19 Feb 2002 eric@datalink.nl wrote:

> On Mon, Feb 18, 2002 at 01:21:50PM -0500, Doug McNaught wrote:
> > eric@datalink.nl writes:
> >
> > > In Solid it was possible to create a procedure (It looks like that a
> > > postgreSQL function is similar to that) and then to do a GRANT for
> > > EXECUTE rights on this procedure.  How can I do this in PostgrSQL?
> > >
> > > I want to use PostgreSQL as a database for our dynamic website, and
> > > the only thing I want to allow to the standard 'webuser' is to
> > > execute some procedures.  The use of a grant to a procedure allows
> > > me to be able to insert/update some specific rows in a database in a
> > > very specific way by a user that normally wouldn't even be allowed
> > > to do a SELECT on this table.
> >
> > I think the only current way to do this is to create views and insert
> > rules for the views, and grant the web user access to the views rather
> > than the underlying table.  This *should* be doable, depending on what
> > you need to do in the rules.
>
> > Having 'setuid' functions has been talked about, but I don't think
> > it's currently there.
>
> I really am stunned. How is it possible that such an essential feature is
> simply missing from a database like PostgreSQL? Are you expecting that all
> security for the database should be implemented ONLY at the top level, the
> users frontend? This is really unacceptable for me, I really need security from
> the bottom up, which in this case is the Database.

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.

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:

-- 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
--
Helge Bahmann <bahmann@math.tu-freiberg.de>             /| \__
Network admin, systems programmer                      /_|____\
                                                     _/\ |   __)
$ ./configure                                        \\ \|__/__|
checking whether build environment is sane... yes     \\/___/ |
checking for AIX... no (we already did this)            |