Large Object permissions lost in transfer

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.