Thread: About permissions on large objects

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

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

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

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

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

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