local_preload_libraries is the preload parameter for people who aren’t allowed to use the other two. It loads shared libraries into a backend at connection time, the same as session_preload_libraries, with two differences: any role can set it, and in exchange it will only load from one directory, $libdir/plugins, which on a stock installation is empty if it exists at all.
Default: empty. Context: user, so it can go in postgresql.conf, in ALTER ROLE ... SET or ALTER DATABASE ... SET, or on the connection itself via PGOPTIONS="-c local_preload_libraries=mylib". You can also change it with a plain SET mid-session, which succeeds, updates what SHOW reports, and loads nothing. The list is read once, during backend startup, and never again. It is one of a handful of GUCs where a successful SET is purely decorative.
The mechanics: after authentication, and after the role and database settings in pg_db_role_setting have been applied, the new backend walks session_preload_libraries and then local_preload_libraries, doing the equivalent of LOAD for each entry. For the local list, a bare name like mylib is rewritten to $libdir/plugins/mylib, and anything not of the form $libdir/plugins/<name> (no further directory separators, so no .. tricks) is rejected with access to library "..." is not allowed. dynamic_library_path does not enter into it; the path is fixed. Non-superuser LOAD enforces the same restriction, but less helpfully: it won’t add the prefix for you, so LOAD 'mylib' fails where LOAD '$libdir/plugins/mylib' works.
That directory is the entire security model. PostgreSQL will never put anything in it. An administrator has to, deliberately, and once a library is there every role on the server can load it into its own sessions. The documentation describes this as the administrator’s responsibility to install only “safe” libraries, and that responsibility is why the directory is empty everywhere.
The parameter dates to 8.2, where it arrived in the same commit that renamed preload_libraries to shared_preload_libraries and invented the plugins directory. The motivating case was the EDB PL/pgSQL debugger, which wanted non-superusers to load an instrumentation hook into their own backends. The debugger moved to shared_preload_libraries within a couple of releases (global breakpoints need it), and local_preload_libraries was left holding a use case nobody had. From 8.2 through 9.4 it was backend context, settable only from postgresql.conf or PGOPTIONS; the docs cheerfully recommended ALTER ROLE SET for it, which had never worked. 9.4 added session_preload_libraries, which was what DBAs had wanted all along: superuser-only, loads from anywhere in $libdir, works with ALTER ROLE. 9.5 then changed local_preload_libraries to user context so that ALTER ROLE finally did what the manual had been promising for years. It isn’t quite a member of the vestigial club alongside array_nulls and bytea_output; it has a job. It’s just a job almost nobody has hired it for.
The failure mode is the one shared by the whole preload family: if a listed library can’t be found, the connection fails with a FATAL. Nothing validates the name when you set it. ALTER ROLE developer SET local_preload_libraries = 'auto_explian' is accepted without comment, and developer discovers the typo at their next login; a superuser has to ALTER ROLE developer RESET local_preload_libraries to let them back in. Put the typo in postgresql.conf and nobody can connect, superusers included. Since the context isn’t postmaster, fixing the file and reloading is enough; no restart. You can’t get a session to run pg_reload_conf() for you at that point, so it’s pg_ctl reload from the shell. On 18, pg_get_loaded_modules() will tell you what actually loaded into your session (the file_name column is the useful one); before 18 there is no SQL-level answer and you’re reading /proc/<pid>/maps.
If you are the DBA and you want a library in some sessions, this is not your parameter. Use session_preload_libraries with ALTER ROLE: it loads from $libdir without curating a directory, and superuser gating is the correct gate. The one job local_preload_libraries does that nothing else does is let roles opt themselves in, per connection, without a ticket. Symlink auto_explain.so into $libdir/plugins/, GRANT SET ON PARAMETER auto_explain.log_min_duration, auto_explain.log_analyze TO developers (the auto_explain.* parameters are superuser context, so without the grant they can load the library and then not configure it; GRANT SET needs 15 or later), and a developer can turn on plan logging for a single psql session with PGOPTIONS and nothing else. That is a real, if narrow, convenience. If you’re reaching for this parameter for any other reason, it’s usually because session_preload_libraries is superuser-only and you aren’t one, and that is the parameter working as designed.