Thread: Large Object permissions lost in transfer
I have recently transferred data from 8.4 to 9.0 and now only my superuser has read access to the large objects. (The transfer was done using pg_dump version 9.0). The large objects have all transferred, the problem is just the permissions, - but how do I set up the permissions for the large objects? Thanks, Howard Cole www.selestial.com
On 4/4/2011 6:12 AM, Howard Cole wrote: > I have recently transferred data from 8.4 to 9.0 and now only my > superuser has read access to the large objects. > > (The transfer was done using pg_dump version 9.0). > > The large objects have all transferred, the problem is just the > permissions, - but how do I set up the permissions for the large objects? > > Thanks, > > Howard Cole > www.selestial.com > I had the same problem. and there is no grant all... there is the new DO though. That's what I used, a simple DO expression to iterate and update the permissions. -Andy
On Monday, April 04, 2011 6:47:44 am Andy Colson wrote: > On 4/4/2011 6:12 AM, Howard Cole wrote: > > I have recently transferred data from 8.4 to 9.0 and now only my > > superuser has read access to the large objects. > > > > (The transfer was done using pg_dump version 9.0). > > > > The large objects have all transferred, the problem is just the > > permissions, - but how do I set up the permissions for the large objects? Is to late to redo the dump/restore? If not you may want to take a look at: http://www.postgresql.org/docs/9.0/interactive/runtime-config- compatible.html#GUC-LO-COMPAT-PRIVILEGES > > > > Thanks, > > > > Howard Cole > > www.selestial.com > > I had the same problem. and there is no grant all... there is the new > DO though. That's what I used, a simple DO expression to iterate and > update the permissions. > > -Andy -- Adrian Klaver adrian.klaver@gmail.com
On 4/4/2011 9:19 AM, Howard Cole wrote: > On 04/04/2011 2:47 PM, Andy Colson wrote: >>> permissions, - but how do I set up the permissions for the large >>> objects? >> I had the same problem. and there is no grant all... there is the new >> DO though. That's what I used, a simple DO expression to iterate and >> update the permissions. >> >> -Andy >> > Thanks Andy. As a temporary measure I am going to use the server > configuration variable lo_compat_privileges when I work out how to use > it. For the longer term, could I borrow your script for the DO :) > I dont seem to have it anymore... but here is a re-created, untested version. do $$ delcare r record; begin for r in select loid from pg_catalog.pg_largeobject loop execute 'ALTER LARGE OBJECT ' || r.loid || ' OWNER TO andy'; end loop; end$$; I wanted to change the owner.. where-as you want grant... but it should get you the idea. -Andy
Andy Colson <andy@squeakycode.net> writes:
> On 4/4/2011 9:19 AM, Howard Cole wrote:
>> Thanks Andy. As a temporary measure I am going to use the server
>> configuration variable lo_compat_privileges when I work out how to use
>> it. For the longer term, could I borrow your script for the DO :)
> I dont seem to have it anymore... but here is a re-created, untested
> version.
> do $$
> delcare r record;
> begin
> for r in select loid from pg_catalog.pg_largeobject loop
> execute 'ALTER LARGE OBJECT ' || r.loid || ' OWNER TO andy';
> end loop;
> end$$;
Suggest "select distinct loid" to avoid a lot of duplicated work,
otherwise this should be fine.
regards, tom lane
>> configuration variable lo_compat_privileges when I work out how to use >> it. For the longer term, could I borrow your script for the DO :) >> > > I dont seem to have it anymore... but here is a re-created, untested > version. > > do $$ > delcare r record; > begin > for r in select loid from pg_catalog.pg_largeobject loop > execute 'ALTER LARGE OBJECT ' || r.loid || ' OWNER TO andy'; > end loop; > end$$; > > > I wanted to change the owner.. where-as you want grant... but it > should get you the idea. > > -Andy > Thanks All, Especially Andy. I shall not bother thanking Tom because in his omnipient state - He knows! ;) Assistance on this forum is soooo good. It puts most of the support I pay for to shame. (for non postgres stuff) Howard.