Secure your Java application connections to Flexible Server via JDBC and SSL
Published May 27 2022 08:24 AM 6,990 Views
Microsoft

Secure application connectivity is important to our customers. Postgres Flexible Server on Azure requires secure connection by default.  In this post, we will explain to you how to setup secured connection from your Java application to Flexible Server with JDBC and SSL. 

lock_1920_1080.jpg

Java Database Connectivity - JDBC

Java Database Connectivity (JDBC) is an application programming interface (API) which allows the programmer to connect and interact with databases. It provides methods to query and update data in the database through update statements like SQL's CREATE, UPDATE, DELETE and INSERT and query statements such as SELECT. Additionally, JDBC can run stored procedures. 

The JDBC API uses Java standard classes and interfaces to connect to databases. In order to use JDBC to connect Java applications to a specific database server, a JDBC driver that supports the JDBC API for that database server is required. You can download the latest version of the driver on the postgresql.org website via the download page. Figure below shows high level architecture of JDBC stack:

 

jdbc_architecture.gif

Figure 1:  JDBC high level architecture

 

Configuring SSL on the server

With Azure Database for PostgreSQL - Flexible Server being a PaaS service in Azure enforces connecting your client applications to the PostgreSQL service by using Transport Layer Security (TLS). TLS is an industry-standard protocol that ensures encrypted network connections between your database server and client applications. TLS is an updated protocol of Secure Sockets Layer (SSL).

Flexible Server supports TLS 1.2 and later. In RFC 8996, the Internet Engineering Task Force (IETF) explicitly states that TLS 1.0 and TLS 1.1 must not be used. Both protocols were deprecated by the end of 2019.

All incoming connections that use earlier versions of the TLS protocol, such as TLS 1.0 and TLS 1.1, will be denied by default.  You can control whether SSL\TLS is enabled by updating the require_secure_transport server parameter to OFF. You can also set TLS version by setting ssl_min_protocol_version and ssl_max_protocol_version server parameters.

 

Configuring SSL on the client

By default, PostgreSQL will not perform any verification of the server certificate. This means that it is possible to spoof the server identity (for example by modifying a DNS record or by taking over the server IP address) without the client knowing. In order to prevent spoofing, SSL certificate verification on the client must be used.

There are a number of connection parameters for configuring the client for SSL.  Below we will list these:

  1. ssl.  Connect using SSL. The server must have been compiled with SSL support. This property does not need a value associated with it. The mere presence of it specifies a SSL connection. However, for compatibility with future versions, the value "true" is preferred.  In this mode, when establishing a SSL connection the JDBC driver will validate the server's identity preventing "man in the middle" attacks. It does this by checking that the server certificate is signed by a trusted authority, and that the host you are connecting to is the same as the hostname in the certificate.
  2. sslmode. If you require encryption and want the connection to fail if it can't be encrypted then set  sslmode=require.  This ensures that the server is configured to accept SSL connections for this Host/IP address and that the server recognizes the client certificate. In other words if the server does not accept SSL connections or the client certificate is not recognized the connection will fail. Table below list values for this setting:
    SSL Mode Explanation Expected behavior with Azure Database for PostgreSQL - Flexible Server 
    disable I don't want to use encryption.  Possibly error since PostgreSQL Flex is enforcing encryption by default on the server . Will work, allowing non-encrypted traffic,  if non-default behavior is enforced on Flex Server disabling encryption. 
    allow I will allow encryption if server settings require\enforce it Encrypted, unless non-default behavior is enforced on Flex Server disabling encryption in which case non-encrypted. 
    prefer I will allow encryption  if the server supports it Encrypted with default Flex settings, if non-default behavior is enforced on Flex Server disabling encryption server -side , will allow for non-encrypted traffic
    require I want my data to be always encrypted, and I accept the overhead. This ensures that the server is configured to accept SSL connections for this Host/IP address and that the server recognizes the client certificate. In other words if the server does not accept SSL connections or the client certificate is not recognized the connection will fail. Encrypted with default Flex settings,  will error out if Flex default server settings changed to turn off encryption. 
    verify-ca I want my data to be always encrypted, and I accept the overhead. The server is verified by checking the certificate chain up to the root certificate stored on the client. Encrypted with default Flex settings,  will error out if Flex default server settings changed to turn off encryption. 
    verify-full I want my data to be always encrypted, and I accept the overhead. The server host name will be verified to make sure it matches the name stored in the server certificate. Encrypted with default Flex settings,  will error out if Flex default server settings changed to turn off encryption. Will attempt to make sure Flex server host name  matches the name stored in the server certificate, otherwise may throw an error. 
  3. sslcert, sslkey and sslrootcert. These parameters can override default location of the client certificate, the PKCS-8 client key and root certificate. These default to /defaultdir/postgresql.crt, /defaultdir/postgresql.pk8, and /defaultdir/root.crt respectively where defaultdir is ${user.home}/.postgresql/ in *nix systems and %appdata%/postgresql/ on windows. 

Example setting up SSL\TLS parameters to enforce SSL via JDBC Connection Properties:

String url = "jdbc:postgresql://localhost/test";

Properties props = new Properties();

props.setProperty("user","john");

props.setProperty("password","p@ssw0rd1");

props.setProperty("ssl","true");

props.setProperty("sslmode","require");

Connection conn = DriverManager.getConnection(url, props);

 

Example setting up SSL\TLS parameters to enforce SSL via JDBC Url:

String url = "jdbc:postgresql://localhost/testuser=fred&password=secret&ssl=true&sslmode=require";

Connection conn = DriverManager.getConnection(url);

 

We hope that you find this blog article helpful and are always interested how you plan to use Flexible Server offering with your Java applications via JDBC.  Additional information on topics discussed above can be found in following documents:

 

  1. Azure Database for PostgreSQL – Flexible Server Networking Documentation
  2. Azure Database for PostgreSQL - Flexible Server Security Documentation
  3. PostgreSQL Client SSL Support 
  4. PostgreSQL JDBC Driver Connection Parameters
  5. Secure Connections with SSL\TLS

 

We’re always eager to get your feedback, so please reach out via email to Ask Azure DB for PostgreSQL.

 

 

Co-Authors
Version history
Last update:
‎Jun 12 2022 12:00 PM
Updated by: