PostgreSQL's local_preload_libraries parameter: for those who can't use the other two

PostgreSQL has three parameters for preloading shared libraries into a backend, and the least used of them is precisely the subject of another installment in Christophe Pettus's "All Your GUCs in a Row" series. In his post on The Build (republished on Planet PostgreSQL), Pettus describes local_preload_libraries bluntly: it's "the preload parameter for people who aren't allowed to use the other two." It's worth understanding exactly what it does, why it exists, and in what rare scenario it's the right tool, because, according to the failure mechanism the source describes further on, confusion around it can even prevent superusers from connecting.

What it does and how it differs from the others

PostgreSQL offers three preload GUCs:

• shared_preload_libraries: loads into the postmaster process, requires a restart, context postmaster;
• session_preload_libraries: loads into each backend on connection, superuser only, reads from anywhere in $libdir;
• local_preload_libraries: loads into each backend on connection, any role can set it, but only reads from a single directory, $libdir/plugins.

local_preload_libraries works almost the same as session_preload_libraries, with two crucial differences: any role can configure it, and in exchange for that freedom, it's restricted to $libdir/plugins, a directory that in a default installation is empty (when it even exists). Its default value is empty and its context is user, which allows setting it in postgresql.conf, via ALTER ROLE ... SET, via ALTER DATABASE ... SET, or on the connection itself with PGOPTIONS="-c local_preload_libraries=mylib".

The SET that does nothing

A treacherous detail pointed out by Pettus: it's possible to run a SET local_preload_libraries in the middle of a session. The command succeeds, SHOW reports the new value, and nothing gets loaded. The list is read only once, during backend initialization, and never again. In the author's words, it's one of the few GUCs where a successful SET is "purely decorative."

Under the hood, after authentication and once role and database settings (pg_db_role_setting) have been applied, the backend walks through session_preload_libraries first and then local_preload_libraries, doing the equivalent of a LOAD for each entry. For the local list, a simple name like mylib is rewritten to $libdir/plugins/mylib. Anything that doesn't have exactly the form $libdir/plugins/, with no additional directory separators (which blocks tricks with ..), gets rejected with access to library "..." is not allowed. dynamic_library_path doesn't come into play: the path is fixed.

The directory is the entire security model

This point deserves the attention of any DBA concerned with governance. $libdir/plugins is the parameter's only security model. PostgreSQL never puts anything there on its own. An administrator must deliberately install each library, and from the moment it's in the directory, every role on the server can load it in their own sessions. The documentation places on the administrator the responsibility of installing only "safe" libraries, and it's precisely because of that responsibility that the directory is empty in practically every installation.

A parameter in search of a use case

The history explains a lot. The parameter was born in PostgreSQL 8.2, in the same commit that renamed preload_libraries to shared_preload_libraries and invented the plugins directory. The motivation was EDB's PL/pgSQL debugger, which wanted to let non-superusers load an instrumentation hook in their own backends. Except the debugger migrated to shared_preload_libraries a few versions later (global breakpoints require it), and local_preload_libraries was left holding a use case nobody had.

Worse: from 8.2 through 9.4, it had backend context, configurable only via postgresql.conf or PGOPTIONS, while the documentation cheerfully recommended ALTER ROLE SET, which had never worked. 9.4 brought session_preload_libraries, which was what DBAs actually wanted: restricted to superuser, loading from anywhere in $libdir, and working with ALTER ROLE. Only in 9.5 did local_preload_libraries move to user context, finally making ALTER ROLE deliver on what the manual had been promising for years. As Pettus sums it up, it has a job, "except it's a job almost nobody hired it to do."

The failure mode that takes down connections

Here's the concrete operational risk. Like the entire preload family, if a listed library isn't found, the connection fails with FATAL. Nothing validates the name at configuration time. An ALTER ROLE developer SET local_preload_libraries = 'auto_explian' (note the typo) is accepted without complaint, and developer finds out about the problem on the next login, requiring a superuser to run ALTER ROLE developer RESET local_preload_libraries to let them back in.

If the typo is in postgresql.conf, nobody can connect, not even superusers. Since the context isn't postmaster, fixing the file and reloading solves it, without a restart. But you won't have a session to run pg_reload_conf(), so the way out is pg_ctl reload from the shell. For diagnostics, in PostgreSQL 18 pg_get_loaded_modules() reports what was actually loaded in the session (the useful column is file_name); before 18 there's no SQL-level answer, and you're left reading /proc//maps.

When (not) to use it

Pettus's guidance is direct: if you're the DBA and want a library in some sessions, this isn't your parameter. Use session_preload_libraries with ALTER ROLE, which loads from $libdir without requiring the curation of a directory, and whose superuser gate is the right gate.

The one job local_preload_libraries does, and nothing else, is letting roles enable themselves on their own, per connection, without filing a ticket. The practical example that closes the piece: symlink auto_explain.so into $libdir/plugins/, grant GRANT SET ON PARAMETER auto_explain.log_min_duration, auto_explain.log_analyze TO developers (GRANT SET requires version 15 or later, since auto_explain.* parameters have superuser context), and then a developer can turn on plan logging for a single psql session with just PGOPTIONS, without depending on anyone. It's a real convenience, even if a narrow one.

If you're reaching for this parameter for any other reason, it's probably because session_preload_libraries is restricted to superuser and you aren't one, and that, Pettus concludes, is the parameter working exactly as designed.

Source 1: Planet PostgreSQL (https://postgr.es/p/9sY)