Re: Permissions on large objects - db backup and restore

David Wall <d(dot)wall(at)computer(dot)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