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
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 |