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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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? 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general