PostgreSQL security best practices

Time:2021-7-30

Database is the “Holy Grail” in the eyes of hackers. We need to take care of it like “flowers”. This article mainly introduces the best practices of database protection. First, starting with the most commonly used open source database PostgreSQL, we will introduce several security levels you need to consider one by one:

Network layer security in PostgreSQL
firewall
Ideally, the PostgreSQL server should be completely isolated and do not allow any inbound requests, SSH or PSQL. However, PostgreSQL does not provide out of the box support for such gateway settings.
At most, we can only set a firewall to lock the port level access of the node where the database is located to improve the security of the database server. By default, PostgreSQL listens on TCP port 5432. Depending on the operating system, the way to lock other ports will be different. Take the firewall most commonly used in Linux as an example. The following lines of code can easily complete the task:

Ensure that existing connections are not dropped

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 all inbound

iptables -A OUTPUT -j ACCEPT
iptables -A INPUT -j DROP
iptables -A FORWARD -j DROP
It is recommended to use tools when updating iptables rules. In this way, even if you accidentally lock yourself out, it can automatically roll back the changes.
This PostgreSQL rule allows everyone to connect to port 5432. Of course, you can also modify it to accept only specific IP addresses or subnets to make the restrictions more stringent:

Only local subnets are allowed to access PostgreSQL ports

iptables -A INPUT -p tcp -m state –state NEW –dport 5432 -s 192.168.1.0/24 -j ACCEPT
Continue to discuss our ideal situation. To completely restrict all inbound connections to port 5432, you need a local agent of some type, which maintains a persistent outbound connection to the client node and can proxy traffic to the local PostgreSQL instance.
This kind of proxy is called “reverse channel”. The specific use method can be demonstrated through the function of SSH Remote port forwarding. Run the following command to open a reverse channel on the node where the PostgreSQL database is running:
ssh -f -N -T -R 5432:localhost:5432 [email protected]<client-host>
The node of PostgreSQL needs to be able to access < client host >, and the SSH daemon on it should also be running. The following instructions will forward port 5432 on the database server to port 5432 on the client machine, so that you can connect to the database through this channel:
psql “host=localhost port=5432 user=postgres dbname=postgres”
PostgreSQL listening address
It is a good habit to restrict the address of the server listening for client connections through configuration file instructions. If there are multiple network interfaces on the node running PostgreSQL, you can ensure that the server will only listen to one or more interfaces connected by the client:
listen_addresses = ‘localhost, 192.168.0.1’
If the client connected to the database always resides on the same node or on the same kubernetes pod with the database, PostgreSQL runs as a sidecar container. Disabling socket listening can completely eliminate the impact of the network. Setting the listening address to an empty string enables the server to only accept socket connections in the UNIX domain:
listen_addresses = ”
Transport level security in PostgreSQL
When most of the world’s networks turn to HTTP, choosing strong transmission encryption for database connections has become a necessary item. PostgreSQL itself supports TLS (because of historical problems, it is still called SSL in documents, configuration files and CLI). We can also use it for server-side and client-side authentication.
Server side TLS
For server authentication, we first need to prepare a certificate for the server to authenticate with the connected client. On let’s encrypt, we can find the free X.509 certificate. Take the command line tool as an example:
certbot certonly –standalone -d postgres.example.com
It should be noted that certbot uses the http-01 challenge of acme specification by default to verify the certificate request. Here, we need to ensure that the DNS of the requesting domain pointing to the node is valid and port 80 is open.
In addition to let’s encrypt, if you want to generate all the information locally, you can also choose the OpenSSL command line tool:

Generate a self signed server CA

openssl req -sha256 -new -x509 -days 365 -nodes \

-out server-ca.crt \
-keyout server-ca.key

Generate the server CSR, and fill in the CN with the host name to connect to the database

openssl req -sha256 -new -nodes \

-subj "/CN=postgres.example.com" \
-out server.csr \
-keyout server.key

Visa

openssl x509 -req -sha256 -days 365 \

-in server.csr \
-CA server-ca.crt \
-CAkey server-ca.key \
-CAcreateserial \
-out server.crt

In a production environment, remember to update the certificate before it expires.
Client TLS
By verifying that the X.509 certificate provided by the client is signed by a trusted certification authority (CA), the server can verify the identity of the connected client.
It is recommended to use different CAS to issue certificates to the client and server respectively. The following code creates a client Ca and uses it to give the client a visa:

Generate a self signed client CA

openssl req -sha256 -new -x509 -days 365 -nodes \

-out client-ca.crt \
-keyout client-ca.key

Generate client CSR. Cn must fill in the database role name used for connection

openssl req -sha256 -new -nodes \

-subj "/CN=alice" \
-out client.csr \
-keyout server.key

Visa

openssl x509 -req -sha256 -days 365 \

-in client.csr \
-CA client-ca.crt \
-CAkey client-ca.key \
-CAcreateserial \
-out client.crt

Note that the common name (CN) field in the client certificate must contain the database account used for connection. The PostgreSQL server will use CN to create the identity of the client.
TLS configuration
To summarize, we can now configure the PostgreSQL server to receive TLS connections:
ssl = on
ssl_cert_file = ‘/path/to/server.crt’
ssl_key_file = ‘/path/to/server.key’
ssl_ca_file = ‘/path/to/client-ca.crt’

The default here is on, but for security reasons, it’s always right to write it out

ssl_prefer_server_ciphers = on

TLS 1.3 provides the strongest security protection. SSL is recommended when controlling servers and clients_ min_ protocol_ version = ‘TLSv1.3’

All we need to reconfigure is the host based authentication file () for updating the PostgreSQL server. It can require all connections to use TLS and authenticate the client with X.509 certificate.

TYPE DATABASE USER ADDRESS METHOD

hostssl all all ::/0 cert
hostssl all all 0.0.0.0/0 cert
Now, the client connecting to the database server needs to provide a valid certificate signed by the client CA:
psql “host=postgres.example.com \

  user=alice \
  dbname=postgres \
  sslmode=verify-full \
  sslrootcert=/path/to/server-ca.crt \
  sslcert=/path/to/client.crt \
  sslkey=/path/to/client.key"

It should be noted that PSQL does not authenticate the server certificate by default, so we need to set “sslmode” to “” or “”. The specific setting depends on whether you use the host name of the CN field in X.509 to connect to the server.
In order to simplify instructions and avoid having to re-enter the path to TLS every time you connect to the database, you can use the connection service file of PostgreSQL. It can group connection parameters into “services” and reference them through the “service” parameter in the connection string.
Create the ” file with the following code:
[example]
host=postgres.example.com
user=alice
sslmode=verify-full
sslrootcert=/path/to/server-ca.crt
sslcert=/path/to/client.crt
sslkey=/path/to/client.key
Now, when connecting to the database, we only need to specify the service name and the database name we want to connect to:
psql “service=example dbname=postgres”
Database level security in PostgreSQL
role
So far, we have discussed the following points: how to protect the PostgreSQL database from unauthenticated network connections, how to use strong encryption for data transmission, and how to make the server and customers trust each other’s identity through common TLS authentication. Next, we will continue to analyze what users can do here, what they can access after connecting to the database, and how to verify their identity. This step is often referred to as “authorization”.
PostgreSQL has a complete set of user permission system established according to roles. In modern PostgreSQL (version 8.1 and above), “role” is a synonym for “user”. No matter what database account name you use, such as “user = Alice” in PSQL, it is actually a role that can connect to the database and has the login attribute. In other words, the following two instructions have the same effect:
CREATE USER alice;
CREATE ROLE alice LOGIN;
In addition to the login permission, roles can also have other attributes: those that can pass all permission checks, those that can create databases, those that can create other roles, and so on.
In addition to attributes, the permissions granted to roles can be divided into two categories: membership of other roles and permissions of database objects. Next, we will introduce how these two types work.
Grant role permissions
Suppose we need to track the server list:
CREATE TABLE server_inventory (

id            int PRIMARY KEY,
description   text,
ip_address    text,
environment   text,
owner         text,

);
By default, the PostgreSQL installation includes a super user role for booting the database, commonly referred to as “Postgres”. Using this role for all database operations is equivalent to using “root” login frequently in Linux system. It is never a good idea. Therefore, we want to create a role without permission and grant it the minimum permission as needed.
By creating a “group role” and authorizing other roles (one-to-one correspondence between roles and users) as members of the group, the trouble of assigning permissions to each user or role can be avoided. Let’s say that we want to grant developers Alice and Bob permission to view the server list but not allow them to modify it:
–Create a group role that has no login ability and grant it the right to select in the server list table
GRANT SELECT ON server_inventory TO developer;
–Create two user accounts and inherit the “developer” permission based on the login permission
CREATE ROLE alice LOGIN INHERIT;
CREATE ROLE bob LOGIN INHERIT;
–“Developer” group roles assigned to two user accounts
GRANT developer TO alice, bob;
Now, when connecting to the database, both Alice and Bob inherit the permissions in the “developer” group role and can query the database manifest table.
Permissions are valid for all columns of the table by default, but this can also be changed. Assuming that we only want interns to view the general information of the server, but do not want them to connect to the server, we can choose to hide the IP address:
CREATE ROLE intern;
GRANT SELECT(id, description) ON server_inventory TO intern;
CREATE ROLE charlie LOGIN INHERIT;
GRANT intern TO charlie;
Other common database object permissions include ”, ”, ”, and ”, which correspond to their respective SQL statements. We can also assign permissions to connect to a specific database, create a new schema or create new objects in the schema, execute functions, and so on. The chapters in the PostgreSQL documentation provide a complete list.
Row level security policy
A more advanced way of playing with the PostgreSQL permission system is row level security policy (RLS), which allows you to assign permissions to some rows in the table. This includes both rows that can be queried and rows that can be, and.
To use row level security, we need to prepare two things: enable RLS in the table and define a policy for controlling row level access.
Continuing with the previous example, suppose we just want to allow users to update their own servers. Then, the first step is to enable RLS in the table:
ALTER TABLE server_inventory ENABLE ROW LEVEL SECURITY;
If no policy is defined, PostgreSQL will default to the “reject” policy, which means that no role can access except the creator / owner of the table.
Row security policy is a Boolean expression, which is used by PostgreSQL to determine all rows that need to be returned or updated. The row returned by the select statement is checked against the expression specified by the using sub statement, while the row updated by the insert, update or delete statement is checked against the with check expression.
First, let’s define several policies that allow users to view all servers, but only update their own servers. This “self” is determined by the “owner” field in the table.
CREATE POLICY select_all_servers

ON server_inventory FOR SELECT
USING (true);

CREATE POLICY update_own_servers

ON server_inventory FOR UPDATE
USING (current_user = owner)
WITH CHECK (current_user = owner);

Note that only the owner of the table can create or update RLS.
audit
So far, we have mainly discussed the security measures of first hand defense. According to one of the basic security principles – defense in depth, we analyzed how these measures can help slow down the process of attackers attacking the system by superimposing each other.
Keeping accurate and detailed audit trail records is often neglected in system security attributes. Monitoring the network layer or node layer access of the database server is not within the scope of this article, but when it comes to the PostgreSQL server itself, we might as well see what options we have first.
When you want to see the situation in the database more clearly, the most common method is to enable detailed logging. Add the following command to the server configuration file to enable logging of all connection attempts and executed SQL.
; Record successful and unsuccessful connection attempts
log_connections = on
; Record terminated conversation
log_disconnections = on
; Record all executed SQL statements
log_statement = all
Unfortunately, for standard self managed PostgreSQL, this is almost all you can do without installing other plug-ins. Although it is always better than none, it is not extensible except for a few database servers and simple “grep”.
If you want a more advanced PostgreSQL audit solution, a third-party plug-in such as this is a good choice. Self managed PostgreSQL needs to be installed manually, but for some managed versions of PostgreSQL, such as AWS RDS, it is available. We just need to enable it.
Pgaudit provides more structure and granularity for recorded statements. But remember that it is not out of the scope of the log, that is, it may be difficult to transfer the audit log to the external Siem system in a structured format for more detailed analysis.
Certificate based access in PostgreSQL
(report for database access) is an open source project. With its help, we can implement all the best practices for protecting PostgreSQL and other databases described in this article.

epilogue
Like any system designed on the premise of security, to correctly protect the access to database instances requires protection measures at multiple levels of the network protocol stack. In this article, we start with network and transmission security and discuss how to use PostgreSQL’s flexible user permission system.

Recommended Today

VBS obtains the operating system and its version number

VBS obtains the operating system and its version number ? 1 2 3 4 5 6 7 8 9 10 11 12 ‘************************************** ‘*by r05e ‘* operating system and its version number ‘************************************** strComputer = “.” Set objWMIService = GetObject(“winmgmts:” _  & “{impersonationLevel=impersonate}!\\” & strComputer & “\root\cimv2”) Set colOperatingSystems = objWMIService.ExecQuery _  (“Select * from […]