Thread: execute permissions of stored procedures?
Hi, Right now I am evaluating PostgreSQL to see whether it can replace our current databse server Solid (3.0). It looks like PostgreSQL has a lot of nice extra features that I would love to use. But there is one thing that I simply can't find anywhere. 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. -- #!perl # Life ain't fair, but root passwords help. # Eric Veldhuyzen eric@terra.telemediair.nl $!=$;=$_+(++$_);($:,$~,$/,$^,$*,$@)=$!=~ # Perl Monger /.(.)...(.)(.)....(.)..(.)..(.)/;`$^$~$/$: $^$*$@$~ $_>&$;`
Attachment
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. -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863
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. Is there any idea if/when this will be implemented? -- #!perl # Life ain't fair, but root passwords help. # Eric Veldhuyzen eric@terra.telemediair.nl $!=$;=$_+(++$_);($:,$~,$/,$^,$*,$@)=$!=~ # Perl Monger /.(.)...(.)(.)....(.)..(.)..(.)/;`$^$~$/$: $^$*$@$~ $_>&$;`
Attachment
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) |
eric@datalink.nl writes: > 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. And you can do it in the database, using views and rules. Where's the problem? -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863
On Tue, 19 Feb 2002, Eric Veldhuyzen wrote:
> On Tue, Feb 19, 2002 at 02:29:05PM +0100, Helge Bahmann wrote:
> > 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.
Using views and rules to do access checking does not prevent you
from writing procedures that combine several operations.
(oh and btw: you could in theory include "artificial" columns in the views
and evaluate their value without inserting it into the underlying table
but this is really messy; and you can perform multiple actions in
a rule)
Look at it this way:
- if your data integrity and consistency constraints cannot easily be
expressed in relational terms, a relational database may not be the best
tool to solve your problem
- if, conversely, you can express these constraints in relational terms,
then the required functionality can generally be provided using views, rules
and triggers; your "procedures" in this case are just convenient helper
functions and do not need to perform any checking
This is not to say that something like setuid functions are useless, they
would occasionally still come in handy for some very specific trigger
procedures...
Best regards
--
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) |
On Tue, Feb 19, 2002 at 05:12:42PM +0100, Helge Bahmann wrote: > - if your data integrity and consistency constraints cannot easily be > expressed in relational terms, a relational database may not be the best > tool to solve your problem I don't see a real difference between a grant on a table, view or sequence and a procedure. Grants have NOTHING to do at all with ANY of the relations in the database, so if this is your point of view the grants shouldn't exist AT ALL. But it seems that nody on this is willing to understand what I want or why I want it, so in one thing you are correct, PostgreSQL is definitely not the databse for me. It is useless to explain something to someone who simply doesn't want to understand you. It seems that I have to use a real database like Oracle after all. Signing off frm this mailinglist. -- #!perl # Life ain't fair, but root passwords help. # Eric Veldhuyzen eric@terra.telemediair.nl $!=$;=$_+(++$_);($:,$~,$/,$^,$*,$@)=$!=~ # Perl Monger /.(.)...(.)(.)....(.)..(.)..(.)/;`$^$~$/$: $^$*$@$~ $_>&$;`
Attachment
If I understand right, many Oracle applications limit data access by users (thereby enhancing security) by allowing data access only via procedures. You appear to be saying that you like this feature, and you want it to be available in PostgreSQL. PostgreSQL has a different method of achieving the same result, as has already been pointed out (views with appropriate rewrite rules). If there is not a strong desire among PostgreSQL users for the feature that you want, that is probably because the existing method works well for them. Remember that PostgreSQL is a project run and developed by volunteers. The strongest vote for a new feature is the submission of code. The developers are not insensitive to the requests of users, but: if development is left to them, don't be surprised that they use their own judgement about what is most important. I have never seen anyone on this list say that Oracle is not a good product. If you want to use Oracle, and you (or your customer) have the money to pay, then go for it. --- eric@datalink.nl wrote: > On Tue, Feb 19, 2002 at 05:12:42PM +0100, Helge > Bahmann wrote: > > - if your data integrity and consistency > constraints cannot easily be > > expressed in relational terms, a relational > database may not be the best > > tool to solve your problem > > I don't see a real difference between a grant on a > table, view or sequence and > a procedure. Grants have NOTHING to do at all with > ANY of the relations in the > database, so if this is your point of view the > grants shouldn't exist AT ALL. > But it seems that nody on this is willing to > understand what I want or why I > want it, so in one thing you are correct, PostgreSQL > is definitely not the > databse for me. It is useless to explain something > to someone who simply > doesn't want to understand you. It seems that I have > to use a real database > like Oracle after all. > > Signing off frm this mailinglist. > > -- > #!perl # Life ain't fair, but > root passwords help. > # Eric Veldhuyzen > eric@terra.telemediair.nl > $!=$;=$_+(++$_);($:,$~,$/,$^,$*,$@)=$!=~ > # Perl Monger > /.(.)...(.)(.)....(.)..(.)..(.)/;`$^$~$/$: $^$*$@$~ > $_>&$;` > > ATTACHMENT part 2 application/pgp-signature __________________________________________________ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com
On Wed, 20 Feb 2002 eric@datalink.nl wrote: > On Tue, Feb 19, 2002 at 05:12:42PM +0100, Helge Bahmann wrote: > > - if your data integrity and consistency constraints cannot easily be > > expressed in relational terms, a relational database may not be the best > > tool to solve your problem > > I don't see a real difference between a grant on a table, view or sequence and > a procedure. Grants have NOTHING to do at all with ANY of the relations in the > database, so if this is your point of view the grants shouldn't exist AT ALL. > But it seems that nody on this is willing to understand what I want or why I > want it, so in one thing you are correct, PostgreSQL is definitely not the > databse for me. It is useless to explain something to someone who simply > doesn't want to understand you. It seems that I have to use a real database > like Oracle after all. Actually, I'm pretty sure that C functions currently allow you to effectively do setuid around spi calls. But you'd still have to do the what users are allowed to call this function portion manually because that permission isn't available AFAIK (although it's been talked about as part of 7.3 development I think).
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> ... But you'd still have to do
> the what users are allowed to call this function portion manually
> because that permission isn't available AFAIK (although it's been
> talked about as part of 7.3 development I think).
Actually, it's already been committed as part of 7.3 development:
2002-02-18 18:10 petere
* doc/src/sgml/catalogs.sgml, doc/src/sgml/release.sgml,
doc/src/sgml/ref/create_function.sgml,
doc/src/sgml/ref/create_language.sgml, doc/src/sgml/ref/grant.sgml,
doc/src/sgml/ref/revoke.sgml, src/backend/catalog/aclchk.c,
src/backend/catalog/pg_proc.c, src/backend/commands/define.c,
src/backend/commands/proclang.c, src/backend/executor/execQual.c,
src/backend/nodes/copyfuncs.c, src/backend/nodes/equalfuncs.c,
src/backend/parser/gram.y, src/backend/parser/keywords.c,
src/backend/utils/Gen_fmgrtab.sh, src/backend/utils/adt/acl.c,
src/backend/utils/adt/sets.c, src/backend/utils/cache/fcache.c,
src/backend/utils/misc/superuser.c, src/bin/initdb/initdb.sh,
src/bin/scripts/createlang.sh, src/include/miscadmin.h,
src/include/catalog/catversion.h,
src/include/catalog/pg_attribute.h, src/include/catalog/pg_class.h,
src/include/catalog/pg_language.h, src/include/catalog/pg_proc.h,
src/include/nodes/nodes.h, src/include/nodes/parsenodes.h,
src/include/parser/keywords.h, src/include/utils/acl.h,
src/include/utils/fcache.h,
src/test/regress/expected/privileges.out,
src/test/regress/sql/privileges.sql: Privileges on functions and
procedural languages
regards, tom lane
On Wed, 20 Feb 2002, Tom Lane wrote: > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: > > ... But you'd still have to do > > the what users are allowed to call this function portion manually > > because that permission isn't available AFAIK (although it's been > > talked about as part of 7.3 development I think). > > Actually, it's already been committed as part of 7.3 development: I really have to update my system from 7.2 final to 7.3 cvs :)
On a somewhat related issue: I wonder whether the current interaction
of rules and functions (especially trigger functions, but functions
used in CHECK constraints are affected as well) is really fortunate:
Suppose a query executed by user 'A' gets rewritten and the resulting
query executed with the privileges of user 'B' -- all functions
are still invoked with the privileges of user 'A'. Is this intentional?
I remember having to grant additional rights to user 'A' in some cases,
just to please some trigger procedures.
Wouldn't it be more useful to make a complete privilege transition to
user 'B' when rewriting the query? Honestly I cannot think of a case
where this would not be the desired behaviour (at least I think this is
the expected behaviour), perhaps someone can give a counter-example why
the current behaviour would be more correct?
Best regards
--
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) |
Oracle has a UNIX-file-system-like set of capabilities for allowing acess to database objects and granting priviledges. Users need to be granted the CONNECT right to connect. Tables and views created under one user are only visible to that user. To share them, you need to create a synonym, and then grant INSERT, UPDATE, SELECT or DELETE rights depending on you're requirements. A stored procedure is the same, with the execute right. Ditto for sequences, and need the SELECT and ALTER right. Finally, there are roles, which are an amalgam of rights. There are all sorts of predifined roles that you normally grant once and never think about again. Some people use them, some don't. We use them, but they were poorly implemented by a consulting company, and as a result, provide no security, and big headaches. If you are working on an application where the user connects directly to the database (client-server) rather than a web-based, 3-tier application, synonyms and rights are more important. But if every user connects with the same user account via the connection pool in your middle tier, then their usefulness drops off dramatically. Yes - Oracle is a good product. Oracle is also an expensive product. We are looking at $60,000 US for a 2-year licence for Oracle just for our production servers (not counting Quality Assurance and Development). I'd say that's worth a little client-side coding to restrict stored procedures, but what do I know. David ----- Original Message ----- From: "Jeff Eckermann" <jeff_eckermann@yahoo.com> To: <eric@datalink.nl> Cc: <pgsql-general@postgresql.org> Sent: Wednesday, February 20, 2002 7:15 AM Subject: Re: [GENERAL] execute permissions of stored procedures? > If I understand right, many Oracle applications limit > data access by users (thereby enhancing security) by > allowing data access only via procedures. > > You appear to be saying that you like this feature, > and you want it to be available in PostgreSQL. > > PostgreSQL has a different method of achieving the > same result, as has already been pointed out (views > with appropriate rewrite rules). If there is not a > strong desire among PostgreSQL users for the feature > that you want, that is probably because the existing > method works well for them. > > Remember that PostgreSQL is a project run and > developed by volunteers. The strongest vote for a new > feature is the submission of code. The developers are > not insensitive to the requests of users, but: if > development is left to them, don't be surprised that > they use their own judgement about what is most > important. > > I have never seen anyone on this list say that Oracle > is not a good product. If you want to use Oracle, and > you (or your customer) have the money to pay, then go > for it. > > --- eric@datalink.nl wrote: > > On Tue, Feb 19, 2002 at 05:12:42PM +0100, Helge > > Bahmann wrote: > > > - if your data integrity and consistency > > constraints cannot easily be > > > expressed in relational terms, a relational > > database may not be the best > > > tool to solve your problem > > > > I don't see a real difference between a grant on a > > table, view or sequence and > > a procedure. Grants have NOTHING to do at all with > > ANY of the relations in the > > database, so if this is your point of view the > > grants shouldn't exist AT ALL. > > But it seems that nody on this is willing to > > understand what I want or why I > > want it, so in one thing you are correct, PostgreSQL > > is definitely not the > > databse for me. It is useless to explain something > > to someone who simply > > doesn't want to understand you. It seems that I have > > to use a real database > > like Oracle after all. > > > > Signing off frm this mailinglist. > > > > -- > > #!perl # Life ain't fair, but > > root passwords help. > > # Eric Veldhuyzen > > eric@terra.telemediair.nl > > $!=$;=$_+(++$_);($:,$~,$/,$^,$*,$@)=$!=~ > > # Perl Monger > > /.(.)...(.)(.)....(.)..(.)..(.)/;`$^$~$/$: $^$*$@$~ > > $_>&$;` > > > > > ATTACHMENT part 2 application/pgp-signature > > > > __________________________________________________ > Do You Yahoo!? > Yahoo! Sports - Coverage of the 2002 Olympic Games > http://sports.yahoo.com > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
On Wed, Feb 20, 2002 at 12:00:04PM -0500, Tom Lane wrote: > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: > > ... But you'd still have to do > > the what users are allowed to call this function portion manually > > because that permission isn't available AFAIK (although it's been > > talked about as part of 7.3 development I think). > > Actually, it's already been committed as part of 7.3 development: > > 2002-02-18 18:10 petere And quite recently, so it seems. Anyway, I just did a checkout of the CVS repository and after a quick look at the src/test/regress/sql/privileges.sql file it seems like this might do exactly what I would like. Before I jump into this deeper I have one question though, how stable should I expect this CVS version to be, is it highly experimental, and should I expect lots of nasty crashes, bugs and dataloss and other disasters, or is it reasonably save to use in a production environment? -- #!perl # Life ain't fair, but root passwords help. # Eric Veldhuyzen eric@terra.telemediair.nl $!=$;=$_+(++$_);($:,$~,$/,$^,$*,$@)=$!=~ # Perl Monger /.(.)...(.)(.)....(.)..(.)..(.)/;`$^$~$/$: $^$*$@$~ $_>&$;`
Attachment
On Wed, Feb 20, 2002 at 07:15:49AM -0800, Jeff Eckermann wrote: > If I understand right, many Oracle applications limit data access by > users (thereby enhancing security) by allowing data access only via > procedures. I am not sure if many Oracle applications do this, but I know that it is possible in Oracle, Solid and at least few others. I think MS-SQL and Sybase support it as well. > You appear to be saying that you like this feature, and you want it to > be available in PostgreSQL. It is currently used in our Solid databse and redesigning it would be a LOT of work. And we used it to create an extra level of security for out database. So that if someone would have been able to break though the Apache/PHP security checks he still wouldn't been able to do anything that he was wasn't allowed to do anyway. > PostgreSQL has a different method of achieving the same result, as has > already been pointed out (views with appropriate rewrite rules). It doesn't look to me to have the same result. But even if it would give me the same result, a stored procedure which is owned by a user (just like a user owns tables, views and sequences) and runs as this user make sense in my opinion. And then it is also natural to have this user use GRANT to grant others to use his functions while running them with his privileges. At least, this is what I expected and in the other databases I looked at, it works like this. So I was very surpriced that PostgreSQL didn't support it. > If there is not a strong desire among PostgreSQL users for the feature > that you want, that is probably because the existing method works well > for them. Or that they didn't think of the possibility.. > Remember that PostgreSQL is a project run and developed by volunteers. > The strongest vote for a new feature is the submission of code. The > developers are not insensitive to the requests of users, but: if > development is left to them, don't be surprised that they use their > own judgement about what is most important. > > I have never seen anyone on this list say that Oracle is not a good > product. I didn't mean this. It is just that this is a vital feature fro me, because I would have to completely rewite the applications we now have without it. And that is simply not an option. -- #!perl # Life ain't fair, but root passwords help. # Eric Veldhuyzen eric@terra.telemediair.nl $!=$;=$_+(++$_);($:,$~,$/,$^,$*,$@)=$!=~ # Perl Monger /.(.)...(.)(.)....(.)..(.)..(.)/;`$^$~$/$: $^$*$@$~ $_>&$;`
Attachment
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@terra.telemediair.nl $!=$;=$_+(++$_);($:,$~,$/,$^,$*,$@)=$!=~ # Perl Monger /.(.)...(.)(.)....(.)..(.)..(.)/;`$^$~$/$: $^$*$@$~ $_>&$;`