Обсуждение: function privileges
Quick question on function privileges: what exactly does GRANT/REVOKE ON FUNCTION fun_name() actually do? I've tried a few scenarios out, and cannot figure out when this is used. Let us say I have a function F(int4) that does some selection on a table T. If user A comes along and has no SELECT privileges on T, but has EXECUTE privileges on F, A is not permitted to run the function, with an error stating access to T is needed. Now, if user B comes along and has SELECT privileges on T, but not EXECUTE privileges on F, B is permitted to run the function. I'm sure I'm missing some scenario where privileges on F make a difference, but I can't find any info in the docs on this. Can anyone help me out understanding this? Thanks! Murat
On Thu, Feb 02, 2006 at 05:45:16PM -0500, Murat Tasan wrote: > Quick question on function privileges: what exactly does GRANT/REVOKE > ON FUNCTION fun_name() actually do? It grants or revokes the ability to execute the function. It doesn't automatically grant or revoke access to objects that the function might reference; you have to set those objects' permissions separately. > I've tried a few scenarios out, and cannot figure out when this is > used. Let us say I have a function F(int4) that does some selection > on a table T. > > If user A comes along and has no SELECT privileges on T, but has > EXECUTE privileges on F, A is not permitted to run the function, with > an error stating access to T is needed. The user *is* permitted to run the function but the function is failing because of the permissions on T. > Now, if user B comes along and has SELECT privileges on T, but not > EXECUTE privileges on F, B is permitted to run the function. A function's default privileges allow anybody to execute it; revoking a particular user's permission has no effect unless you've also revoked permission from PUBLIC. I'm guessing you did something like this: CREATE FUNCTION f(integer) ... REVOKE ALL ON FUNCTION f(integer) FROM UserB; The problem is that the function still has "allow everybody" privileges so user B can still execute it. If you want to allow user A to execute the function but not user B then do this: CREATE FUNCTION f(integer) ... REVOKE ALL ON FUNCTION f(integer) FROM PUBLIC; GRANT EXECUTE ON FUNCTION f(integer) TO UserA; You might also be interested in the difference between SECURITY INVOKER (the default) and SECURITY DEFINER; for more information see the CREATE FUNCTION documentation. -- Michael Fuhr
Murat Tasan <murat.tasan@cwru.edu> writes:
> If user A comes along and has no SELECT privileges on T, but has
> EXECUTE privileges on F, A is not permitted to run the function, with
> an error stating access to T is needed.
> Now, if user B comes along and has SELECT privileges on T, but not
> EXECUTE privileges on F, B is permitted to run the function.
I don't think I believe either of the above statements. Perhaps you
could provide a complete example of what you're doing?
regards, tom lane
On 2 Feb 2006, at 23:57, Tom Lane wrote: > Murat Tasan <murat.tasan@cwru.edu> writes: >> If user A comes along and has no SELECT privileges on T, but has >> EXECUTE privileges on F, A is not permitted to run the function, with >> an error stating access to T is needed. All within schema "public"... Let T be a table (say with primary key "id" and column "name") with no SELECT privileges for user A. Now, as the owner of the database, execute CREATE FUNCTION F(T.id%TYPE) RETURNS T.name%TYPE AS $$ SELECT name FROM T WHERE id = $1; $$ LANGUAGE SQL; Now login as user A and try: SELECT * FROM F(69); (and let 69 be some id in T). The execution fails, stating that user A doesn't have SELECT privileges on T. >> Now, if user B comes along and has SELECT privileges on T, but not >> EXECUTE privileges on F, B is permitted to run the function. This latter problem has been addressed by Michael Fuhr's email, I didn't realize all new functions had PUBLIC execution permissions by default, so revoking B's permissions to execute F don't make a difference until I revoke PUBLIC's permissions as well. In fact, I didn't see this anywhere in the documentation, although that's probably my fault from my frequent speed-reading ;-) > I don't think I believe either of the above statements. Perhaps you > could provide a complete example of what you're doing? > > regards, tom lane Thanks again for all the help! Murat
Murat Tasan <murat.tasan@cwru.edu> writes:
> Now, as the owner of the database, execute
> CREATE FUNCTION F(T.id%TYPE) RETURNS T.name%TYPE AS $$ SELECT name
> FROM T WHERE id = $1; $$ LANGUAGE SQL;
> Now login as user A and try:
> SELECT * FROM F(69);
> (and let 69 be some id in T).
> The execution fails, stating that user A doesn't have SELECT
> privileges on T.
Right, because the function is running with A's privileges.
If you want the function to run with its owner's privileges,
label it SECURITY DEFINER.
regards, tom lane
Is there a maximun for database users in PostgreSQL? Is is sensible to create a database with 3000 or more database users? All divided different groups? And give those groups privileges? Or is it better to let PHP handle the user privileges? Greetings Tjibbe
tjibbe@hotmail.com ("Tjibbe Rijpma") writes:
> Is there a maximun for database users in PostgreSQL?
>
> Is is sensible to create a database with 3000 or more database users? All
> divided different groups? And give those groups privileges?
>
> Or is it better to let PHP handle the user privileges?
User identities are stored in a table, pg_shadow, and PostgreSQL is
fairly good at managing data stored in tables :-).
But it is fairly common for applications to not expose database users
to the application users.
For instance, the SAP R/3 system (which doesn't use PostgreSQL; it
typically uses Oracle) generally runs as just one database user.
Likewise, it is common for a web application to have one or just a few
"database users;" think of Slashdot, where there is not really any
reason for each of the many thousands of users to be identifiable
inside the database.
The applications we hook to PostgreSQL generally have small numbers of
database users, however many "application users" there may be.
You'll likely find the same true for you...
--
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/wp.html
Rules of the Evil Overlord #97. "My dungeon cells will not be
furnished with objects that contain reflective surfaces or anything
that can be unravelled." <http://www.eviloverlord.com/>
On Fri, Feb 03, 2006 at 19:15:37 -0500, Chris Browne <cbbrowne@acm.org> wrote: > > But it is fairly common for applications to not expose database users > to the application users. > > For instance, the SAP R/3 system (which doesn't use PostgreSQL; it > typically uses Oracle) generally runs as just one database user. And doing this in the wrong circumstances is a big security whole. For example, giving someone two tier access in Peoplesoft, gives away the whole system because the application is running in an untrusted environment and is connecting as a database user that full access to all of the Peoplesoft tables. > Likewise, it is common for a web application to have one or just a few > "database users;" think of Slashdot, where there is not really any > reason for each of the many thousands of users to be identifiable > inside the database. This isn't the same problem for use with web services, since typically the web server is running in a trusted environment. However, it can make it easier to escalate access in the event of a security breach.
--- Tjibbe Rijpma <tjibbe@hotmail.com> wrote: > Is there a maximun for database users in PostgreSQL? > > Is is sensible to create a database with 3000 or > more database users? All > divided different groups? And give those groups > privileges? > > Or is it better to let PHP handle the user > privileges? > > Greetings > > Tjibbe php has some classes that handle user privileges. i think phpgacl is one of them. i'd have to have one heckuva compelling reason in order to start using pgsql to allow privileges to more than a few db users. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com