When PostgreSQL returns permission denied for large object, turning lo_compat_privileges to on can look like a quick way forward. In some cases, the permission error does disappear.

The setting does not grant a missing privilege, however. It switches large objects back toward the compatibility behavior that existed before PostgreSQL 9.0 introduced their current privilege model.

If you inherit a database where the setting is on, how can you return it to off without breaking the application? The answer starts with the boundary of the bypass, then follows the failed operation back to ownership or ACLs.

Removing a permission error by bypassing the large-object ACL

In the PostgreSQL 18 compatibility settings, lo_compat_privileges defaults to off. Turning it on disables the large-object privilege checks added in PostgreSQL 9.0.

The affected path includes the read and write checks in lo_open() and operations built on that path, such as lo_get(), lo_put(), lo_read(), and lo_truncate(). The ownership check in lo_unlink() and the checks around COMMENT and SECURITY LABEL on large objects are bypassed as well.

Not every security check disappears. Privilege requirements for GRANT ... ON LARGE OBJECT, ownership requirements for ALTER LARGE OBJECT ... OWNER TO, and the separate permissions for server-side lo_import() and lo_export() remain. The official documentation likewise says that the setting disables only the checks whose behavior changed in PostgreSQL 9.0.

This is therefore not a newly announced CVE or a bug limited to one current release. It is a warning about a longstanding compatibility setting. The practical risk depends on whether the database uses large objects, which roles can connect, and where the setting is effective.

PostgreSQL 9.0 ownership and ACLs define the compatibility boundary

PostgreSQL 9.0, released on September 20, 2010, added per-large-object ownership and access privileges. Before that change, large objects had no permission structure and database users could read or modify them.

Under the current large-object implementation, reading requires SELECT, while writing and truncating require UPDATE. Deleting, commenting on, or changing the owner is limited to the owner or a database superuser.

Large objects are managed independently from ordinary table rows, and their owner and ACL live in pg_largeobject_metadata. Access to a referencing table therefore does not fix a mismatch between the role that created a large object and the role the application uses to read it.

One concrete example is an import performed by a migration or ETL role while the application connects as a different role. Enabling the compatibility setting can hide the resulting error, but it leaves the ownership or ACL mismatch unresolved.

Find the effective value under the application's connection conditions

Start with the same database and role the application uses. SHOW returns the value resolved for the current session.

SHOW lo_compat_privileges;

To distinguish a configuration-file value from database, role, session, or other sources, inspect source in pg_settings. sourcefile and sourceline help when a configuration file supplied the value, although they can be NULL for a role without sufficient visibility.

SELECT setting, source, sourcefile, sourceline
FROM pg_settings
WHERE name = 

$$l{o}_{c}ompa{t}_{p}rivileges$$

;

Only a superuser or a role with SET privilege on the parameter can change it. Per-parameter SET privileges were introduced in PostgreSQL 15, so delegated roles are part of the audit on version 15 and later.

Before changing a persistent setting, an authorized operator can switch it off temporarily in a test transaction and observe which application operation fails. SET LOCAL lasts only for that transaction.

BEGIN;
SET LOCAL lo_compat_privileges = off;
SHOW lo_compat_privileges;
ROLLBACK;

Run the relevant read or write operation between SHOW and ROLLBACK. Use a test environment with a restricted role and controlled data rather than experimenting in a production traffic session.

Repair ownership mismatches from pg_largeobject_metadata with least privilege

If the effective value is on, inventory large-object owners and ACLs next. lomowner is a role OID, so pg_get_userbyid() makes the result easier to review.

SELECT
  oid,
  pg_get_userbyid(lomowner) AS owner_name,
  lomacl
FROM pg_largeobject_metadata
ORDER BY oid;

Grant SELECT to a role that only reads an object, and add UPDATE only when it must write or truncate. The large-object form of GRANT separates those two privileges.

GRANT SELECT ON LARGE OBJECT 12345 TO app_role;
GRANT UPDATE ON LARGE OBJECT 12345 TO app_role;

If the application is genuinely responsible for deletion and lifecycle management, consider transferring ownership. ALTER LARGE OBJECT requires ownership of the object and the ability to switch to the new owning role.

ALTER LARGE OBJECT 12345 OWNER TO app_role;

12345 and app_role are placeholders. Before generating changes in bulk, map each object to its reference, expected owner, and required operations. If the application only reads it, granting SELECT expresses a narrower intent than transferring ownership.

Fix the vacuumlo execution role instead of weakening the setting

vacuumlo finds and removes large objects that are not referenced by an oid or lo column. Its official documentation also provides --dry-run to show candidates without deleting them.

vacuumlo --dry-run --username=maintenance_role database_name

Deletion eventually uses lo_unlink(), so the operation fails when the execution role does not own a candidate object. Instead of avoiding that failure with lo_compat_privileges = on, run vacuumlo as the appropriate owner or a superuser and review the dry-run candidates first.

This aligns the maintenance job with the same ownership model the application follows. It also leaves an operational rule that will behave predictably on the next run.

Returning to off turns permission failures back into design signals

The answer to the opening question is to avoid flipping a global setting first. Resolve the effective value under the application's connection conditions, identify the operation that fails with the setting off, and then repair the relevant ownership or ACL.

lo_compat_privileges = off is more than a recommended default. It restores PostgreSQL's answer to who may read, write, and delete each large object—and turns a permission error back into useful diagnostic information.

For databases that use large objects, the practical endpoint is an audit that connects the setting, pg_largeobject_metadata, application roles, and maintenance jobs under one explicit privilege model.

Source

• Title: Christophe Pettus: All Your GUCs in a Row: lo_compat_privileges
• URL: https://postgr.es/p/9sZ