Re: 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