JDBC with PG 9.2 and large object permissions as they relate to blobs (after upgrade from 8.4) - Mailing list pgsql-jdbc

From David Wall
Subject JDBC with PG 9.2 and large object permissions as they relate to blobs (after upgrade from 8.4)
Date
Msg-id 50CF651C.9040004@computer.org
Whole thread Raw
Responses Re: JDBC with PG 9.2 and large object permissions as they relate to blobs (after upgrade from 8.4)  (Dave Cramer <pg@fastcrypt.com>)
List pgsql-jdbc
We upgraded our PG from 8.4 to 9.2 and found that our pg_dump and
pg_restore caused all of the large objects to be given permission to our
db admin user, but not our application user.  This resulted in some null
pointers in our java code when trying to access an OID column, with the
PG error saying: permission denied for large object NNNN.

I checked with the excellent PG team and they pointed out that prior to
9.0 large objects didn't have any access permissions/roles assigned to
them.  So I guess on restore, all of the large objects became owned by
our DB admin account, and despite all of our application role table
GRANTS, these permission changes did not go down to the related large
objects.

I'm just checking if the latest JDBC library is doing anything special
with respect to large objects and permissions.  We just use the
PreparedStatement getBlob()/setBlob().  Will the large objects be owned
by the role that executes the INSERT?

By the way, we used the following psql script (found in posting
http://doginpool.blogspot.com/2011/10/today-upgraded-to-new-ubuntu-11.html)
to give all large objects ownership back to our application role and
this seems to have resolved our issue:

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,
David


pgsql-jdbc by date:

Previous
From: Scott Harrington
Date:
Subject: Re: performance problem of Failover Datasource?
Next
From: Dave Cramer
Date:
Subject: Re: JDBC with PG 9.2 and large object permissions as they relate to blobs (after upgrade from 8.4)