Re: [GENERAL] About permissions on large objects
Hi Howard, Il giorno mer, 13/07/2011 alle 23.30 +0100, Howard Cole ha scritto: Hi Guiseppe, Perhaps you can create a trigger that monitors for the insertion of an oid and then grant permissions. No idea if this can be done, but if it can it will save you lots of repeated grants. [...] Thanks for your tip. I already created a trigger on all my tables, as this one: CREATE OR REPLACE FUNCTION grant_large_object() RETURNS trigger AS ' BEGIN execute ''GRANT SELECT,UPDATE ON LARGE OBJECT '' || NEW.IMAGE || '' TO agenzia_r''; RETURN NEW; END;' LANGUAGE 'plpgsql'; CREATE TRIGGER grant_large_object AFTER INSERT OR UPDATE ON agenzia.imagebydocument FOR EACH ROW EXECUTE PROCEDURE grant_large_object(); And it seems to be working right. I still would like to know if there is any way to query acl metadata, maybe from table pg_catalog.pg_largeobject_metadata in order to collect information about granted rights on large objects. Bye, Giuseppe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] About permissions on large objects
On 13/07/2011 8:49 AM, Giuseppe Sacco wrote: Hi all, I moved a few clusters from 8.4 to 9.0 since I required the new way of authenticating against LDAP (or, in my case, AD). Now, I found the new database version introduced permissions on large object, so my application, in order to share large object across a group, require a bit of change. While the application code will be changed in order to give rights on large objects too, I would like to know if there is any way for listing current rights, i.e., for finding all large objects that still need to have permissions changed. Currently I cannot know how to distinguish what large objects have already been granted, so I do give permissions to all large objects. This is quite time consuming, about 5 minutes, and need to be executed a few times per hour. This is what I do now: do $$ declare r record; begin for r in select distinct loid from pg_catalog.pg_largeobject loop execute 'GRANT SELECT,UPDATE ON LARGE OBJECT ' || r.loid || ' TO agenzia_r'; end loop; end$$; Is there a better/faster way? Thanks, Giuseppe As an interim solution, you could set the large object compatibility: www.postgresql.org/docs/9.0/interactive/runtime-config-compatible.html#GUC-LO-COMPAT-PRIVILEGEScompatible.html#GUC-LO-COMPAT-PRIVILEGES Howard www.selestial.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] About permissions on large objects
Hi Howard, Il giorno mer, 13/07/2011 alle 13.18 +0100, Howard Cole ha scritto: [...] As an interim solution, you could set the large object compatibility: www.postgresql.org/docs/9.0/interactive/runtime-config-compatible.html#GUC-LO-COMPAT-PRIVILEGES thanks for pointing to this option. I already evaluated it and decided to keep 9.0 with new large object permissions since I think it is a good thing. Is there any other possibility? Thanks to all, Giuseppe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] About permissions on large objects
On 13/07/2011 8:15 PM, Giuseppe Sacco wrote: Is there any other possibility? Hi Guiseppe, Perhaps you can create a trigger that monitors for the insertion of an oid and then grant permissions. No idea if this can be done, but if it can it will save you lots of repeated grants. An easier option to use the compatibility option and then, when you have updated your code, you can turn off the compatibility mode and run your script once. Howard Cole www.selestial.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] About permissions on large objects
On Wed, 2011-07-13 at 23:30 +0100, Howard Cole wrote: On 13/07/2011 8:15 PM, Giuseppe Sacco wrote: Is there any other possibility? Hi Guiseppe, Perhaps you can create a trigger that monitors for the insertion of an oid and then grant permissions. No idea if this can be done, but if it can it will save you lots of repeated grants. Large Objects are inserted in a system table. And you cannot add triggers to system tables. So this can't work. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
