JDBC with PG 9.2 and large object permissions as they relate to blobs (after upgrade from 8.4)
От | David Wall |
---|---|
Тема | JDBC with PG 9.2 and large object permissions as they relate to blobs (after upgrade from 8.4) |
Дата | |
Msg-id | 50CF651C.9040004@computer.org обсуждение исходный текст |
Ответы |
Re: JDBC with PG 9.2 and large object permissions as they
relate to blobs (after upgrade from 8.4)
|
Список | 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 по дате отправления: