pg lo permissions

Tue Jul 10 2018 00:00:00 GMT+0000 (UTC)

Just some interesting code to remember

The Issue

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.

Possible Solutions

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;

My Solution

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();