How to Encrypt a PostgreSQL Database at Rest and in Transit

PostgreSQL is a robust, open-source object-relational database system with numerous capabilities that securely store and handle even the most complex data demands.

Because of its well-established design, reliability, data integrity, extensive feature set, extensibility, and the commitment of the open-source community to continuously provide cutting-edge solutions, PostgreSQL has established a solid reputation.

It is not surprising that PostgreSQL has emerged as the preferred open-source relational database among several individuals and organizations.

In this blog, we will discuss how we can Encrypt a PostgreSQL Database at Rest and in Transit.

Data Encryption

Encryption is the process of modifying data in such a way that it is unreadable for someone other than those with special knowledge (by using a “key”) that enables them to modify the information back to its original, readable form.

Data encryption helps in preventing unauthorized individuals from reading information.

Data at Rest

When data does not travel actively from device to device or network to network, such as information saved on a hard disc, laptop, flash drive, or otherwise archived/stored is known as Data at rest.

Therefore, it aims to secure inactive information stored on any computer or network.

For example, your PostgreSQL’s data_directory, MySQL/MariaDB data_dir, or MongoDB’s dbPath storage locations.

Data in transit

Data in motion or in transit is when it travels actively from one place to another, such as through the Internet or a private network.

Its protection in transit is its security as it passes from network to network or when it is moved from a local storage system to a cloud storage device.

Data transferred across an insecure connection can be intercepted by the attacker by listening in. This can be prevented if TLS/SSL encryption is used to encrypt the data transferred over the wire between the database client and the database server.

To learn more about encryption, you can refer to the Blog.

Database Encryption

Database encryption offers improved security for your data in transit and at rest. The recent security breach cases have caused many organizations to take encryption seriously.

Because they often contain the most important asset for most organizations, database servers are frequently the target of attackers.

An attacker will likely take advantage of the data on your server once they have access to sensitive information.

Why do we need to Encrypt the Database?

It is highly recommended practice to encrypt your data, particularly for companies in the financial, healthcare, or e-commerce industries.

People are highly conscious of data security and privacy, and they want their information to be kept safe and utilized only when necessary.

Database encryption has the following wonderful advantages:

• Although security breaches and data breaches are unavoidable. However, with improved security and encryption techniques, hackers may be prevented from decrypting or analyzing the data to get additional insight.
• One of the most crucial criteria when it comes to security laws like PCI-DSS is encryption. It is an essential prerequisite.
• With centralized key management and simple APIs for encryption, encryption key management is perfect for safeguarding sensitive data.

Ways to Encrypt PostgreSQL Database

PostgreSQL supports multiple encryption levels and flexibility in safeguarding data from exposure from network security breaches, dishonest administrators, and loss of database servers.

You may also need to encrypt sensitive data, like medical information or financial transactions.

Password Encryption

The administrator cannot verify the real password that is assigned to a database user since user passwords are saved as hashes. Passwords for PostgreSQL databases are different from those for operating system users.

Every database user’s password is kept in the pg_authid system catalog. Password authentication will always fail for a user for whom no password has been set up since the password that is saved for that user is null.

Pros

• Passwords are encrypted so that they cannot be viewed in plain text by someone with unauthorized access to the database.
• Password encryption aids in complying with security compliance laws and regulations like GDPR, HIPAA, and others.

Cons

• Password encryption may have a little negative impact on authentication speed.
• Especially when it comes to password resets and user administration, maintaining encrypted passwords can be more difficult than maintaining plain text passwords.

How to Achieve

There are numerous password-based authentication techniques. Both of these approaches work similarly, but they differ in how the credentials of users are kept on the server and how a client’s password is transmitted over the network.

• scram-sha-256
• md5
• password

Encrypt Data Across A Network

All data transmitted over the network, including the password, queries, and returned data, is encrypted via SSL connections.

Administrators can choose which hosts require SSL-encrypted connections (hostssl) and which can utilize non-encrypted connections (host) using the pg_hba.conf file. Clients can also choose that they will only use SSL to connect to servers.

You can also encrypt transmissions using SSH or Tunnel.

Pros

• Data encryption ensures that information cannot be read or intercepted by unauthorized persons while it is being transmitted across a network.
• To prevent tampering, SSL/TLS makes sure that data is transmitted without alteration.

Cons

• Data encryption increases computational overhead, which, particularly on busy systems, may cause a slight decrease in speed.
• It can be difficult and require careful configuration to set up and manage SSL/TLS certificates, particularly in a distributed or clustered environment.

How to Achieve

A configuration file that is kept in the data directory of the database cluster and is commonly called pg_hba.conf manages client authentication. Host-based authentication is referred to as HBA.

When the data directory is initialized by initdb, a default pg_hba.conf file is installed. However, you can use the hba_file configuration parameter to specify a different location for the authentication configuration file.

SSL Host Authentication

SSL certificates can be exchanged between the client and the server. This offers more robust identity verification than just using passwords, although it does require additional setup on both ends.

It doesn’t allow a computer to pose as the server for the brief purpose of reading the password that the client sends.

Furthermore, it aids in preventing “man in the middle” attacks, in which a computer positioned between the client and the server assumes the role of the server and reads and transfers all data between the two.

Pros

• By using SSL host authentication, customers may be sure they are connecting to the correct server and not a malicious or fraudulent one.
• By pretending to be the server, SSL host authentication stops hackers from listening in on client-server conversations.

Cons

• It might be difficult to set up and maintain SSL certificates for host authentication, particularly in settings with several servers and clients.
• Ensuring the security of host authentication requires careful handling of SSL certificates and keys. Key loss or compromise may lead to security lapses.

How to Achieve

For enhanced security, PostgreSQL comes with a native capability for encrypting client/server communications over SSL connections.

For this to work, PostgreSQL support must be enabled during build time, and OpenSSL must be installed on both client and server systems.

OpenSSL supports numerous authentication techniques and ciphers of different strengths. While a list of ciphers can be supplied in the OpenSSL configuration file, you can alter postgresql.conf’s ssl_ciphers to specifically name ciphers for usage by the database server.

To set up SSL, make sure the value of parameter ssl = on is set in postgresql.conf

After generating the SSL you can mention the path of the SSL files in the below options
ssl_cert_file=’server.crt’
ssl_key_file=’server.key’

Other Methods to encrypt the data

Data Partition Encryption

Encrypting the database while it is on the disc is the first technique. It is the most basic file system-level encryption currently in use. If the drives or the computer as a whole are stolen, this method stops unencrypted data from being read from them.

Since the operating system offers an unencrypted view of the data when the file system is mounted, this does not protect against attacks during that time.

However, the encryption key must be transferred to the operating system in order for the file system to be mounted. At times, the key is kept on the host that mounts the disc.

Pros

• Data partition encryption provides an additional degree of protection, shielding private information from unwanted access.
• Aids in complying with legal obligations and privacy and data protection regulations, like GDPR, HIPAA, etc.
• Permits the encryption of particular partitions, giving the user fine-grained control over the data that is encrypted.

Cons

• Data partition encryption can be difficult to manage and implement, and it takes extra work.
• Encryption keys must be managed properly, and this can be difficult to do securely.
• Performance overhead may be introduced by encrypting and decrypting data, particularly for big datasets.

Encryption For Specific Columns

Certain fields can be stored encrypted due to the pgcrypto module. If there is just a small amount of sensitive data, this is helpful. After the data is decrypted on the server and delivered to the client, the client provides the decryption key.

Duration the data is being decrypted and transferred between the client and server, the decrypted data and the decryption key are kept on file on the server for a short duration.

This creates a brief period of opportunity for someone with full access to the database server, such as the system administrator, to intercept the data and keys.

Pros

• You can preserve sensitive data while keeping other data unencrypted by encrypting certain columns; this can help adhere to legal requirements like GDPR.
• It may be less computationally demanding to encrypt individual columns as opposed to full tables, therefore reducing the impact on performance.

Cons

• While particular column encryption safeguards individual columns, if security precautions are not taken with regard to those columns, other columns become vulnerable to unwanted access.
• Your database design and application code become more complex when you implement and manage encryption for particular columns.

Conclusion

Sensitive and valuable amounts of data in an organization rise simultaneously since sensitive data is always present, even in our day-to-day personal lives.

It’s critical to realize that not all data needs to be encrypted. Certain types of data may not require encryption because they are regularly shared globally or used for different purposes.

Consider the benefits and drawbacks of implementing encryption in your database. Finding the right place and application method for it allows you to create a secure environment without sacrificing efficiency.

To see Admin configuration password encryption and decryption in magento, you can refer to the Blog.

NEED HELP?

Was this guide helpful? Please share your feedback in the comments below.

In case you have any issues/queries regarding the module please raise a ticket at
https://webkul.uvdesk.com/en/customer/create-ticket/

For any further information or query contact us at [email protected].

Thanks for reading!!