Skip to main content

PostgreSQL with SSL auth (Java client)

PostgreSQL Server

1. Generate certificates

Download easyrsa2 from github and extract it
 
# ./easyrsa build-ca
# ./easyrsa build-server-full postgresql-server
# ./easyrsa build-client-full postgresql-client
 
This will generate ca.crt in pki folder, postgres-server.crt, postgres-client.crt in pki/issued folder and postgres-server.key and postgres-client.key in pki/private folder.
 
PostgreSQL JDBC library cannot read .key file, which is why we have to convert the key to DER format (.pk8) file.
 
openssl pkcs8 -topk8 -outform DER -in postgres-client.key -out postgres-client.key.pk8 -nocrypt 
 
Give proper unix permissions to the certificates and keys, for eg.
 
# chown postgres:postgres postgres-server.key 
# chown postgres:postgres postgres-server.crt
# chmod go-r postgres-server.key
  

2. Edit postgresql.conf

ssl = on         
ssl_cert_file = '/opt/postgres-sec/postgres-server.crt'   
ssl_key_file = '/opt/postgres-sec/postgres-server.nopass.key'
ssl_ca_file = '/opt/postgres-sec/ca.crt'

2. Edit pg_hba.conf

On PostgreSQL > 12.0
 
hostssl    all     all      0.0.0.0/0     cert
 
On PostgreSQL < 12.0
 
hostssl    all     all      0.0.0.0/0     cert     clientcert=verify-ca
 
This will allow only remote machines with ssl turned on to connect to the server with a password and a valid certificate without verifying 'cn' attribute in the certificate with the hostname. More details on pg_hba.conf file and options supported are available here.
 
Note that, in older PostgreSQL servers, the only option is full certificate verification including cn. So, make sure that your client certificate has cn equal to your username.

Java Client

Using ConnectionPooling (Apache DBCP2)



Using Plain JDBC


Labels:
Location: Kurla, Mumbai, Maharashtra, India

Comments

Post a Comment

Popular posts from this blog

Multimaster replication with Symmetric DS

Symmetric DS is an awesome tool for trigger based replication whcih works for all major database vendors, including but not limited to PostgreSQL, MySQL, MSSQL, Oracle and many others. Symmetric-DS is a java application and can execute on any platform on whcih JRE is available including Windows and Linux. Trigger based replication, in constrast to disk based (eg. DRBD ) or transaction log file shipping based or statement based , works by registering triggers on DMLs and sending the data thus generated to remote machines. Another very popular trigger based DB replication tool is Slony . Symmetric-DS in addition to being database agnostic also supports multi-master replication (MMR). MMR usecase involves multiple database nodes, connected in a pool with DML updates coming from any of them. This is different from the normal master-slave replication, where slaves are not expected to generate any data events, and the sole authority of database is the master. MMR requirement causes d...
1 comment
Read more

PC Power supply and hacks

For posterity and myself, I'm leaving some tips and tricks of PC Power Supply Unit (PSU) whcih is an SMPS (Switched Mode Power Supply). There are a variety of uses of a +12V, +5V and +3V DC power supply like lighting up an LED strip or powering a raspberry pi. There are various colored cables in a typical ATX 12V SMPS. I'll list out the various color lines and what they mean, Sr. No Cable color Number of cables in a PSU Use 1 Green exactly one (1) Wake up signal from motherboard. Pressing PC power button makes this signal carry wake up signal to PSU to start. Green needs to be touched with the any ground to make the SMPS start. For self-starting PSUs, green needs to be connected with one black all the time. 2 Blue exactly one (1) -12V 3 Purple exactly one (1) +5V standby. When power supply is on standby mode (not on by signalling green), this line can give 1-2 A current. 4 Gray exactly one (1) Power good signal. When PSU levels has reached specificati...
3 comments
Read more

Cryptographic Primitive III: RSA Asymmetric Keys

RSA cryptosystems involves, a private key (which is kept private) and a public key, which is kept public i.e. known to everyone. The security of RSA hinges on the mathematically difficult problem of finding prime factorization of a very large number. Let's quickly disuss how a public, private key pair can be generated, Let, p and q be two large primes, then $n = q \times q$ $\phi(n) = (p-1) \times (q-1)$ Here, $\phi(n)$ is called euler's totient function Choose a random number $e$ such that, $e \in \left\{0,1,2...\phi(n)-1\right\}$ and $gcd(e,\phi(n)) = 1$ The gcd condition will ensure that we have an inverse of $e$ in $\mathbb{Z}_{26}$. Now, using extended euclidian algorithm one can get the inverse of e as d such that, $d \equiv e \pmod{\phi(n)}$ So, there we have it, the private key is $e$ and the public key is $(n,d)$. Few points to note here are, $p$ and $q$ are both $\geq 2^{512}$, although the recommened size is $2^{1024}$ $n$ is $\geq 2^{1024}$, although the recommended...
Post a Comment
Read more