DBMS - Data Security

During the implementation of a new project, the customer often asks the question of how the implemented DBMS is protected. One of the probable answers (incorrect in my opinion): "The database is located in the internal perimeter of the computer network and is not accessible to the attacker." According to statistics, insiders are more dangerous, since they have the opportunity to legitimately investigate vulnerabilities in the services provided.

I suggest trying to independently configure the existing functionality that allows you to increase the level of security of your system.

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

1. Software update.

We definitely need to try to install the latest version of software or special patches to close 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 a restart of the DBMS service.

2. Setting custom 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 port knocking .

3. Limit the number of possible connections

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

listen_addresses = 'ip_1, ip_2, ip_3'

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

max_connections = '4'

In the pg_hba.conf file, we remove the host , hostnossl, and local entries (the latter if the Unix domain sockets are not used). Leave \ install only hostssl .

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

In the METHOD field, write the type of user authentication + additional options. In my example, we check the validity of users using 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 people’s direct access is not used, then I would set the value to “1s” - enough for the robot to enter the correct password information, but not enough for a full brute force. We hide PostgreSQL user passwords with MD5:

password_encryption = 'on'

We require that in case of a successful connection, the DBMS should check the user access to the database. If this setting is activated, users will have to be created in the format <user name> @ <work database name>.

db_user_namespace = 'on'

If we want to use GSSAPI authentication - install:

krb_server_keyfile = 'файл_гсcапи'
krb_caseins_users = 'on' 

Note: Names become case sensitive. When using this setting, users need to create names in the format <user name> @ <domain name>. Accordingly, db_user_namespace must be switched off .

5. Use of architectural features

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

Although the default REPEATABLE READ isolation level in Postgres is more stringent than 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 level of transactions in the session. Thus, you can keep a log of changes made to the DBMS.

6. Data channel encryption

Turn on ssl - we protect the efficiency between PostgreSQL and the client:

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

The ! ANULL parameter prohibits the entry 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'

We generate certificates for SSL - it is possible according to this instruction . We consider that Postgres requires the identity of the name of the issued certificate and the name of the user who makes the connection. If we use Windows, then you can install OpenSSL on it and execute the same commands. The default certificates must be in PGDATA:

• certificate of our DBMS (without key):
  
  

  ssl_cert_file = 'серт.crt'	
  

  
  
• DBMS key:
  
  

  ssl_key_file = 'ключ.key'
  

  
  
• if we want to track the chain of certificates, then add the certificate of the certification center (CA):
  
  

  ssl_ca_file = 'ваш_са.crt'	
  

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

  ssl_crl_file = 'ваш_сос.crl'
  

We restart the postgresql service, check for errors. On the user side, we establish a chain of certificates. You can check the connection using the psql utility with the following 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: Starting with version 9.5, a string 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.