Securing Postgresql Database

Database Security
This concerns with the use of broad range of information security controls to protect database potentially including the data, the database applications or stored functions, the database systems, the database servers and the associated network links against compromises of their confidentiality, integrity and availability. It involves various types or categories of controls, such as technical, procedural/administrative and physical.

What is the importance of database security
Database security is crucial for the following reasons and they include:

Regulatory compliance
With the coming up of the General Data Protection Regulation (GDPR) coming into force in 25 May 2018, data security is more important than ever before. Under the GDPR, organizations must ensure that their systems are secure and confidential, which means having strong database security is essential.

Reputation damage due to data breaches
Data breaches can also damage your brand and reputation. In fact, according to a study, almost 80% of consumers would take their business elsewhere if a company suffered a data breach that put their information at risk ,Thus its important that organisations ensure that their database system are secure at all time from external threats to prevent any breaches that may destroy the organisation's reputation.

Protect organisational data
To protect organizational data, which contains trade information and customer data. The data can be accessed by cybercriminals for malicious reasons, compromising customer privacy.

Threats in Database security
Attacks on database systems may come in many form some may come from malware-infected computer systems to targeted cyberattacks on your database systems themselves.Some of the most common types of attack on database systems may include :

SQL injection: This is when an attacker inserts malicious code into a website’s login page to obtain information.

Denial of service (DoS): This is an attempt to overload a database or website with superfluous requests, causing it to slow down or even shut down.

Database exploitation: This refers to the unauthorized use of sensitive data.

Code modification: This occurs when attackers change the code of a database.

Malicious insiders: This are people who have been given access to a database, but misuse their privileges.

Hidden flaws in code: These can be exploited by attackers to steal sensitive data or cause damage to your system.

Deep Dive into mechanisms used in postgresql database security

database security in postgresql is addressed in several levels and they include :

• Database file protection. All files stored within the database are protected from reading by any account other than the Postgres superuser account.

• Connections from a client to the database server are, by default, allowed only via a local Unix socket, not via TCP/IP sockets. The backend must be started with the -i option to allow non-local clients to connect.

• Client connections can be restricted by IP address and/or user name via the pg_hba.conf file in PG_DATA.

• Client connections may be authenticated vi other external packages.

• Each user in Postgres is assigned a username and (optionally) a password. By default, users do not have write access to databases they did not create.

• Users may be assigned to groups, and table access may be restricted based on group privileges.

Network -Level security
The network level security mechanism has two main components that include the unix domain socket ,TCP/IP socket, and firewall deployed on the host machine.

Unix Domain sockets
To be able to connect a postgresql database you need to connect it to a Unix-based environment, Unix domain sockets (or UDS) is the feature that enables the said connection. It looks like a separate file in the filesystem. The socket owner is the OS user that runs the Postgres server, and the only way to access that UDS file directly is from the machine where it is installed.

A UDS is assigned the same access control as other files on the filesystem, although it only requires write permissions. You can control and manage a UDS using the unix_socket_group and unix_socket_permissions configuration options. You can also change access controls on the directory hosting the socket to change its permissions
PostgreSQL lets you create multiple sockets, via the unix_socket_directories option. You can specify several directories, each with its own permissions, to define separate permissions for different applications or groups of users.

Restricting Listening to address
For example if the node that postgresql is running on contains several network interfaces you can make use of the listen_address parameter of the configuration file. This parameter ensures that the server listens only to those addresses that the clients will use to connect.

listen_addresses = 'localhost, 192.168.0.1'

If users connecting to the database are always on the same node, you can disable listening to TCP sockets. Therefore, the server will accept connections from Unix domain sockets only, and not from the network.

To achieve this, place an empty line as the address that the server should listen to as below:

listen_addresses = ''

The following methods are helpful to ensure an appropriate level of security at the network level and protect your Postgres databases.

Firewalls
A firewall lets you define rules that define what inbound and outbound traffic is allowed. There are several parameters you can use to define these rules, including the local port (the default is 5432 in PostgreSQL), the protocol (IPv6 or TCP), as well as the source address (typically a list of subnets or addresses). To ensure your database remains protected, strictly control external access to the server.

some of these methods used for database security using firewalls include:iptables utilitty

sample implementations include:

# Make sure to keep the established connections valid.
iptables -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT

# Allow SSH.
iptables -A INPUT -p tcp -m state --state NEW --dport 22 -j ACCEPT

# Allow PostgreSQL.
iptables -A INPUT -p tcp -m state --state NEW --dport 5432 -j ACCEPT

# Allow all outbound, drop everything else inbound.
iptables -A OUTPUT -j ACCEPT
iptables -A INPUT -j DROP
iptables -A FORWARD -j DROP

Database -level security
In the database level we are going to look at some of the data mechanisms that can be used to secure your database and these mechanisms may include:
Roles and permission,Auditing,row-level security.

Auditing
Keeping an audit trail is to ensure that you have accurate and detailed records. You can monitor several aspects of Postgres, including the server itself, by enabling the verbose logging feature.

One of the most efficient options is keeping a detailed log. To enable it, add the following parameters to the server configuration file as follows :

; Log successful and unsuccessful connection attempts.
log_connections = on

; Log terminated sessions.
log_disconnections = on

; Log all executed SQL statements.
log_statement = all

Row -level security
The row level security method is used in setting up privileges to table rows. It relates to both the existing rows brought by the SELECT command and the new rows that result from the INSERT, UPDATE, or DELETE commands. To apply this method, you need to enable RLS for the table and configure access (create a policy).

when implementing row-level security policies these are some of the aspects you should consider:

• Use the CREATE POLICY command—to define new row-level security policies for each table.
• The policy is selective, only applying to rows that match a predefined SQL expression.
• Use ALTER TABLE … ENABLE ROW LEVEL SECURITY—to enable row-level security on the table. If this option is not enabled, your policy cannot be applied to the table.
• USING statements are used to check existing table rows for the policy expression.
• WITH CHECK statements are used to check new rows.
• Policy names can be repeated across tables—you can create a policy with the same name and reuse it across multiple tables.
• Each policy grants permission to specific database operations, such as DELETE, UPDATE, or SELECT.

and some of the parameters that one should put into consideration while implementing row -level security include:

• Name—this is the name of the policy.
• table_name—lets you create a name for the table the policy is applied to.
• command—the default option is DELETE. ALL. You can also use, SELECT, ALL, INSERT, UPDATE.
• role_name—the default is PUBLIC, which means the policy applies to all database users.
• using_expression—a SQL expression that returns boolean. Each row is checked against this expression—if it returns false, it is silently suppressed and cannot be viewed or modified by the user. No error is returned.
• check_expression—a SQL expression returning boolean, which is used when INSERT or UPDATE operations are performed on the table. Rows are allowed if the policy expression is true, and if it returns false, an error is returned.
• RESTRICTIVE—lets you create a restrictive policy. Restrictive policies can be applied together to a single query. You can use this parameter to restrict access to tables.

Roles and permissions
Postgresql comes with an inbuilt user permission system designed around the concept of roles .a database account name is treated as a role, and it comes with a LOGIN attribute that enables the role to connect to the database.Roles can be assigned other attributes that grant them certain types of privileges.

sample example implementation of how roles are created:

CREATE USER jane;
Or
CREATE ROLE jane LOGIN;

Transport Level Security
This is a secure protocol for safe communication on the internet.PostgreSQL supports it by default for data transport and database connection (it is called SSL in the documentation). When enabled, it allows the client to authenticate itself on the server (and vice versa) using the certificates issued by a trusted certificate authority.

To enable TLS for PostgreSQL connections, you’ll need a server key and certificate. Protect the key and certificate with a passphrase, which can either be entered manually when the server starts, or automatically, by writing a script that uses the ssl_passphrase_command configuration parameter.

There are various transport level security configurations that can be implemented to your database and they include:

client TLS.
Server TLS.

Best postgresql security practices
Prevent external connections to the database
You can disable remote access to your database using pg_hba.conf. If you need remote access, you can use SSH to access the database host machine, then use a local connection. You can also use SSH to configure tunnel access to your PostgreSQL database.

Also you can configure listen_addresses to localhost, or the specific host machine running the application that uses the database. This forces the operating system to reject connection attempts originating from any other machines except the PostgreSQL host or another known host. This can help prevent unauthorized access to your database and any underlying data structures.

Staying alert on critical security updates and patches
Regularly check for updates and patches this a good practice in that you are able to check what updates are important for the efficient working of your postgresql databases.

Restricting database logs from revealing more information than intended.
Ensure you use standard practices to administer your database, to prevent revealing sensitive information in database logs.

Using one way encryption for values that do not need to be decrypted
Using encryption techniques such as AES, can be decrypted. Hash-based encryption techniques, on the other hand, such as MD5, perform one-way encryption. Ensure you use the most appropriate encryption for each type of data—for example, use two-way encryption for data that is needed for analysis, and one-way encryption for user passwords.

Data security technologies that can help secure you postgresql database :

There are various data security technologies that organisations can use to secure and protect their database systems from external and internal threats and some of the technologies include:

• Data encryption.
• Data masking.
• Tokenization.
• Authentication.

Data Masking
Masking can help protect your data from exposure to unauthorized or malicious sources externally or internally. Masking can be applied to personally identifiable information (PII), such as a phone number or email address, by obscuring parts of the PPI, e.g., the first eight digits or letters within a database.

Tokenization
Tokenization replaces data with random characters,The “token,” which relates to the original data, is stored away separately in a database lookup table, where it is protected from unauthorized access.

Authentication
This is the process of confirming or validating user login credentials to make sure they match the information stored in the database. User credentials include usernames, passwords, PINS, security tokens, swipe cards, biometrics, etc.

Authentication is a frontline defense against unauthorized access to confidential and sensitive information, making it an important process. Authentication technologies, such as single sign-on, multi-factor authentication, and breached password detection make it simpler to secure the authentication process while maintaining user convenience.

Data encryption
Data encryption uses an algorithm to scramble every data character converting information to unreadable format Encryption keys from authorized users only are needed to decrypt the data before reading the files.

Conclusion
Database security is and should be a top priority in any organisation managing any database system thus you need apply the appropriate measures and set them to work together as solid barriers against intruders. Such barriers will block the hackers and hinder them as much as possible from accessing your database systems.