Re: JDBC with PG 9.2 and large object permissions as they relate to blobs (after upgrade from 8.4)

David,

As far as I know if you want to use large objects with the JDBC library you
should look at
http://jdbc.postgresql.org/documentation/81/binary-data.html#binary-data-examplefor
examples of how it is handled.

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Mon, Dec 17, 2012 at 1:31 PM, David Wall <d(dot)wall(at)computer(dot)org> wrote:

> 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<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
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-jdbc<http://www.postgresql.org/mailpref/pgsql-jdbc>
>