In PostgreSQL releases prior to 9.0, large objects did not have access privileges and were, therefore, always readable and writable by all users.
PostgreSQL 9.0 introduced a new permission concept and large objects are restricted after an database upgrade. (column lomacl of table pg_largeobject_metadata). By default, nobody except the owner (column lomowner) has any permissions for a large object.
To fix that, we need to grant permissions for a user for each large object individually or disable the new permission model at all.
Disable lo permissions
To temporary disable the new permission model:
ALTER DATABASE dbname SET lo_compat_privileges TO on;
To permanent disable the new permission model add this line to postgresql.conf:
lo_compat_privilege = on
Grant a permission to a single large object:
GRANT SELECT ON LARGE OBJECT :objid TO databaserole;
Assuming we already have a group 'readonly' which should get read access to all large objects. We have tables 'tbl_invoice_file_details' and 'tbl_invoice_file_voucher' which have a reference to all valid large object IDs (oid).
I created a temporary plpgsql function to grant the select permission to 'readonly' to all large objects:
CREATE OR REPLACE FUNCTION namui_grant_lo() RETURNS void AS $BODY$ DECLARE r BIGINT; BEGIN FOR r IN SELECT blob_oid FROM tbl_invoice_file_details LOOP EXECUTE 'GRANT SELECT ON LARGE OBJECT ' || r || 'TO readonly'; END LOOP; FOR r IN SELECT blob_oid FROM tbl_invoice_file_voucher LOOP EXECUTE 'GRANT SELECT ON LARGE OBJECT ' || r || 'TO readonly'; END LOOP; RETURN; END; $BODY$ LANGUAGE plpgsql; SELECT namui_grant_lo(); DROP FUNCTION namui_grant_lo();