Permissions on large objects - db backup and restore
От | David Wall |
---|---|
Тема | Permissions on large objects - db backup and restore |
Дата | |
Msg-id | 515CA0C1.2000603@computer.org обсуждение исходный текст |
Ответы |
Re: Permissions on large objects - db backup and restore
|
Список | 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
В списке pgsql-general по дате отправления: