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
