PG 8.4 to 9.2 upgrade issues with ownership of large objects

From: David Wall <d(dot)wall(at)computer(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: PG 8.4 to 9.2 upgrade issues with ownership of large objects
Date: 2012-12-16 19:09:26
Message-ID: 50CE1C66.7070101@computer.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

David

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2012-12-16 19:17:25 Re: Authenticate with hash instead of plaintext password?
Previous Message Peter Bex 2012-12-16 19:07:49 Re: Authenticate with hash instead of plaintext password?