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

Thanks Dave, but we don't have any issues with BLOB APIs and just use
the JDBC getBlob/setBlob calls and do not ever directly work with large
objects. Since the JDBC library is doing the actual large object calls
for us, I was checking to see if it did anything different in the PG 9.0
and later code when permissions were added to large objects.

Our presumption is that the creation of a large object through the JDBC
INSERT statement using the PreparedStatement.setBlob() API results in it
being owned by the role associated with the Connection used. (Sadly, I
still cannot find out how to even check what permissions are on a LOID,
just how to GRANT/ALTER them.)

David

On 12/17/2012 10:44 AM, Dave Cramer wrote:
> 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-example
> for 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
> <mailto: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)
> 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
> <mailto:pgsql-jdbc(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>