PG-10 invoke an UDF report 'java.lang.SecurityException' read on /path/to/pljava-1.5.7.jar error

[ZhangHuiGui]

Background information

os-version: centos-7.0 with 4.14.105-19-0024
pg-version: pg-10
pljava-version: 1.5.7

jvm-options:

pljava.vmoptions = '-Djava.security.manager,-Djava.security.policy=/data/TEST/backup_env/jdk/jre/lib/security/java.policy'
pljava.classpath = '/data/TEST/backup_env/jdk/lib/pljava-1.5.7.jar'

java.policy, added the pljava-1.5.7.jar path into it.

grant codeBase "file:${{java.ext.dirs}}/*" {
        permission java.security.AllPermission;
};

grant {
  permission java.security.AllPermission;
};

// default permissions granted to all domains

grant {
        // Allows any thread to stop itself using the java.lang.Thread.stop()
        // method that takes no argument.
        // Note that this permission is granted by default only to remain
        // backwards compatible.
        // It is strongly recommended that you either remove this permission
        // from this policy file or further restrict it to code sources
        // that you specify, because Thread.stop() is potentially unsafe.
        // See the API specification of java.lang.Thread.stop() for more
        // information.
        permission java.lang.RuntimePermission "stopThread";

        // allows anyone to listen on dynamic ports
        permission java.net.SocketPermission "localhost:0", "listen";

        // "standard" properies that can be read by anyone

        permission java.util.PropertyPermission "java.version", "read";
        permission java.util.PropertyPermission "java.vendor", "read";
        permission java.util.PropertyPermission "java.vendor.url", "read";
        permission java.util.PropertyPermission "java.class.version", "read";
        permission java.util.PropertyPermission "os.name", "read";
        permission java.util.PropertyPermission "os.version", "read";
        permission java.util.PropertyPermission "os.arch", "read";
        permission java.util.PropertyPermission "file.separator", "read";
        permission java.util.PropertyPermission "path.separator", "read";
        permission java.util.PropertyPermission "line.separator", "read";

        permission java.util.PropertyPermission "java.specification.version", "read";
        permission java.util.PropertyPermission "java.specification.maintenance.version", "read";
        permission java.util.PropertyPermission "java.specification.vendor", "read";
        permission java.util.PropertyPermission "java.specification.name", "read";

        permission java.util.PropertyPermission "java.vm.specification.version", "read";
        permission java.util.PropertyPermission "java.vm.specification.vendor", "read";
        permission java.util.PropertyPermission "java.vm.specification.name", "read";
        permission java.util.PropertyPermission "java.vm.version", "read";
        permission java.util.PropertyPermission "java.vm.vendor", "read";
        permission java.util.PropertyPermission "java.vm.name", "read";

        permission java.util.PropertyPermission "sun.security.pkcs11.disableKeyExtraction", "read";
        permission java.io.FilePermission "/data/TEST/backup_env/jdk/lib/pljava-1.5.7.jar", "read";
};

Basic operation:

export CLASSPATH=/data/home/hugoozhang/source/TBase-V3.0/build/tdsqla/share/postgresql/pljava/:.:/data/home/hugoozhang/source/ft_local/jdk/lib/dt.jar:/data/home/hugoozhang/source/ft_local/jdk/lib/tools.jar
create extension pljava;
alter database postgres set pljava.libjvm_location='/data/home/hugoozhang/source/ft_local/jdk/jre/lib/amd64/server/libjvm.so';
select pg_reload_conf();
CREATE FUNCTION privacy_decrypt(VARCHAR)
    RETURNS VARCHAR IMMUTABLE
    AS 'com.hihonor.udf.PrivacyDecryption.decrypt'
LANGUAGE JAVA;
select sqlj.install_jar('file:/path/to/test_udf-1.0.jar', `test_udf', true);
select sqlj.set_classpath('public', 'test_udf');

show pljava.libjvm_location;
show pljava.classpath;

# udf in a independent jar
select public.privacy_decrypt('bj1#cn#408b48edd19dfc417305153b5ee4be8f');
ERROR:  (XX000) java.lang.SecurityException: read on /data/tbase/backup_env/jdk/lib/pljava-1.5.7.jar

Debug log

2024-11-26 14:14:39.461 CST,"NC8441_465_0_0_0_0","TEST","datalake",312425,"coord(312425,465)","9.0.23.5:61858",6745674c.4c469,"coord(312425,465)","coord(0,0)",44,"SELECT",2024-11-26 14:14:36 CST,21/465,0,DEBUG,00000,"find_in_dynamic_libpath: trying ""/data/TEST/user_1/tdata_00/cdwpg-qooqmt1n/3.16.9.1/install/TEST_pgxz/lib/postgresql/libpljava-so-1.5.7""",,,,,,,,,"psql"
2024-11-26 14:14:39.461 CST,"NC8441_465_0_0_0_0","TEST","datalake",312425,"coord(312425,465)","9.0.23.5:61858",6745674c.4c469,"coord(312425,465)","coord(0,0)",45,"SELECT",2024-11-26 14:14:36 CST,21/465,0,DEBUG,00000,"find_in_dynamic_libpath: trying ""/data/TEST/user_1/tdata_00/cdwpg-qooqmt1n/3.16.9.1/install/TEST_pgxz/lib/postgresql/libpljava-so-1.5.7.so""",,,,,,,,,"psql"
2024-11-26 14:14:39.471 CST,"NC8441_465_0_0_0_0","TEST","datalake",312425,"coord(312425,465)","9.0.23.5:61858",6745674c.4c469,"coord(312425,465)","coord(0,0)",46,"SELECT",2024-11-26 14:14:36 CST,21/465,0,DEBUG,00000,"Added JVM option string ""-Djava.security.manager,-Djava.security.policy=/data/TEST/backup_env/jdk/jre/lib/security/java.policy""",,,,,,,,,"psql"
2024-11-26 14:14:39.471 CST,"NC8441_465_0_0_0_0","TEST","datalake",312425,"coord(312425,465)","9.0.23.5:61858",6745674c.4c469,"coord(312425,465)","coord(0,0)",47,"SELECT",2024-11-26 14:14:36 CST,21/465,0,DEBUG,00000,"Added JVM option string ""-Dvisualvm.display.name=PL/Java:312425:datalake""",,,,,,,,,"psql"
2024-11-26 14:14:39.471 CST,"NC8441_465_0_0_0_0","TEST","datalake",312425,"coord(312425,465)","9.0.23.5:61858",6745674c.4c469,"coord(312425,465)","coord(0,0)",48,"SELECT",2024-11-26 14:14:36 CST,21/465,0,DEBUG,00000,"Added JVM option string ""vfprintf""",,,,,,,,,"psql"
2024-11-26 14:14:39.471 CST,"NC8441_465_0_0_0_0","TEST","datalake",312425,"coord(312425,465)","9.0.23.5:61858",6745674c.4c469,"coord(312425,465)","coord(0,0)",49,"SELECT",2024-11-26 14:14:36 CST,21/465,0,DEBUG,00000,"Added JVM option string ""-Xrs""",,,,,,,,,"psql"

2024-11-26 14:14:39.472 CST,"NC8441_465_0_0_0_0","TEST","datalake",312425,"coord(312425,465)","9.0.23.5:61858",6745674c.4c469,"coord(312425,465)","coord(0,0)",51,"SELECT",2024-11-26 14:14:36 CST,21/465,0,DEBUG,00000,"creating Java virtual machine",,,,,,,,,"psql"
2024-11-26 14:14:39.516 CST,"NC8441_465_0_0_0_0","TEST","datalake",312425,"coord(312425,465)","9.0.23.5:61858",6745674c.4c469,"coord(312425,465)","coord(0,0)",52,"SELECT",2024-11-26 14:14:36 CST,21/465,0,DEBUG,00000,"successfully created Java virtual machine",,,,,,,,,"psql"
2024-11-26 14:14:39.516 CST,"NC8441_465_0_0_0_0","TEST","datalake",312425,"coord(312425,465)","9.0.23.5:61858",6745674c.4c469,"coord(312425,465)","coord(0,0)",53,"SELECT",2024-11-26 14:14:36 CST,21/465,0,DEBUG,00000,"checking for a PL/Java Backend class on the given classpath",,,,,,,,,"psql"
2024-11-26 14:14:39.526 CST,"NC8441_465_0_0_0_0","TEST","datalake",312425,"coord(312425,465)","9.0.23.5:61858",6745674c.4c469,"coord(312425,465)","coord(0,0)",54,"SELECT",2024-11-26 14:14:36 CST,21/465,0,DEBUG,00000,"successfully loaded Backend class",,,,,,,,,"psql"
2024-11-26 14:14:39.746 CST,"NC8441_465_0_0_0_0","TEST","datalake",312425,"coord(312425,465)","9.0.23.5:61858",6745674c.4c469,"coord(312425,465)","coord(0,0)",55,"SELECT",2024-11-26 14:14:36 CST,21/465,0,DEBUG,00000,"PL/Java loaded","versions:
PL/Java native code (1.5.7)
PL/Java common code (1.5.7)
Built for (PostgreSQL 10.0 @ TEST_v3.16.9.1 (commit: c17de220c) 2024-11-15 09:03:25 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.0, 64-bit)
Loaded in (PostgreSQL 10.0 @ TEST_v3.16.9.1 (commit: c17de220c) 2024-11-15 09:03:25 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.0, 64-bit)
OpenJDK Runtime Environment (1.8.0_352-b1)
OpenJDK 64-Bit Server VM (25.352-b1, mixed mode, sharing)",,,,,,,,"psql"
2024-11-26 14:14:39.752 CST,"NC8441_465_0_0_0_0","TEST","datalake",312425,"coord(312425,465)","9.0.23.5:61858",6745674c.4c469,"coord(312425,465)","coord(0,0)",56,"SELECT",2024-11-26 14:14:36 CST,21/465,0,DEBUG,00000,"26 十一月 24 14:14:39 org.postgresql.pljava.internal.Backend Using SecurityManager for trusted language",,,,,,,,,"psql"
2024-11-26 14:14:39.762 CST,"NC8441_465_0_0_0_0","TEST","datalake",312425,"coord(312425,465)","9.0.23.5:61858",6745674c.4c469,"coord(312425,465)","coord(0,0)",57,"SELECT",2024-11-26 14:14:36 CST,21/465,0,DEBUG,00000,"className = 'com.hihonor.udf.PrivacyDecryption', methodName = 'decrypt', parameters = 'null', returnType = 'null'",,,,,,,,,"psql"
2024-11-26 14:14:39.762 CST,"NC8441_465_0_0_0_0","TEST","datalake",312425,"coord(312425,465)","9.0.23.5:61858",6745674c.4c469,"coord(312425,465)","coord(0,0)",58,"SELECT",2024-11-26 14:14:36 CST,21/465,0,DEBUG,00000,"26 十一月 24 14:14:39 org.postgresql.pljava.sqlj.Loader Creating typeMappings for schema public",,,,,,,,,"psql"
2024-11-26 14:14:39.841 CST,"NC8441_465_0_0_0_0","TEST","datalake",312425,"coord(312425,465)","9.0.23.5:61858",6745674c.4c469,"coord(312425,465)","coord(0,0)",6669,"SELECT",2024-11-26 14:14:36 CST,21/465,0,DEBUG,00000,"Loading class com.hihonor.udf.PrivacyDecryption",,,,,,,,,"psql"
2024-11-26 14:14:39.844 CST,"NC8441_465_0_0_0_0","TEST","datalake",312425,"coord(312425,465)","9.0.23.5:61858",6745674c.4c469,"coord(312425,465)","coord(0,0)",6682,"SELECT",2024-11-26 14:14:36 CST,21/465,0,DEBUG,00000,"Obtaining method com.hihonor.udf.PrivacyDecryption.decrypt (Ljava/lang/String;)Ljava/lang/String;",,,,,,,,,"psql"

2024-11-26 14:14:39.858 CST,"NC8441_465_0_0_0_0","TEST","datalake",312425,"coord(312425,465)","9.0.23.5:61858",6745674c.4c469,"coord(312425,465)","coord(0,0)",6708,"SELECT",2024-11-26 14:14:36 CST,21/465,0,LOG,00000,"An error occurred while calling the function, msg:java.lang.SecurityException: read on /data/TEST/backup_env/jdk/lib/pljava-1.5.7.jar",,,,,,"select public.privacy_decrypt('bj1#cn#408b48edd19dfc417305153b5ee4be8f');",,,"psql"

It seems to be a problem caused by environment configuration, but I don’t know much about the Java-related system. Any feasible suggestions?

Best wishes.

[jcflack]

The key difficulty here will be the attempt to use the standard Java security manager and policy-based permissions with PL/Java 1.5.x, which used an incompatible custom security manager. I am not sure whether you could, with some amount of effort, make it do something you want, but the combination is unsupported.

Is there a reason you cannot use the current PL/Java 1.6.x? It uses policy-based permissions natively.

PL/Java 1.6 will require using Java 9 or later. Would that pose a difficulty for you?

[jcflack]

For convenience, I'll add a link to PL/Java 1.6.x's docs regarding policy-based permissions.

https://tada.github.io/pljava/use/policy.html

For the legacy PL/Java 1.5.x series, this is 'wontfix'.

[ZhangHuiGui]

Thank you very much for your suggestion. The local pljava version is indeed a bit low, and I am going to upgrade it to 1.6x.

In addition, there is a debugging problem.

How to set the parameters related to pljava to obtain the java error stack when an exception occurs?
At present, it is found that only the error stack information can be printed to the log, but it seems to be printed through elog, and many symbol information is incomplete.

Stack trace:
1    0x1190a7e postgres errstart + 0x55e
2    0x7ff3462a8b14 libpljava-so-1.5.7.so <symbol not found> + 0x462a8b14
3    0x7ff3462a8d18 libpljava-so-1.5.7.so <symbol not found> + 0x462a8d18
4    0x7ff3462aa61f libpljava-so-1.5.7.so JNI_callStaticObjectMethodA + 0xbe
5    0x7ff3462b6b15 libpljava-so-1.5.7.so _Type_invoke + 0x33
6    0x7ff3462b6258 libpljava-so-1.5.7.so Type_invoke + 0x3d
7    0x7ff3462a5a21 libpljava-so-1.5.7.so Function_invoke + 0x259
8    0x7ff34629e60a libpljava-so-1.5.7.so <symbol not found> + 0x4629e60a
9    0x7ff34629e4a5 libpljava-so-1.5.7.so java_call_handler + 0x1d
10   0x870570 postgres <symbol not found> + 0x870570
11   0x869391 postgres ExecInterpExprStillValid + 0x31
12   0x90013e postgres <symbol not found> + 0x90013e
13   0x88a6ab postgres standard_ExecutorRun + 0x22b
14   0x7ff9bab614d5 pg_stat_statements.so <symbol not found> + 0xbab614d5
15   0x7ff9ba1355ce pg_stat_log.so <symbol not found> + 0xba1355ce
16   0x88ac64 postgres ExecutorRun + 0x54
17   0xce3495 postgres <symbol not found> + 0xce3495
18   0xce6747 postgres PortalRun + 0x397
19   0xcdcab9 postgres <symbol not found> + 0xcdcab9
20   0xce0d7a postgres PostgresMain + 0x335a
21   0x502e7c postgres <symbol not found> + 0x502e7c
22   0xb8446e postgres PostmasterMain + 0x122e
23   0x5081b6 postgres main + 0x3d6
24   0x7ff9c1cbb555 libc.so.6 __libc_start_main + 0xf5
25   0x5082cc postgres <symbol not found> + 0x5082cc

[jcflack]

Hmm, it does seem the trick to seeing exception stacktraces could be documented a bit better. It is mentioned in the wiki.

The wiki still says it only works to set log_min_messages to DEBUG1 or finer, but in the release notes for 1.5.1, it mentions that setting client_min_messages to such a level will also work. Still, the Java stacktrace only goes to the log (or to the backend's standard error channel, which goes to the log if logging_collector is on).