DBMS - Data Security

During the implementation of a new project, the customer often asks a question about how the implemented DBMS is protected. One of the likely answers (wrong in my opinion): "The database is located in the inner perimeter of the computer network and is inaccessible to the attacker." According to statistics, insiders are more dangerous because they have the ability to legitimately investigate vulnerabilities in the services provided.

I propose to try to customize the existing functionality, allowing you to increase the level of security of your system.

Preface: I was assigned the task of writing a utility that should check the PostgreSQL settings. In this article I want to share the analytics of the capabilities of this DBMS used to prevent unauthorized access.

1. Software update.

Definitely, you should try to install the latest version of the software or special patches to close the vulnerabilities discovered by the Internet community . At the time of writing, this is 9.5.4. Next, we will change the parameters in the postgresql.conf file from the PGDATA directory, since all of them require restarting the DBMS service.
')

2. Installation of non-standard settings

Change the connection port:

port = '5333' 

Of course, nmap will detect the postgres service, but detecting an active scan on the network is easier than pinpointing the target host. If desired, you can use the port knocking .

3. Limiting the number of possible connections

Explicitly specify ip-users. Users are not often given access to writing SQL queries, so we limit the number of productive servers:

 listen_addresses = 'ip_1, ip_2, ip_3' 

Change the maximum number of simultaneous connections (+1 for superuser or replication). The default value is 100 (apparently based on working with the web server), but if you have a standard bundle with the 1C pool, then install:

 max_connections = '4' 

In the pg_hba.conf file, we remove the records host , hostnossl and local (the latter - if you are not using Unix domain sockets). We leave \ install only hostssl .

We remove the standard postgres account and all parameters: for DATABASE, specify the specific database name, and for USER, the name of the user who is allowed to connect.

In the field METHOD we write the type of user authentication + additional options. In my example, we check the validity of users for SSL certificates, that is, we add the cert parameter. We get the lines of the form:

 hostssl test_database test_user 192.168.23.2/24 cert 

In general, there is a lot of freedom for imagination, since postgres implemented compatibility with GSS, SSPI, IDENT, LDAP, RADIUS and PAM.

4. The complication of password selection.

Limit the time for authentication to the DBMS:

 authentication_timeout = '1s' 

If no direct access is used by humans, then I would set the value “1s” - enough for the robot to enter the correct password information, but not enough for a full-fledged brute force. Using MD5, we hide PostgreSQL user passwords:

 password_encryption = 'on' 

We demand that, in case of successful connection, the DBMS should check the user's access to the database. If this setting is activated, users will have to be created in the format <username> @ <working_BD_name>.

 db_user_namespace = 'on' 

If you want to use GSSAPI authentication, we install:

 krb_server_keyfile = '_c' krb_caseins_users = 'on' 

Note: Names are case-sensitive. When using this setting, users need to create names in the format of <username> @ <domain_name>. Accordingly, db_user_namespace needs to be switched to off mode.

5. Use of architectural features

If you use replication, you can limit the number of replicants with the parameters max_wal_senders = 2 and max_replication_slots = 2 . A potential intruder, even if he gets access to the database, will not be able to immediately download all the backup data, provided that your replicas are in working condition.

Although the Postgres REPEATABLE READ default isolation level in Postgres is more stringent than what ISO / IEC 9075 requires, the same SQL standard recommends using:

 default_transaction_isolation = 'serializable' 

You can set default_transaction_read_only = on , and then create a trigger that will trigger a change in the transaction level in the session. Thus, you can keep a log of changes to the database.

6. Data Channel Encryption

Turn ssl on - protect efficiency between PostgreSQL and client:

 ssl = 'on' ssl_ciphers = 'HIGH:+3DES:!aNULL' 

The parameter ! ANULL denies the entrance of anonymous users. Just in case, we explicitly indicate that our server will dictate its rules when establishing a secure connection (by default, it works):

 ssl_prefer_server_ciphers = 'on' 

Generate certificates for SSL - please follow this instruction . We take into account that Postgres requires the identity of the issued certificate and the name of the user who makes the connection. If you use Windows, then you can put OpenSSL on it and execute the same commands. Default certificates should be in PGDATA:

• certificate of our DBMS (without a key):
  
  

   ssl_cert_file = '.crt' 

  
  
• key for DBMS:
  
  

   ssl_key_file = '.key' 

  
  
• if we want to track certificate chains, then we add a certificate from a certification authority (CA):
  
  

   ssl_ca_file = '_.crt' 

  
  
• when you activate the previous item, you can track the list of revoked certificates (COC):
  
  

   ssl_crl_file = '_.crl' 

Restart the postgresql service, check for errors. On the user side, we install a certificate chain. You can check the connection correctness using the psql utility with parameters:

 psql -U test_user sslcert=test_user.crt 

Conclusion in case of successful connection:

 SSL- (: TLSv1.2, : ECDHE-RSA-AES256-GCM-SHA384, : 256) 

Afterword: Beginning with version 9.5, the row security policy has been added. About her and the use of native data encryption in the database I hope I can write a separate text.