Thread: PG 8.4 to 9.2 upgrade issues with ownership of large objects
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
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
David Wall <d.wall@computer.org> writes:
> 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.
Large objects didn't have privileges, nor owners, in 8.4. If you don't
feel like fixing your apps right now, you can return to the previous
behavior by setting the obscurely-named lo_compat_privileges setting in
postgresql.conf.
regards, tom lane
On 12/16/2012 11:22 AM, Tom Lane wrote: > David Wall <d.wall@computer.org> writes: >> 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. > Large objects didn't have privileges, nor owners, in 8.4. If you don't > feel like fixing your apps right now, you can return to the previous > behavior by setting the obscurely-named lo_compat_privileges setting in > postgresql.conf. > > regards, tom lane > Thanks for the information, Tom and Adrian. I am using the latest JDBC driver and have not noted any other issues with large objects accessed, created or deleted using the blob interfaces. What does fixing an app mean or entail? We've always accessed large objects as a simple blob stored and referenced in a table as an OID with both having the same lifetime.
David Wall <d.wall@computer.org> writes:
> On 12/16/2012 11:22 AM, Tom Lane wrote:
>> Large objects didn't have privileges, nor owners, in 8.4. If you don't
>> feel like fixing your apps right now, you can return to the previous
>> behavior by setting the obscurely-named lo_compat_privileges setting in
>> postgresql.conf.
> I am using the latest JDBC driver and have not noted any other issues
> with large objects accessed, created or deleted using the blob
> interfaces. What does fixing an app mean or entail? We've always
> accessed large objects as a simple blob stored and referenced in a table
> as an OID with both having the same lifetime.
It would only be an issue if you created large objects under one role
and then tried to access them under another, since the default
permissions would forbid that. I assumed since you were complaining
that you'd run into something of the sort ...
regards, tom lane
On 12/16/2012 6:21 PM, Tom Lane wrote: > David Wall <d.wall@computer.org> writes: >> On 12/16/2012 11:22 AM, Tom Lane wrote: >>> Large objects didn't have privileges, nor owners, in 8.4. If you don't >>> feel like fixing your apps right now, you can return to the previous >>> behavior by setting the obscurely-named lo_compat_privileges setting in >>> postgresql.conf. >> I am using the latest JDBC driver and have not noted any other issues >> with large objects accessed, created or deleted using the blob >> interfaces. What does fixing an app mean or entail? We've always >> accessed large objects as a simple blob stored and referenced in a table >> as an OID with both having the same lifetime. > It would only be an issue if you created large objects under one role > and then tried to access them under another, since the default > permissions would forbid that. I assumed since you were complaining > that you'd run into something of the sort ... > > regards, tom lane > Okay, that's good. I suspect the JDBC library is taking care of this, but I'll check with them. I think our issue was just on the restore of the backup from 8.4 which had no owner/permissions and the restore into 9.2 in which they were assigned. Our apps all use a more limited role than what the db admin uses, and I guess that's when it first appeared. We do reset all of the GRANTs for tables for the application role, but I guess that doesn't make it down to the large objects. I'll read up more on permissions as they relate large objects. Thanks again for all your help, Tom. David
On 12/16/2012 6:21 PM, Tom Lane wrote: >> David Wall <d.wall@computer.org> writes: >>> On 12/16/2012 11:22 AM, Tom Lane wrote: >>>> Large objects didn't have privileges, nor owners, in 8.4. If you >>>> don't >>>> feel like fixing your apps right now, you can return to the previous >>>> behavior by setting the obscurely-named lo_compat_privileges >>>> setting in >>>> postgresql.conf. >> > I'll read up more on permissions as they relate large objects. > Okay, I cannot find any information on how to view the permissions for a large object. I know that psql's \dp can be used to see the permissions on a table, but how do I see the permissions assigned to a large object? The docs show how to use GRANT/REVOKE.... ON LARGE OBJECT loid, and a script that shows ALTER LARGE OBJECT loid OWNER TO..., but I don't see anything about retrieving the loid's permissions. Thanks, David
David Wall <d.wall@computer.org> writes:
> Okay, I cannot find any information on how to view the permissions for a
> large object. I know that psql's \dp can be used to see the permissions
> on a table, but how do I see the permissions assigned to a large object?
AFAICS psql doesn't have any support for this --- you'd need to look
directly at pg_largeobject_metadata.
regards, tom lane