As we fight this war against Covid-19, our day-to-day lives have transformed the normals. Same holds true for businesses. So many businesses are asking their employees to work from home (WFH) to ensure a smooth functioning of the business. While WFH is the only viable option there is, enterprises are growingly concerned about data security. Most organizations who were confident of the data being secure in their data centres with no access to the outside world, have gone into a panic state allowing their teams to work remotely. This may stand to expose their databases and applications to all kinds of security threats. Therefore, it is important to be ready against the unforeseen menaces. We always make it a point to educate our customers on best security practises. It is good to implement security at database and application levels, rather than just relying on infrastructure level security measures like firewalls, antiviruses, etc. It gives it an additional level of protection against cyber-attacks and data theft.
Postgres as a database has got a wide acceptance in the industry when it comes to security, as it complies with many security standards and has US government security certificates. The database itself has several inbuilt security capabilities, and here I’m drawing out some tips to better secure your data in PostgreSQL:

1. Do not leave the database connection open for the man-in-the-middle attack

When it comes to securing access to Postgres database, you should limit the access only from application server IP address using application specific database user to the database server specific to that application. This can be controlled using pg_hba.conf file. The entries in pg_hba.conf file are in the below format.
connection type database user Client IP address authentication method.

# local DATABASE USER METHOD [OPTIONS]
# host DATABASE USER ADDRESS METHOD [OPTIONS]
# hostssl DATABASE USER ADDRESS METHOD [OPTIONS]
# hostnossl DATABASE USER ADDRESS METHOD [OPTIONS]
By using connection type you can specify whether database server will accept the local or remote connection and whether the connection would be SSL connection or plain text connection. It is important that the data traffic between application server and database is always on secured encrypted channel to avoid the middle-man attack. Postgres does support SSL connection that ensures the communication between clients and database is on encrypt channel which makes connection sniffing difficult.

2. Avoid specifying database users name in pg_hba config file.

Though this may seem like a minor security measure, but when implemented, it secures your database against insider/outsider security threats. The database server might be hosting multiple databases catering to multiple applications hence it becomes important that application users’ accesses are limited only to application specific database. By using specific database name in pg_hba entry, the application users access are limited to just application specific database.
Since pg_hba.conf entries are in plain text format, rather than specifying the user names in plain text, DBA should create the group of database users at database level and specify group name in pg_hba file instead of individual database users name, making pg_hba entries more secure. This will act as an additional layer of security.

As you can see in the above screenshot, app_user, appuser1 are a part of the app_group group that have access to db_test database. Now in pg_hba.conf file, we can mention the name of the group rather than all individual users that should have access to db_test database.

This entry will allow access to only intended database users and make it difficult to identify which different users are present on database or allowed access to the application by just looking at the pg_hba config entry. If someone tries to connect with user test to db_test database, it will result into failure as test user is not a part of app_group.

3. Choose encryption algorithm for password

Instead of using md5, you should use more secure SCRAM authentication method for password encryption as it stores the password hashes in an irreversible format. Apart from SCRAM, Postgres provides various other options of authentication like ldap, pam, gss, sspi, ident, radius, cert. Strong password hashing mechanism and authentication method will protect database against hacker who may try to decrypt the password. With this, it is even possible to integrate Postgres with existing organization wide authentication method. Integrating it with organization wide authentication mechanism like ldap will make it easier to standardize the authentication process and detect/prevent such unauthorized access.
Also, when it comes to defining the user password, you should use EnterpriseDB user password policy that lets you strengthen the password.

4. Setup appropriate Role-based access for users

At database level, predefined roles with appropriate grants provide additional level of security as the application or admin users have only required privileges. Roles makes it easier to grant and revoke the privileges at role level rather than managing it at individual user levels. Create separate roles for DBA and application users. The application role level can be restricted managing the access to schema objects and DBA role can have more detailed administrative access. Roles makes it is easier to detect the unauthorized access granted to individual users.

5. Do not let database logging reveal more than intended information

When it comes to securing your environment, it is always important that you follow standard practises for database administration. For example, it is a normal practise to create database user using PSQL command “create user” as shown below. If the statements logging has been enabled on database server, this will result into password being displayed in the log file. You can standardize the user creation process using alternate option, the OS command utility “createuser” that will display the password in encrypted format in the log file and save you from unintended password exposure.

6. Overcoming the challenges of pg_crypto

As Postgres has become choice of database for critical finance application, it is important that it provides encryption features that will store sensitive information in the encrypted format. This is where pgcrypto function comes to help. Pgcrypto module provides many functions to encrypt your password or to encrypt the sensitive information like credit card # or social security #. It provides pgp encryption functionality that further provides symmetric and asymmetric encryption function.
The encryption key is stored in function body and anyone having execute permission can view the key stored in the function. When it is important to secure the business logic, EDB* wrap functionality will help as it will store the function body in obfuscated format and secure the function from inside threats.

7. Keep the business flow simple and use data redaction to hide the sensitive information

In certain cases, rather than encrypting the data, it is more important to limit the exposure of sensitive information in the tables. Data redaction feature of EnterpriseDB works well for such requirements. Data redaction functions will redact the column values for users based on redaction policy as shown in the below example.

8. Control access to table rows using row level security without using tedious encryption logic

If the intention is to control which user should have access to which rows in the table, you should use EnterpriseDB dbms_rls package to define row level security (RLS) policy. Once the RLS policy has been defined, all the subsequent DML transaction would follow the policy. This feature is equivalent to Virtual private database and policies can be defined to control which users have access to which rows inside the table.

9. Protection against SQL inject attack

It is important to protect your database against SQL injection attack where the attacker would gain access to data. Sometimes attackers will try to inject queries that may compromise the server availability or perform denial of service attack. To protect postgres database against such attacks, EnterpriseDB Postgres provides SQL protect features that protect database using protected roles. SQL protect works in 3 protection level.
a) In Learning mode, it will track and record all activities by the protected roles and relations accessed by that role.
b) If set in passive mode, it will start monitoring SQL statements and will generate warning if there are SQL statements execution breaking the defined rules but will not stop the sql statement execution. This may help to quickly identify any unauthorised relation access or query execution.
c) The last ACTIVE mode, in fact will not at all allow any unauthorised queries execution. The active mode will actively protect database against SQL injection attack.

10. Encryption at rest

Encryption at rest is an area where the Postgres community is putting in efforts to develop inbuilt capabilities for now, but that should not stop you from trying or exploring third party tools to secure your data at rest. There are many third party tools available, each with its own pros and cons – so you can analyse and see what works for you better.
In a nutshell, using these various techniques and options will ensure better security of your Postgres deployment. I have tried to cover the inbuilt features which are available as a part of PostgreSQL and EnterpriseDB Postgres. There are many more ways you can secure your data in PostgreSQL to stay worry-free. Our experts will be happy to help if you reach out to us at success@ashnik.com