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@gmail.com
