Permissions on large objects - db backup and restore

From: David Wall <d(dot)wall(at)computer(dot)org>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Permissions on large objects - db backup and restore
Date: 2013-04-03 21:36:01
Message-ID: 515CA0C1.2000603@computer.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jigar Shah 2013-04-03 21:48:37 Re: corrupted item pointer in streaming based replication
Previous Message Robert Fitzpatrick 2013-04-03 20:22:21 Re: could not load plperl library SOLVED