[PATCH] Allow bare library names for non-superuser LOAD

On Thu, 03 Sep 2026 at 20:11, "cca5507" <cca5507@qq.com> wrote:

Hi,

Non-superusers may use LOAD only for libraries located in
$libdir/plugins, but currently they must specify that directory
explicitly, for example:

LOAD '$libdir/plugins/mylib';

According to the documentation [1]https://www.postgresql.org/docs/current/sql-load.html, this is expected behavior.

Non-superusers can only apply LOAD to library files located in $libdir/plugins/ —
the specified filename must begin with exactly that string.

This differs from local_preload_libraries, which accepts a bare library
name and automatically prepends $libdir/plugins.

The attached patch moves this expansion from load_libraries() into
load_file(). As a result, a non-superuser can write:

LOAD 'mylib';

and have it interpreted as:

LOAD '$libdir/plugins/mylib';

Thoughts?

--
Regards,
ChangAo Chen

[1]: https://www.postgresql.org/docs/current/sql-load.html

--
Regards,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.

According to the documentation [1], this is expected behavior.

Non-superusers can only apply LOAD to library files located in $libdir/plugins/ —
        the specified filename must begin with exactly that string. 

Yes, I knew that this is the expected behavior, but it's not very convenient for
non-superusers, right? Therefore, I would like to propose a patch that allows
non-superusers to omit the $libdir/plugins/ prefix and let postgres to expand
it automatically.

--
Regards,
ChangAo Chen

On Thu, 03 Sep 2026 at 21:17, "cca5507" <cca5507@qq.com> wrote:

According to the documentation [1], this is expected behavior.

Non-superusers can only apply LOAD to library files located in $libdir/plugins/ —
        the specified filename must begin with exactly that string. 

Yes, I knew that this is the expected behavior, but it's not very convenient for
non-superusers, right?

Yeah, it's a bit inconvenient.

Therefore, I would like to propose a patch that allows
non-superusers to omit the $libdir/plugins/ prefix and let postgres to expand
it automatically.

The $libdir/plugins directory was introduced in commit abc3120e9b2. It
automatically expands for local_shared_libraries, but not for LOAD command.
I'm not sure if that was intentional.

Other than that, it looks good.

--
Regards,
ChangAo Chen

--
Regards,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.

Hi all
I reviewed and tested the v1 patch for allowing bare library names for
non-superuser LOAD.
I was able to reproduce the existing behavior before applying the patch:
-As a non-superuser, LOAD mylib failed with access to library mylib is
not allowed.
-LOAD $libdir/plugins/mylib succeeded.

After applying the v1 patch and rebuilding/restarting PostgreSQL:
-LOAD mylib succeeded and loaded the library from $libdir/plugins.
-LOAD $libdir/plugins/mylib continued to work.
-Attempts to load /tmp/mylib.so, $libdir/mylib, and ../mylib were
still rejected for the non-superuser.
I also ran git diff --check, which completed without any errors.

From my testing, the patch fixes the reported issue while preserving
the existing security restriction for non-superuser LOAD.
I did not find any functional issue with the patch during these tests.

Regards
solai

Hi,
Thanks for the patch.

I have two quick observations:

== LOAD semantics ==

LOAD now means different things depending on which user runs it, which
can be quite problematic for scripting. For instance in the following
scenario, where we have two modules with the same name living in
different directories:

$ LIBDIR=$(pg_config --pkglibdir)
$ cp $LIBDIR/passwordcheck.so $LIBDIR/demo.so
$ cp $LIBDIR/auto_explain.so $LIBDIR/plugins/demo.so

-- a superuser loads the lib from the root directory:
postgres=# LOAD 'demo';
LOAD
postgres=# SELECT count(*) FROM pg_settings WHERE name LIKE 'auto_explain%';
count
-------
0
(1 row)

-- a normal user loads it from the plugins directory, although the
command is identical

postgres=# CREATE USER foo;
CREATE ROLE
postgres=# SET ROLE foo;
SET
postgres=> LOAD 'demo';
LOAD
postgres=> SELECT count(*) FROM pg_settings WHERE name LIKE 'auto_explain%';
count
-------
15
(1 row)

Without the patch we get an ERROR, which is arguably better than "the
wrong file":

postgres=> LOAD 'demo';
ERROR: access to library "demo" is not allowed

== error message ==

The error message now says something else (different sqlstates?):

postgres=> LOAD 'pg_stat_statements';
ERROR: could not access file "$libdir/plugins/pg_stat_statements": No
such file or directory

Without the patch:

postgres=> LOAD 'pg_stat_statements';
ERROR: access to library "pg_stat_statements" is not allowed

I believe that this kind of error reclassification might confuse
monitoring tools -- IMO not a blocker, but must be well justified.

Best, Jim

I have two quick observations:

== LOAD semantics ==

LOAD now means different things depending on which user runs it, which
can be quite problematic for scripting. For instance in the following
scenario, where we have two modules with the same name living in
different directories:

$ LIBDIR=$(pg_config --pkglibdir)
$ cp $LIBDIR/passwordcheck.so $LIBDIR/demo.so
$ cp $LIBDIR/auto_explain.so  $LIBDIR/plugins/demo.so

I think we just need to avoid doing something like this: different libraries
with the same name.

== error message ==

The error message now says something else (different sqlstates?):

postgres=> LOAD 'pg_stat_statements';
ERROR:  could not access file "$libdir/plugins/pg_stat_statements": No
such file or directory

Without the patch:

postgres=> LOAD 'pg_stat_statements';
ERROR:  access to library "pg_stat_statements" is not allowed

I believe that this kind of error reclassification might confuse
monitoring tools -- IMO not a blocker, but must be well justified.

The behavior of "local_preload_libraries" also like this. So I think it's
ok if we document the auto expand of "$libdir/plugins/" clearly.

--
Regards,
ChangAo Chen

On 09/09/2026 10:04, cca5507 wrote:

I think we just need to avoid doing something like this: different libraries
with the same name.

As a general rule I totally agree with you. I just wonder if it should
be possible in the first place. My point is that we could be trading
server guarantee for admin discipline.

The behavior of "local_preload_libraries" also like this. So I think it's
ok if we document the auto expand of "$libdir/plugins/" clearly.

Fair enough.

Thanks!

Best, Jim