On 12/16/2012 11:09 AM, David Wall wrote:
In past PG upgrades, we've done a pg_dump on the current version, then a
pg_restore on the new version. But during the 8.4 to 9.2 upgrade (on
Linux x64), we ran into issues with the permissions associated with the
large objects after the restore.
Is this something new or were we just "lucky" before?
Our postmaster runs many databases, with each database owned by the PG
admin, but we normally just used a set of GRANT statements to provide
appropriate access control to the application user. In our each, each
database has it's own application user which accesses the DB for a web
app (and for convenience, the DBNAME and DBUSER are the same name).
Our pg_dump command is basically: pg_dump --format=c --oids DBNAME
Our pg_restore is basically: pg_restore -v -O -d DBNAME
Should we be doing this differently now as we never found an issue
before this somewhat significant update from 8.4 to 9.2?
We resolved the issue from a posting we saw online that basically
suggested this resolution after the restore with psql:
do $$
declare r record;
begin
for r in select loid from pg_catalog.pg_largeobject loop
execute 'ALTER LARGE OBJECT ' || r.loid || ' OWNER TO DBUSER';
end loop;
end$$;
CLOSE ALL;
Thanks for any good pointers or tips on this.
http://www.postgresql.org/docs/9.2/interactive/lo-implementation.html
..."For compatibility with prior releases, see lo_compat_privileges .."
lo_compat_privileges (boolean)
In PostgreSQL releases prior to 9.0, large objects did not have access
privileges and were, in effect, readable and writable by all users.
Setting this variable to on disables the new privilege checks, for
compatibility with prior releases. The default is off.
Setting this variable does not disable all security checks related to
large objects — only those for which the default behavior has changed in
PostgreSQL 9.0. For example, lo_import() and lo_export() need superuser
privileges independent of this setting.
David
--
Adrian Klaver
adrian.klaver@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
