lo_compat_privileges: the PostgreSQL GUC that silently disables large object security

In the "All Your GUCs in a Row" series, published on Planet PostgreSQL, a post dedicated to lo_compat_privileges deserves the attention of any DBA running production databases. The parameter is described there as the member "with the worst personality" in PostgreSQL's family of compatibility flags, and the reason is straightforward: its side effect is to open up all large object security without leaving a trace.

What the parameter does

Up through PostgreSQL 8.4, a large object belonged to no one: any role able to connect to the database could read it, overwrite it, or remove it. Starting with 9.0, every large object gained an owner and an ACL, recorded in pg_largeobject_metadata. The rules are what you'd expect from a table: SELECT to read, UPDATE to write or truncate, and ownership to run lo_unlink(), comment, grant privileges, or transfer the object.

lo_compat_privileges is the switch that pretends 9.0 never happened. With it turned on (on), the read and write checks in lo_open() are skipped, and with them everything that relies on that function: lo_get(), lo_put(), lo_read(), lo_truncate(), and the client-side lo_export(). The ownership check in lo_unlink() also disappears, along with the one protecting COMMENT ON LARGE OBJECT and SECURITY LABEL. According to the post, this list was checked against the PostgreSQL 18 source code and then tested on a disposable cluster, precisely because the documentation describes the boundary "only vaguely".

Not everything falls away. GRANT ... ON LARGE OBJECT still requires grant options, ALTER LARGE OBJECT ... OWNER TO still requires being the owner (or a member of the owning role), and the server-side versions of lo_import() and lo_export() still need EXECUTE, revoked from PUBLIC since PostgreSQL 11, for the good reason that these functions read and write to the server's file system under the OS user running Postgres itself.

Why it's dangerous in practice

The default is off, and its context is superuser, meaning no restart is involved. A superuser can apply it in a session, fix it on a role or database with ALTER ROLE ... SET / ALTER DATABASE ... SET, or, starting with PostgreSQL 15, delegate it via GRANT SET ON PARAMETER lo_compat_privileges. Each of these paths is a way to downgrade the security of an entire database from a place nobody will think to look.

This is where the post nails the difference from other compatibility GUCs, like array_nulls, backslash_quote, and escape_string_warning. Those preserve a syntax or a format. lo_compat_privileges disables a permissions model for every large object in the database, and it does so silently. There's no log line, no warning, nothing in any statistics view indicating that large objects are, at that moment, open for writing by anyone. The only evidence is running SHOW lo_compat_privileges, and whoever runs that already suspects the problem.

Why anyone turns this on in 2026

Nobody enables the parameter for actual compatibility with 8.4. The concrete reasons, the post points out, are more embarrassing. The most common one: the large objects were imported by one role (a migration user, a superuser running \lo_import, an ETL job) and the application connects with a different role. The first lo_get() fails with permission denied for large object, and someone discovers that this parameter makes the error go away.

The other: vacuumlo calls lo_unlink() on every orphan it finds. If it connects with a role that doesn't own the objects, the first removal fails with must be owner of large object, which puts the batch transaction into an error state and ends the run.

The correct fixes are cheap

The article's central point is that both cases have a simple solution, without needing to compromise the database's security. For vacuumlo, it's enough to run it as the owner of the objects or as a superuser.

For the ownership mismatch, pg_largeobject_metadata itself tells you exactly what belongs to whom, and psql's \gexec handles the rest:

SELECT format('ALTER LARGE OBJECT %s OWNER TO app;', oid)
FROM pg_largeobject_metadata
WHERE lomowner <> 'app'::regrole \gexec

If the application only needs to read the objects, the more restricted version is to swap in GRANT SELECT ON LARGE OBJECT in the same loop, granting only the necessary privilege instead of transferring ownership. The text notes, as a caveat, that the underlying fix is to stop storing files as large objects altogether, but that's a topic for another post.

The lesson for those running production

The recommendation is direct: leave the parameter off. If you inherit a database where it's on, turn it off in session, find the query that breaks, and fix that query's object with a GRANT or an ALTER ... OWNER. The sentence that closes the piece sums up the philosophy of anyone who treats data as a critical asset: "The error was the bug report. The parameter was someone refusing to read it."

The practical takeaway applies to any DBA's daily work in Brazil: a permission denied isn't an obstacle to be silenced with a global flag, it's a signal that the ownership and privilege model is out of step with the application's design. Fixing the source costs a few lines of SQL and preserves the integrity PostgreSQL has offered since version 9.0. Silencing the error with lo_compat_privileges leaves the entire database exposed, with no log and no warning, waiting for an audit to discover it.