I am trying to connect to a PostgreSQL database server using SSL but I get the following error:
If you have a certificate signed by a global certificate authority. You need to make this available to the Java client to enable it to validate the server's certificate. Only the JDBC 3 driver supports SSL. Setting the connection URL parameter sslfactory=org.postgresql.ssl.NonValidatingFactory will turn off all SSL validation. PostgreSQL with client certificates. SSL have to be set up (HowToPostgreSQLoverSSL). Must have cert of trusted authority ( PGDATA=/var/lib/pgsql/data ).
The PostgreSQL database server is 9.6 running on Ubuntu 16.04 LTS
I am able to connect to this database using PGAdmin4 using SSL with no problems.
I used a self-signed certificate authority (CA)
Here is how I generated the required files
ca-priv.key - my CA private key file ca-cert.crt - my CA certificate file server-priv-key - my server private key file server-cert-req.csr - my server certificate request file server-cert.crt - my server certificate file server-cert-fullchain.crt - my server certificate that includes the full chain of certificates up to my root CA certificate johnsmith-priv.key my user private key file johnsmith-cert.crt my user certificate file johnsmith-cert-req.csr my user certificate request file My CA files
From the server machine, logged in as root
Generate CA private key ca-priv.key openssl genrsa -des3 -out /etc/ssl/private/ca-priv.key 2048
Set permissions on CA private key ca-priv.key so there is no group or world access
chmod 600 /etc/ssl/private/ca-priv.key
Generate CA certificate ca-priv.crt using CA private key
From the server sudo openssl req -new -x509 -days 3650 -subj '/C={MyCountry}/ST={My State}/L={My Location}/O={My Org}/CN={my CA name}' -key /etc/ssl/private/ca-priv.key -out /usr/local/share/ca-certificates/ca-cert.crt
Add certificate to the server trusted roots
update-ca-certificates
Server Files
From the server machine, logged in as root
Generate server private key server-priv.key openssl genrsa -des3 -out /etc/ssl/private/server-priv.key 2048
Remove the passphrase from server private key server-priv.key
openssl rsa -in /etc/ssl/private/server-priv.key -out /etc/ssl/private/server-priv.key
Set permissions on server private key server-priv.key so world has no access
chmod 640 /etc/ssl/private/server-priv.key
Generate server certificate request server-cert-req.csr
openssl req -new -nodes -key /etc/ssl/private/server-priv.key -days 3650 -out /tmp/server-cert-req.csr -subj '/C={My Country}/ST={My State}/L={My Location}/O={My Org}/CN={my server name}'
Generate (sign) the server certificate request by
openssl x509 -days 3650 -req -in /tmp/server-cert-req.csr -CA /usr/local/share/ca-certificates/ca-cert.crt -CAkey /etc/ssl/private/ca-priv.key -CAcreateserial -out /usr/local/share/ca-certificates/server-cert.crt
Generate the server certificate that includes the full chain back to the root signing CA
cat /usr/local/share/ca-certificates/server-cert.crt /usr/local/share/ca-certificates/ca-cert.crt > /usr/local/share/ca-certificates/server-cert-fullchain.crt Because there is no intermediary between the server and the root, this step is not really required by client
Log into the postgreSQL sever, create the group sslcert and add user john to the group
su - postgres psql CREATE GROUP sslcert; ALTER GROUP sslcert ADD USER john;
Modify PostgreSQL /etc/postgresql/9.6/main/pg_ident.conf:
# MAPNAME SYSTEM-USERNAME PG-USERNAME mymap johnsmith john
Modify PostgreSQL /etc/postgresql/9.6/main/postgresql.conf for SSL
ssl = on ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' ssl_prefer_server_ciphers = on #ssl_ecdh_curve = 'prime256v1' ssl_cert_file = '/etc/ssl/certs/server-cert.pem' ssl_key_file = '/etc/ssl/private/server-priv.key' ssl_ca_file = '/etc/ssl/certs/ca-cert.pem' #ssl_crl_file = '
Modify PostgreSQL /etc/postgresql/9.6/main/pg_hba.conf:
hostssl all +sslcertusers all cert clientcert=1 map=mymap
User Files
From the server machine, logged in as john
Generate my private key file johnsmith-priv.key openssl genrsa -des3 -out ~/johnsmith-priv.key 2048
Remove the passphrase from my user private key johnsmith-priv.key
openssl rsa -in ~/johnsmith-priv.key -out ~/johnsmith-priv.key
Generate my user certificate request johnsmith-cert-req.csr
openssl req -new -nodes -key ~/johnsmith-priv.key -days 3650 -out ~/johnsmith-cert-req.csr -subj '/C={My Country}/ST={My State}/L={My Location}/O={My Org}/CN=johnsmith'
Request my CA to sign my user certificate request
sudo openssl x509 -days 3650 -req -in ~/johnsmith-cert-req.csr -CA /etc/ssl/certs/ca-cert.pem -CAkey /etc/ssl/private/ca-priv.key -out ~/johnsmith-cert.crt -CAcreateserial
From the client machine, logged in as johnsmith
create the .postgresql directory
mkdir ~/.postgresql
copy these certificate and private key files from the server to the client
These filenames are the recommended names according to the PostgreSQL docs scp [email protected]:/user/local/share/ca-certificates/ca-cert.crt ~/.postgresql/root.crt scp [email protected]:/user/local/share/ca-certificates/server-cert.crt ~/.postgresql/postgresql.crt scp [email protected]:/johnsmith-pri.key ~/.postgresql/postgresql.key
set the permissions on postgresql.key so no world access
chmod 640 ~/.postgresql/postgresql.key
![]() Start DBeaver
Create a database connection to server.com with the following settings
user: john database: postgres ssl: checked root: ~/.postgresql/root.crt ssl certificate: ~/.postgresql/postgresql.crt ssl certificate key: ~/.postgresql/postgresql.key ssl mode: verify-ca
I get the following error:
Could not read SSL key file /Users/johnsmith/.postgresql/postgresql.key. java.io.IOException: extra data given to DerValue constructor
I can confirm the SSL configuration on the server is correct because I can connect to it from application PGAdmin4.
Any ideas how can I resolve this error?
Comments are closed.
|
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |