Re: postgresql jndi datasource with certificate authentication?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Felix, On 10/26/19 16:37, Felix Schumacher wrote: > > Am 22.10.19 um 20:07 schrieb Magosányi Árpád: >> Thank you all for the suggestions. >> >> Based on the documentation, my setup should work: The server >> certificate is already processed and accepted (I know that >> because I could not get it right at the first try). The driver is >> supposed to work with a PEM certificate and a pkcs-8 DER encoded >> key, and those what I supply to it. > > Is your key password protected? Have you tried to remove the > password? This was essentially resolved. The OP just didn't circle back to us. If you look at the PR, it turns out that openssl changed behavior and used a different encryption algorithm to encrypt the private key. The pgsql JDBC driver has limited support for reading keys. So basically, you have to make sure that openssl uses the deprecated encryption strategy. I'm looking at maybe providing a patch to their project, if only to allow them to read something other than a binary DER key file. Yuck. It's the only product I've ever seen that can ONLY take a binary file and not e.g. PEM, keystore, whatever. Weird that they have a Java product that can't use a Java keystore for its keys. - -chris >> The problem seems to be that the java installation (openjdk-11) >> does not have a cryptographic security provider understanding a >> specific oid. What I understand is that BouncyCastle have that >> security provider, and I should be able to configure it somewhere >> either in the java setup or tomcat. I have already tried in the >> java setup, but the documented way did not seem to work. I have >> no idea how to configure it in Tomcat datasource, this is why I >> have asked here. The other reason is to see whether anyone have a >> similar setup: if so, then someone already dealt with same >> problem, and I should like to see how. >> >> It's true that it seems to be a pgjdbc related problem: it does >> not work with directly jdbc calls. I am trying to get help from >> the jdbc guys, this is why I have an open issue there: >> >> https://github.com/pgjdbc/pgjdbc/issues/1585 >> >> >> On 10/22/19 6:10 PM, Christopher Schultz wrote: >>> Arpad, >>> >>> On 10/22/19 12:19, logo wrote: I have the following in context.xml: >>> type="javax.sql.DataSource" driverClassName="org.postgresql.Driver" url="jdbc:postgresql://infra.kodekonveyor.com:5432/users?ssl=tr uesslmode=verify-ca" username="market" maxTotal="20" maxIdle="10" maxWaitMillis="-1"/> I have this in ~tomcat/.postgresql: root@market:/var/lib/tomcat9/.postgresql# ls -lL total 11 -rw-r--r-- 1 root root 4597 Oct 21 12:49 postgresql.crt -r 1 tomcat root 1329 Oct 21 17:40 postgresql.pk8 -rw-r--r-- 1 root root 1493 Oct 21 12:49 root.crt >>> The documentation for the driver[1] is a little unclear, but it >>> seems that you can indeed specify the location of the client >>> certificate using sslcert=/path/to/cert and sslkey=/path/to/key >>> connection parameters. Their defaults are >>> ${user.home}/.postgresql/postgresql.crt and >>> ${user.home}/.postgresql/postgresql.pk8 (and >>> ${user.home}/.postgresql/root.crt for the root certificate). >>> >>> So I think those settings should be working. >>> >>> Under the notes in [1], it says: >>> >>> " If you are using Java's default mechanism (not LibPQFactory) >>> to create the SSL connection you will need to make the server >>> certificate available to Java, the first step is to convert it >>> to a form Java understands. " >>> >>> I'm not sure what LibPQFactory is, but you may have to convert >>> to PKCS12/JKS and use their process to use those certificates. >>> >>> The documentation suggests that you will need to start your JVM >>> with specific system properties to make your connection. IMO >>> this is a terrible bug because it means you can't configure >>> these things on a per-connection basis. The documentation is >>> also incomplete because they only tell you how to configure a >>> trust store (to trust the server) and not how to configure the >>> key store (which contains your client certificate). The correct >>> system properties to use for a key store are: >>> >>> javax.net.ssl.keyStore (path to keystore) >>> javax.net.ssl.keyStorePassword (password for keystore) >>> javax.net.ssl.keyStoreType (type of keystore, PKCS12, JCEKS, >>> JKS, etc.) >>> >>> At this point, all of your questions should be directed to the >>> PostgreSQL community since it's the driver you are having >>> trouble configuring. It appears that Tomcat is working as >>> expected and you just need help with the driver configuration. >>> >>> Hope that helps, -chris >>> >>> [1] >>> https://jdbc.postgresql.org/documentation/head/ssl-client.html >>> >>>
Re: postgresql jndi datasource with certificate authentication?
Am 22.10.19 um 20:07 schrieb Magosányi Árpád: > Thank you all for the suggestions. > > Based on the documentation, my setup should work: The server certificate > is already processed and accepted (I know that because I could not get > it right at the first try). The driver is supposed to work with a PEM > certificate and a pkcs-8 DER encoded key, and those what I supply to it. Is your key password protected? Have you tried to remove the password? Felix > > The problem seems to be that the java installation (openjdk-11) does not > have a cryptographic security provider understanding a specific oid. > What I understand is that BouncyCastle have that security provider, and > I should be able to configure it somewhere either in the java setup or > tomcat. > I have already tried in the java setup, but the documented way did not > seem to work. > I have no idea how to configure it in Tomcat datasource, this is why I > have asked here. > The other reason is to see whether anyone have a similar setup: if so, > then someone already dealt with same problem, and I should like to see how. > > It's true that it seems to be a pgjdbc related problem: it does not work > with directly jdbc calls. I am trying to get help from the jdbc guys, > this is why I have an open issue there: > > https://github.com/pgjdbc/pgjdbc/issues/1585 > > > On 10/22/19 6:10 PM, Christopher Schultz wrote: >> Arpad, >> >> On 10/22/19 12:19, logo wrote: >>> I have the following in context.xml: >>> >>> >> type="javax.sql.DataSource" >>> driverClassName="org.postgresql.Driver" >>> url="jdbc:postgresql://infra.kodekonveyor.com:5432/users?ssl=truesslmode=verify-ca" >>> >>> >>> username="market" maxTotal="20" maxIdle="10" >>> maxWaitMillis="-1"/> >>> >>> I have this in ~tomcat/.postgresql: >>> >>> root@market:/var/lib/tomcat9/.postgresql# ls -lL >>> total 11 >>> -rw-r--r-- 1 root root 4597 Oct 21 12:49 postgresql.crt >>> -r 1 tomcat root 1329 Oct 21 17:40 postgresql.pk8 >>> -rw-r--r-- 1 root root 1493 Oct 21 12:49 root.crt >> The documentation for the driver[1] is a little unclear, but it seems >> that you can indeed specify the location of the client certificate >> using sslcert=/path/to/cert and sslkey=/path/to/key connection >> parameters. Their defaults are ${user.home}/.postgresql/postgresql.crt >> and ${user.home}/.postgresql/postgresql.pk8 (and >> ${user.home}/.postgresql/root.crt for the root certificate). >> >> So I think those settings should be working. >> >> Under the notes in [1], it says: >> >> " >> If you are using Java's default mechanism (not LibPQFactory) to create >> the SSL connection you will need to make the server certificate >> available to Java, the first step is to convert it to a form Java >> understands. >> " >> >> I'm not sure what LibPQFactory is, but you may have to convert to >> PKCS12/JKS and use their process to use those certificates. >> >> The documentation suggests that you will need to start your JVM with >> specific system properties to make your connection. IMO this is a >> terrible bug because it means you can't configure these things on a >> per-connection basis. The documentation is also incomplete because >> they only tell you how to configure a trust store (to trust the >> server) and not how to configure the key store (which contains your >> client certificate). The correct system properties to use for a key >> store are: >> >> javax.net.ssl.keyStore (path to keystore) >> javax.net.ssl.keyStorePassword (password for keystore) >> javax.net.ssl.keyStoreType (type of keystore, PKCS12, JCEKS, JKS, etc.) >> >> At this point, all of your questions should be directed to the >> PostgreSQL community since it's the driver you are having trouble >> configuring. It appears that Tomcat is working as expected and you >> just need help with the driver configuration. >> >> Hope that helps, >> -chris >> >> [1] https://jdbc.postgresql.org/documentation/head/ssl-client.html >> >> - >> To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org >> For additional commands, e-mail: users-h...@tomcat.apache.org >> > > - > To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org > For additional commands, e-mail: users-h...@tomcat.apache.org > - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: postgresql jndi datasource with certificate authentication?
Thank you all for the suggestions. Based on the documentation, my setup should work: The server certificate is already processed and accepted (I know that because I could not get it right at the first try). The driver is supposed to work with a PEM certificate and a pkcs-8 DER encoded key, and those what I supply to it. The problem seems to be that the java installation (openjdk-11) does not have a cryptographic security provider understanding a specific oid. What I understand is that BouncyCastle have that security provider, and I should be able to configure it somewhere either in the java setup or tomcat. I have already tried in the java setup, but the documented way did not seem to work. I have no idea how to configure it in Tomcat datasource, this is why I have asked here. The other reason is to see whether anyone have a similar setup: if so, then someone already dealt with same problem, and I should like to see how. It's true that it seems to be a pgjdbc related problem: it does not work with directly jdbc calls. I am trying to get help from the jdbc guys, this is why I have an open issue there: https://github.com/pgjdbc/pgjdbc/issues/1585 On 10/22/19 6:10 PM, Christopher Schultz wrote: > Arpad, > > On 10/22/19 12:19, logo wrote: >> I have the following in context.xml: >> >> > type="javax.sql.DataSource" >> driverClassName="org.postgresql.Driver" >> url="jdbc:postgresql://infra.kodekonveyor.com:5432/users?ssl=truesslmode=verify-ca" >> >> >> username="market" maxTotal="20" maxIdle="10" >> maxWaitMillis="-1"/> >> >> I have this in ~tomcat/.postgresql: >> >> root@market:/var/lib/tomcat9/.postgresql# ls -lL >> total 11 >> -rw-r--r-- 1 root root 4597 Oct 21 12:49 postgresql.crt >> -r 1 tomcat root 1329 Oct 21 17:40 postgresql.pk8 >> -rw-r--r-- 1 root root 1493 Oct 21 12:49 root.crt > > The documentation for the driver[1] is a little unclear, but it seems > that you can indeed specify the location of the client certificate > using sslcert=/path/to/cert and sslkey=/path/to/key connection > parameters. Their defaults are ${user.home}/.postgresql/postgresql.crt > and ${user.home}/.postgresql/postgresql.pk8 (and > ${user.home}/.postgresql/root.crt for the root certificate). > > So I think those settings should be working. > > Under the notes in [1], it says: > > " > If you are using Java's default mechanism (not LibPQFactory) to create > the SSL connection you will need to make the server certificate > available to Java, the first step is to convert it to a form Java > understands. > " > > I'm not sure what LibPQFactory is, but you may have to convert to > PKCS12/JKS and use their process to use those certificates. > > The documentation suggests that you will need to start your JVM with > specific system properties to make your connection. IMO this is a > terrible bug because it means you can't configure these things on a > per-connection basis. The documentation is also incomplete because > they only tell you how to configure a trust store (to trust the > server) and not how to configure the key store (which contains your > client certificate). The correct system properties to use for a key > store are: > > javax.net.ssl.keyStore (path to keystore) > javax.net.ssl.keyStorePassword (password for keystore) > javax.net.ssl.keyStoreType (type of keystore, PKCS12, JCEKS, JKS, etc.) > > At this point, all of your questions should be directed to the > PostgreSQL community since it's the driver you are having trouble > configuring. It appears that Tomcat is working as expected and you > just need help with the driver configuration. > > Hope that helps, > -chris > > [1] https://jdbc.postgresql.org/documentation/head/ssl-client.html > > - > To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org > For additional commands, e-mail: users-h...@tomcat.apache.org > - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: postgresql jndi datasource with certificate authentication?
Arpad, On 10/22/19 12:19, logo wrote: I have the following in context.xml: url="jdbc:postgresql://infra.kodekonveyor.com:5432/users?ssl=truesslmode=verify-ca" username="market" maxTotal="20" maxIdle="10" maxWaitMillis="-1"/> I have this in ~tomcat/.postgresql: root@market:/var/lib/tomcat9/.postgresql# ls -lL total 11 -rw-r--r-- 1 root root 4597 Oct 21 12:49 postgresql.crt -r 1 tomcat root 1329 Oct 21 17:40 postgresql.pk8 -rw-r--r-- 1 root root 1493 Oct 21 12:49 root.crt The documentation for the driver[1] is a little unclear, but it seems that you can indeed specify the location of the client certificate using sslcert=/path/to/cert and sslkey=/path/to/key connection parameters. Their defaults are ${user.home}/.postgresql/postgresql.crt and ${user.home}/.postgresql/postgresql.pk8 (and ${user.home}/.postgresql/root.crt for the root certificate). So I think those settings should be working. Under the notes in [1], it says: " If you are using Java's default mechanism (not LibPQFactory) to create the SSL connection you will need to make the server certificate available to Java, the first step is to convert it to a form Java understands. " I'm not sure what LibPQFactory is, but you may have to convert to PKCS12/JKS and use their process to use those certificates. The documentation suggests that you will need to start your JVM with specific system properties to make your connection. IMO this is a terrible bug because it means you can't configure these things on a per-connection basis. The documentation is also incomplete because they only tell you how to configure a trust store (to trust the server) and not how to configure the key store (which contains your client certificate). The correct system properties to use for a key store are: javax.net.ssl.keyStore (path to keystore) javax.net.ssl.keyStorePassword (password for keystore) javax.net.ssl.keyStoreType (type of keystore, PKCS12, JCEKS, JKS, etc.) At this point, all of your questions should be directed to the PostgreSQL community since it's the driver you are having trouble configuring. It appears that Tomcat is working as expected and you just need help with the driver configuration. Hope that helps, -chris [1] https://jdbc.postgresql.org/documentation/head/ssl-client.html - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: postgresql jndi datasource with certificate authentication?
Arpad and Chris, Am 2019-10-22 18:07, schrieb Christopher Schultz: Magosányi, On 10/22/19 11:09, Magosányi Árpád wrote: As I understand you are suggesting to use direct connection to the database. The servlet needs a JNDI datasource, and the question is about configuring that datasource in tomcat. As I can modify the servlet, I could choose to set up hibernate in other ways, but I would like to separate the concerns of providing the database and using it between the operations staff and development. And JNDI is exactly for that. I think Peter was suggesting that you change your connection URL and the format of your keystore. +1 PKCS#8 is not a recognized format for Java keystores; you'll need to use JKS or PKCS#12 (recommended, as JKS is being deprecated). I was looking for the right type name. Thanks Chris. Peter -chris On 10/22/19 2:27 PM, logo wrote: Arpad, have you tried sth like this: StringBuffer sb = new StringBuffer("jdbc:postgresql://infra.kodekonveyor.com:5432/users?useSSL=true=true=UTF-8&"); sb.append("user=market=&"); sb.append("clientCertificateKeyStoreUrl=file:var/lib/tomcat9/.postgresql/client.jks&"); sb.append("clientCertificateKeyStorePassword=changeit"); Connection c = DriverManager.getConnection(sb.toString()); and convert the pem certificate to JKS/P12 ? I have this working in mysql... Peter Am 2019-10-22 12:56, schrieb Magosányi Árpád: Hi! Anyone have a postgresql jndi datasource with certificate authentication working? I have the following in context.xml: url="jdbc:postgresql://infra.kodekonveyor.com:5432/users?ssl=truesslmode=verify-ca" username="market" maxTotal="20" maxIdle="10" maxWaitMillis="-1"/> I have this in ~tomcat/.postgresql: root@market:/var/lib/tomcat9/.postgresql# ls -lL total 11 -rw-r--r-- 1 root root 4597 Oct 21 12:49 postgresql.crt -r 1 tomcat root 1329 Oct 21 17:40 postgresql.pk8 -rw-r--r-- 1 root root 1493 Oct 21 12:49 root.crt where the crt file is a pem client certificate, root.crt is the ca cert, and pk8 is the client key in der pkcs-8 format. The logs: NOTE: Picked up JDK_JAVA_OPTIONS: --add-opens=java.base/java.lang=ALL-UNNAMED --add-opens=java.base/java.io=ALL-UNNAMED --add-opens=java.rmi/sun.rmi.transport=ALL-UNNAMED Server version name: Apache Tomcat/9.0.16 (Ubuntu) Server built: Sep 11 2019 19:47:51 UTC Server version number: 9.0.16.0 OS Name: Linux OS Version:4.15.0-65-generic Architecture: amd64 Java Home: /usr/lib/jvm/java-11-openjdk-amd64 JVM Version: 11.0.4+11-post-Ubuntu-1ubuntu218.04.3 JVM Vendor:Ubuntu CATALINA_BASE: /var/lib/tomcat9 CATALINA_HOME: /usr/share/tomcat9 Command line argument: --add-opens=java.base/java.lang=ALL-UNNAMED Command line argument: --add-opens=java.base/java.io=ALL-UNNAMED Command line argument: --add-opens=java.rmi/sun.rmi.transport=ALL-UNNAMED Command line argument: -Djava.util.logging.config.file=/var/lib/tomcat9/conf/logging.properties Command line argument: -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager Command line argument: -Djava.awt.headless=true Command line argument: -XX:+UseG1GC Command line argument: -Djdk.tls.ephemeralDHKeySize=2048 Command line argument: -Djava.protocol.handler.pkgs=org.apache.catalina.webresources Command line argument: -Dorg.apache.catalina.security.SecurityListener.UMASK=0027 Command line argument: -Dignore.endorsed.dirs= Command line argument: -Dcatalina.base=/var/lib/tomcat9 Command line argument: -Dcatalina.home=/usr/share/tomcat9 Command line argument: -Djava.io.tmpdir=/tmp Loaded APR based Apache Tomcat Native library [1.2.21] using APR version [1.6.3]. APR capabilities: IPv6 [true], sendfile [true], accept filters [false], random [true]. APR/OpenSSL configuration: useAprConnector [false], useOpenSSL [true] OpenSSL successfully initialized [OpenSSL 1.1.1 11 Sep 2018] Initializing ProtocolHandler ["ajp-nio-8009"] Server initialization in [1,859] milliseconds Starting service [Catalina] Starting Servlet engine: [Apache Tomcat/9.0.16 (Ubuntu)] Deploying web application archive [/var/lib/tomcat9/webapps/market.war] SLF4J: Class path contains multiple SLF4J bindings. SLF4J: Found binding in [jar:file:/var/lib/tomcat9/webapps/market/WEB-INF/lib/slf4j-simple-1.7.24.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/var/lib/tomcat9/webapps/market/WEB-INF/lib/logback-classic-1.2.3.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J:
Re: postgresql jndi datasource with certificate authentication?
Magosányi, On 10/22/19 11:09, Magosányi Árpád wrote: As I understand you are suggesting to use direct connection to the database. The servlet needs a JNDI datasource, and the question is about configuring that datasource in tomcat. As I can modify the servlet, I could choose to set up hibernate in other ways, but I would like to separate the concerns of providing the database and using it between the operations staff and development. And JNDI is exactly for that. I think Peter was suggesting that you change your connection URL and the format of your keystore. PKCS#8 is not a recognized format for Java keystores; you'll need to use JKS or PKCS#12 (recommended, as JKS is being deprecated). -chris On 10/22/19 2:27 PM, logo wrote: Arpad, have you tried sth like this: StringBuffer sb = new StringBuffer("jdbc:postgresql://infra.kodekonveyor.com:5432/users?useSSL=true=true=UTF-8&"); sb.append("user=market=&"); sb.append("clientCertificateKeyStoreUrl=file:var/lib/tomcat9/.postgresql/client.jks&"); sb.append("clientCertificateKeyStorePassword=changeit"); Connection c = DriverManager.getConnection(sb.toString()); and convert the pem certificate to JKS/P12 ? I have this working in mysql... Peter Am 2019-10-22 12:56, schrieb Magosányi Árpád: Hi! Anyone have a postgresql jndi datasource with certificate authentication working? I have the following in context.xml: url="jdbc:postgresql://infra.kodekonveyor.com:5432/users?ssl=truesslmode=verify-ca" username="market" maxTotal="20" maxIdle="10" maxWaitMillis="-1"/> I have this in ~tomcat/.postgresql: root@market:/var/lib/tomcat9/.postgresql# ls -lL total 11 -rw-r--r-- 1 root root 4597 Oct 21 12:49 postgresql.crt -r 1 tomcat root 1329 Oct 21 17:40 postgresql.pk8 -rw-r--r-- 1 root root 1493 Oct 21 12:49 root.crt where the crt file is a pem client certificate, root.crt is the ca cert, and pk8 is the client key in der pkcs-8 format. The logs: NOTE: Picked up JDK_JAVA_OPTIONS: --add-opens=java.base/java.lang=ALL-UNNAMED --add-opens=java.base/java.io=ALL-UNNAMED --add-opens=java.rmi/sun.rmi.transport=ALL-UNNAMED Server version name: Apache Tomcat/9.0.16 (Ubuntu) Server built: Sep 11 2019 19:47:51 UTC Server version number: 9.0.16.0 OS Name: Linux OS Version:4.15.0-65-generic Architecture: amd64 Java Home: /usr/lib/jvm/java-11-openjdk-amd64 JVM Version: 11.0.4+11-post-Ubuntu-1ubuntu218.04.3 JVM Vendor:Ubuntu CATALINA_BASE: /var/lib/tomcat9 CATALINA_HOME: /usr/share/tomcat9 Command line argument: --add-opens=java.base/java.lang=ALL-UNNAMED Command line argument: --add-opens=java.base/java.io=ALL-UNNAMED Command line argument: --add-opens=java.rmi/sun.rmi.transport=ALL-UNNAMED Command line argument: -Djava.util.logging.config.file=/var/lib/tomcat9/conf/logging.properties Command line argument: -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager Command line argument: -Djava.awt.headless=true Command line argument: -XX:+UseG1GC Command line argument: -Djdk.tls.ephemeralDHKeySize=2048 Command line argument: -Djava.protocol.handler.pkgs=org.apache.catalina.webresources Command line argument: -Dorg.apache.catalina.security.SecurityListener.UMASK=0027 Command line argument: -Dignore.endorsed.dirs= Command line argument: -Dcatalina.base=/var/lib/tomcat9 Command line argument: -Dcatalina.home=/usr/share/tomcat9 Command line argument: -Djava.io.tmpdir=/tmp Loaded APR based Apache Tomcat Native library [1.2.21] using APR version [1.6.3]. APR capabilities: IPv6 [true], sendfile [true], accept filters [false], random [true]. APR/OpenSSL configuration: useAprConnector [false], useOpenSSL [true] OpenSSL successfully initialized [OpenSSL 1.1.1 11 Sep 2018] Initializing ProtocolHandler ["ajp-nio-8009"] Server initialization in [1,859] milliseconds Starting service [Catalina] Starting Servlet engine: [Apache Tomcat/9.0.16 (Ubuntu)] Deploying web application archive [/var/lib/tomcat9/webapps/market.war] SLF4J: Class path contains multiple SLF4J bindings. SLF4J: Found binding in [jar:file:/var/lib/tomcat9/webapps/market/WEB-INF/lib/slf4j-simple-1.7.24.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/var/lib/tomcat9/webapps/market/WEB-INF/lib/logback-classic-1.2.3.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation. SLF4J: Actual binding is of type [org.slf4j.impl.SimpleLoggerFactory] 3 Spring WebAp
Re: postgresql jndi datasource with certificate authentication?
Peter, As I understand you are suggesting to use direct connection to the database. The servlet needs a JNDI datasource, and the question is about configuring that datasource in tomcat. As I can modify the servlet, I could choose to set up hibernate in other ways, but I would like to separate the concerns of providing the database and using it between the operations staff and development. And JNDI is exactly for that. On 10/22/19 2:27 PM, logo wrote: > Arpad, > > have you tried sth like this: > > StringBuffer sb = new > StringBuffer("jdbc:postgresql://infra.kodekonveyor.com:5432/users?useSSL=true=true=UTF-8&"); > sb.append("user=market=&"); > > sb.append("clientCertificateKeyStoreUrl=file:var/lib/tomcat9/.postgresql/client.jks&"); > sb.append("clientCertificateKeyStorePassword=changeit"); > > Connection c = DriverManager.getConnection(sb.toString()); > > and convert the pem certificate to JKS/P12 ? I have this working in > mysql... > > Peter > > Am 2019-10-22 12:56, schrieb Magosányi Árpád: >> Hi! >> >> Anyone have a postgresql jndi datasource with certificate authentication >> working? >> >> I have the following in context.xml: >> >> > type="javax.sql.DataSource" >> driverClassName="org.postgresql.Driver" >> >> url="jdbc:postgresql://infra.kodekonveyor.com:5432/users?ssl=truesslmode=verify-ca" >> >> username="market" maxTotal="20" maxIdle="10" >> maxWaitMillis="-1"/> >> >> I have this in ~tomcat/.postgresql: >> >> root@market:/var/lib/tomcat9/.postgresql# ls -lL >> total 11 >> -rw-r--r-- 1 root root 4597 Oct 21 12:49 postgresql.crt >> -r 1 tomcat root 1329 Oct 21 17:40 postgresql.pk8 >> -rw-r--r-- 1 root root 1493 Oct 21 12:49 root.crt >> >> where the crt file is a pem client certificate, root.crt is the ca cert, >> and pk8 is the client key in der pkcs-8 format. >> >> The logs: >> >> NOTE: Picked up JDK_JAVA_OPTIONS: >> --add-opens=java.base/java.lang=ALL-UNNAMED >> --add-opens=java.base/java.io=ALL-UNNAMED >> --add-opens=java.rmi/sun.rmi.transport=ALL-UNNAMED >> Server version name: Apache Tomcat/9.0.16 (Ubuntu) >> Server built: Sep 11 2019 19:47:51 UTC >> Server version number: 9.0.16.0 >> OS Name: Linux >> OS Version: 4.15.0-65-generic >> Architecture: amd64 >> Java Home: /usr/lib/jvm/java-11-openjdk-amd64 >> JVM Version: 11.0.4+11-post-Ubuntu-1ubuntu218.04.3 >> JVM Vendor: Ubuntu >> CATALINA_BASE: /var/lib/tomcat9 >> CATALINA_HOME: /usr/share/tomcat9 >> Command line argument: --add-opens=java.base/java.lang=ALL-UNNAMED >> Command line argument: --add-opens=java.base/java.io=ALL-UNNAMED >> Command line argument: >> --add-opens=java.rmi/sun.rmi.transport=ALL-UNNAMED >> Command line argument: >> -Djava.util.logging.config.file=/var/lib/tomcat9/conf/logging.properties >> Command line argument: >> -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager >> Command line argument: -Djava.awt.headless=true >> Command line argument: -XX:+UseG1GC >> Command line argument: -Djdk.tls.ephemeralDHKeySize=2048 >> Command line argument: >> -Djava.protocol.handler.pkgs=org.apache.catalina.webresources >> Command line argument: >> -Dorg.apache.catalina.security.SecurityListener.UMASK=0027 >> Command line argument: -Dignore.endorsed.dirs= >> Command line argument: -Dcatalina.base=/var/lib/tomcat9 >> Command line argument: -Dcatalina.home=/usr/share/tomcat9 >> Command line argument: -Djava.io.tmpdir=/tmp >> Loaded APR based Apache Tomcat Native library [1.2.21] using APR >> version [1.6.3]. >> APR capabilities: IPv6 [true], sendfile [true], accept filters >> [false], random [true]. >> APR/OpenSSL configuration: useAprConnector [false], useOpenSSL >> [true] >> OpenSSL successfully initialized [OpenSSL 1.1.1 11 Sep 2018] >> Initializing ProtocolHandler ["ajp-nio-8009"] >> Server initialization in [1,859] milliseconds >> Starting service [Catalina] >> Starting Servlet engine: [Apache Tomcat/9.0.16 (Ubuntu)] >> Deploying web application archive >> [/var/lib/tomcat9/webapps/market.war] >>
Re: postgresql jndi datasource with certificate authentication?
Arpad, have you tried sth like this: StringBuffer sb = new StringBuffer("jdbc:postgresql://infra.kodekonveyor.com:5432/users?useSSL=true=true=UTF-8&"); sb.append("user=market=&"); sb.append("clientCertificateKeyStoreUrl=file:var/lib/tomcat9/.postgresql/client.jks&"); sb.append("clientCertificateKeyStorePassword=changeit"); Connection c = DriverManager.getConnection(sb.toString()); and convert the pem certificate to JKS/P12 ? I have this working in mysql... Peter Am 2019-10-22 12:56, schrieb Magosányi Árpád: Hi! Anyone have a postgresql jndi datasource with certificate authentication working? I have the following in context.xml: I have this in ~tomcat/.postgresql: root@market:/var/lib/tomcat9/.postgresql# ls -lL total 11 -rw-r--r-- 1 root root 4597 Oct 21 12:49 postgresql.crt -r 1 tomcat root 1329 Oct 21 17:40 postgresql.pk8 -rw-r--r-- 1 root root 1493 Oct 21 12:49 root.crt where the crt file is a pem client certificate, root.crt is the ca cert, and pk8 is the client key in der pkcs-8 format. The logs: NOTE: Picked up JDK_JAVA_OPTIONS: --add-opens=java.base/java.lang=ALL-UNNAMED --add-opens=java.base/java.io=ALL-UNNAMED --add-opens=java.rmi/sun.rmi.transport=ALL-UNNAMED Server version name: Apache Tomcat/9.0.16 (Ubuntu) Server built: Sep 11 2019 19:47:51 UTC Server version number: 9.0.16.0 OS Name: Linux OS Version: 4.15.0-65-generic Architecture: amd64 Java Home: /usr/lib/jvm/java-11-openjdk-amd64 JVM Version: 11.0.4+11-post-Ubuntu-1ubuntu218.04.3 JVM Vendor: Ubuntu CATALINA_BASE: /var/lib/tomcat9 CATALINA_HOME: /usr/share/tomcat9 Command line argument: --add-opens=java.base/java.lang=ALL-UNNAMED Command line argument: --add-opens=java.base/java.io=ALL-UNNAMED Command line argument: --add-opens=java.rmi/sun.rmi.transport=ALL-UNNAMED Command line argument: -Djava.util.logging.config.file=/var/lib/tomcat9/conf/logging.properties Command line argument: -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager Command line argument: -Djava.awt.headless=true Command line argument: -XX:+UseG1GC Command line argument: -Djdk.tls.ephemeralDHKeySize=2048 Command line argument: -Djava.protocol.handler.pkgs=org.apache.catalina.webresources Command line argument: -Dorg.apache.catalina.security.SecurityListener.UMASK=0027 Command line argument: -Dignore.endorsed.dirs= Command line argument: -Dcatalina.base=/var/lib/tomcat9 Command line argument: -Dcatalina.home=/usr/share/tomcat9 Command line argument: -Djava.io.tmpdir=/tmp Loaded APR based Apache Tomcat Native library [1.2.21] using APR version [1.6.3]. APR capabilities: IPv6 [true], sendfile [true], accept filters [false], random [true]. APR/OpenSSL configuration: useAprConnector [false], useOpenSSL [true] OpenSSL successfully initialized [OpenSSL 1.1.1 11 Sep 2018] Initializing ProtocolHandler ["ajp-nio-8009"] Server initialization in [1,859] milliseconds Starting service [Catalina] Starting Servlet engine: [Apache Tomcat/9.0.16 (Ubuntu)] Deploying web application archive [/var/lib/tomcat9/webapps/market.war] SLF4J: Class path contains multiple SLF4J bindings. SLF4J: Found binding in [jar:file:/var/lib/tomcat9/webapps/market/WEB-INF/lib/slf4j-simple-1.7.24.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/var/lib/tomcat9/webapps/market/WEB-INF/lib/logback-classic-1.2.3.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation. SLF4J: Actual binding is of type [org.slf4j.impl.SimpleLoggerFactory] 3 Spring WebApplicationInitializers detected on classpath onStartup getContext getRootConfigClasses [main] INFO com.kodekonveyor.market.WebInitializer - getRootConfigClasses getServletConfigClasses [main] INFO com.kodekonveyor.market.WebInitializer - getServletConfigClasses getServletMappings [main] INFO com.kodekonveyor.market.WebInitializer - getServletMappings At least one JAR was scanned for TLDs yet contained no TLDs. Enable debug logging for this logger for a complete list of JARs that were scanned but no TLDs were found in them. Skipping unneeded JARs during scanning can improve startup time and JSP compilation time. Initializing Spring root WebApplicationContext [main] INFO org.springframework.web.context.ContextLoader - Root WebApplicationContext: initialization started [main] INFO org.springframework.data.repository.config.RepositoryConfigurationDelegate - Bootstrapping Spring Data repositories in DEFAULT mode. [main] INFO org.springfr
postgresql jndi datasource with certificate authentication?
Hi! Anyone have a postgresql jndi datasource with certificate authentication working? I have the following in context.xml: I have this in ~tomcat/.postgresql: root@market:/var/lib/tomcat9/.postgresql# ls -lL total 11 -rw-r--r-- 1 root root 4597 Oct 21 12:49 postgresql.crt -r 1 tomcat root 1329 Oct 21 17:40 postgresql.pk8 -rw-r--r-- 1 root root 1493 Oct 21 12:49 root.crt where the crt file is a pem client certificate, root.crt is the ca cert, and pk8 is the client key in der pkcs-8 format. The logs: NOTE: Picked up JDK_JAVA_OPTIONS: --add-opens=java.base/java.lang=ALL-UNNAMED --add-opens=java.base/java.io=ALL-UNNAMED --add-opens=java.rmi/sun.rmi.transport=ALL-UNNAMED Server version name: Apache Tomcat/9.0.16 (Ubuntu) Server built: Sep 11 2019 19:47:51 UTC Server version number: 9.0.16.0 OS Name: Linux OS Version: 4.15.0-65-generic Architecture: amd64 Java Home: /usr/lib/jvm/java-11-openjdk-amd64 JVM Version: 11.0.4+11-post-Ubuntu-1ubuntu218.04.3 JVM Vendor: Ubuntu CATALINA_BASE: /var/lib/tomcat9 CATALINA_HOME: /usr/share/tomcat9 Command line argument: --add-opens=java.base/java.lang=ALL-UNNAMED Command line argument: --add-opens=java.base/java.io=ALL-UNNAMED Command line argument: --add-opens=java.rmi/sun.rmi.transport=ALL-UNNAMED Command line argument: -Djava.util.logging.config.file=/var/lib/tomcat9/conf/logging.properties Command line argument: -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager Command line argument: -Djava.awt.headless=true Command line argument: -XX:+UseG1GC Command line argument: -Djdk.tls.ephemeralDHKeySize=2048 Command line argument: -Djava.protocol.handler.pkgs=org.apache.catalina.webresources Command line argument: -Dorg.apache.catalina.security.SecurityListener.UMASK=0027 Command line argument: -Dignore.endorsed.dirs= Command line argument: -Dcatalina.base=/var/lib/tomcat9 Command line argument: -Dcatalina.home=/usr/share/tomcat9 Command line argument: -Djava.io.tmpdir=/tmp Loaded APR based Apache Tomcat Native library [1.2.21] using APR version [1.6.3]. APR capabilities: IPv6 [true], sendfile [true], accept filters [false], random [true]. APR/OpenSSL configuration: useAprConnector [false], useOpenSSL [true] OpenSSL successfully initialized [OpenSSL 1.1.1 11 Sep 2018] Initializing ProtocolHandler ["ajp-nio-8009"] Server initialization in [1,859] milliseconds Starting service [Catalina] Starting Servlet engine: [Apache Tomcat/9.0.16 (Ubuntu)] Deploying web application archive [/var/lib/tomcat9/webapps/market.war] SLF4J: Class path contains multiple SLF4J bindings. SLF4J: Found binding in [jar:file:/var/lib/tomcat9/webapps/market/WEB-INF/lib/slf4j-simple-1.7.24.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/var/lib/tomcat9/webapps/market/WEB-INF/lib/logback-classic-1.2.3.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation. SLF4J: Actual binding is of type [org.slf4j.impl.SimpleLoggerFactory] 3 Spring WebApplicationInitializers detected on classpath onStartup getContext getRootConfigClasses [main] INFO com.kodekonveyor.market.WebInitializer - getRootConfigClasses getServletConfigClasses [main] INFO com.kodekonveyor.market.WebInitializer - getServletConfigClasses getServletMappings [main] INFO com.kodekonveyor.market.WebInitializer - getServletMappings At least one JAR was scanned for TLDs yet contained no TLDs. Enable debug logging for this logger for a complete list of JARs that were scanned but no TLDs were found in them. Skipping unneeded JARs during scanning can improve startup time and JSP compilation time. Initializing Spring root WebApplicationContext [main] INFO org.springframework.web.context.ContextLoader - Root WebApplicationContext: initialization started [main] INFO org.springframework.data.repository.config.RepositoryConfigurationDelegate - Bootstrapping Spring Data repositories in DEFAULT mode. [main] INFO org.springframework.data.repository.config.RepositoryConfigurationDelegate - Finished Spring Data repository scanning in 198ms. Found 1 repository interfaces. [main] INFO org.springframework.data.repository.config.RepositoryConfigurationDelegate - Bootstrapping Spring Data repositories in DEFAULT mode. [main] INFO org.springframework.data.repository.config.RepositoryConfigurationDelegate - Finished Spring Data repository scanning in 35ms. Found 1 repository interfaces. [main] INFO org.springframework.context.support.PostProcessorRegistrationDelegate$BeanPostProcessorChec