Thread: Permissions on large objects - db backup and restore

When we upgraded from PG 8.4 to PG 9.2, we ran into a permissions issue
with large objects as discussed here:
http://postgresql.1045698.n5.nabble.com/Large-Object-permissions-lost-in-transfer-td4281604.html

The basic solution was to do an ALTER LARGE OBJECT and set the OWNER TO
using a script like the following in our bash script:

do \$\$
declare r record;
begin
for r in select distinct loid from pg_catalog.pg_largeobject loop
execute 'ALTER LARGE OBJECT ' || r.loid || ' OWNER TO $DBUSER';
end loop;
end\$\$;
CLOSE ALL;

I thought it had to do with an 8.4 backup and a 9.2 restore, but even
when I did a backup on a 9.2.2 and restored on a 9.2.3 (we migrated to a
new server at the same time), the same issue arose. Is there a setting
for pg_dump and pg_restore so that our large objects don't run into this
issue?  I suspect I'm missing something easy, or do I just need to do
this after any restore where I change systems?  I suspect it's because
my PG is owned with a superuser account like 'dbadmin' which I use to
run the backups and restores, while the DB itself is owned by a less
privileged user account like 'dbuser'.  It may be that on restore, the
large objects are all owned by dbadmin instead of dbuser?

Thanks for any clarifications.  I may just find I'll put that script
above in my table grants that we use to set all such permissions for tables.

David

David Wall <d.wall@computer.org> writes:
> When we upgraded from PG 8.4 to PG 9.2, we ran into a permissions issue
> with large objects as discussed here:
> http://postgresql.1045698.n5.nabble.com/Large-Object-permissions-lost-in-transfer-td4281604.html

> The basic solution was to do an ALTER LARGE OBJECT and set the OWNER TO
> using a script like the following in our bash script:

This isn't terribly surprising, since 8.4 didn't have a notion of owners
for large objects at all.  The blobs would've wound up owned by
whichever user did the restore.

> I thought it had to do with an 8.4 backup and a 9.2 restore, but even
> when I did a backup on a 9.2.2 and restored on a 9.2.3 (we migrated to a
> new server at the same time), the same issue arose.

A 9.2->9.2 dump and restore certainly should preserve large object
ownership (and permissions, if you've set any).  In a quick check I do
see "ALTER LARGE OBJECT nnn OWNER TO ..." commands in pg_dump's output
for such a case.  Are you sure this is really the "same" issue?  Are you
doing something strange like using pg_dump's --no-owner option?  Did you
get any errors while doing the pg_dump or pg_restore?  (Trying to run
the restore as non-superuser would mean the ALTER OWNER commands would
fail, but you should have gotten plenty of bleats about that.)

            regards, tom lane

A 9.2->9.2 dump and restore certainly should preserve large object ownership (and permissions, if you've set any). In a quick check I do see "ALTER LARGE OBJECT nnn OWNER TO ..." commands in pg_dump's output for such a case. Are you sure this is really the "same" issue? Are you doing something strange like using pg_dump's --no-owner option? Did you get any errors while doing the pg_dump or pg_restore? (Trying to run the restore as non-superuser would mean the ALTER OWNER commands would fail, but you should have gotten plenty of bleats about that.) regards, tom lane

We used this to backup on server1 (9.2.2):

pg_dump --format=c --oids dbname

And we restored on server2 (9.2.3) with:

 pg_restore -v -O -d dbname

The application user/role is the same name as the dbname but has more limited permissions than the superuser/role $PGUSER used when running pg_dump and pg_restore.

How can I check if ALTER LARGE OBJECT is specified in my backup?  Do I need to change the --format option?

I'm not positive it was the same issue as when I upgraded from 8.4, but I did note that my db had a different pg_largeobject_metadata.lomowner value before and after I ran that DO script to alter each and the problem with reading a large object in my code went away.

Thanks, David

David Wall <d.wall@computer.org> writes:
> On 4/3/2013 3:14 PM, Tom Lane wrote:
>> A 9.2->9.2 dump and restore certainly should preserve large object
>> ownership (and permissions, if you've set any). In a quick check I do
>> see "ALTER LARGE OBJECT nnn OWNER TO ..." commands in pg_dump's output
>> for such a case. Are you sure this is really the "same" issue? Are you
>> doing something strange like using pg_dump's --no-owner option?

> We used this to backup on server1 (9.2.2):
> pg_dump --format=c --oids /dbname/
> And we restored on server2 (9.2.3) with:
> pg_restore -v -O -d /dbname/

$ pg_restore -?
 ...
  -O, --no-owner               skip restoration of object ownership
 ...

So there you have it.  pg_restore just restored all the objects (blobs
and otherwise) as owned by the user running it.  I should think you'd
have had issues with other things besides the blobs by now.

            regards, tom lane

On 4/3/2013 5:57 PM, Tom Lane wrote:
> $ pg_restore -? ... -O, --no-owner skip restoration of object
> ownership ... So there you have it. pg_restore just restored all the
> objects (blobs and otherwise) as owned by the user running it. I
> should think you'd have had issues with other things besides the blobs
> by now. regards, tom lane

Thanks you!  Unsure why that -O was being used since when I check
version control, it's been in there since PG 8.3 days.  I hadn't checked
on its meaning and just assumed it was something to do with OIDs like
the pg_dump flags.  No doubt it solved something back then since TABLE
GRANTS could be run after a restore since before 9.0 the loids had no
permission issue to deal with.

Thanks again, Tom!  Best regards, David