Data protection and security are essential for the continuity of business. Data protection is not recommended, but it is required by the legal system. Sensitive data, such as user information, email addresses, geographical addresses, and payment information, should be protected against any data breach. There are several other topics related to data security, such as data privacy, retention, and loss prevention. In this article, we will look at authentication best practices in PostgreSQL including PostgreSQL host-based authentication, and proxy authentication strategies.
There are several levels of data protection, often defined in the data protection policy and by the country’s legal system. A data protection policy often defines data dissemination to other parties, users authorized to access the data, and so on. Data should be protected on different levels, including transferring and encrypting data on storage devices. Data security is a huge topic and often there are data security managers dedicated only to these tasks.
This article is an excerpt taken from the book ‘Learning PostgreSQL 11 – Third Edition’ by Andrey Volkov, and Salahadin Juba. The book explores the latest features in PostgreSQL 11 and will get you up and running with building efficient PostgreSQL database solutions from scratch.
Authentication answers the question: Who is the user? PostgreSQL supports several authentication methods, including the following:
There are other authentication methods not covered; the full list of supported authentication methods can be found on the PostgreSQL website.
To understand authentication, you need to have the following information:
psql -U postgres -c "SELECT pg_reload_conf();"
sudo service postgresql reload
sudo /etc/init.d/postgresql reload
sudo Kill -HUP <postgres process id>
sudo systemctl reload postgresql-11.service
As in postgresql.conf, the pg_hba.conf file is composed of a set of records, lines can be commented using the hash sign, and spaces are ignored. The structure of the pg_hba.conf file record is as follows:
host_type database user [IP-address| address] [IP-mask] auth-method [auth-options]
The host_type part of this query can be the following:
The database part of the query is the name of the database that the user would like to connect to. For flexibility, you could also use a comma-separated list to specify several databases, or you could use all to indicate that the user can access all the databases in the database cluster. Also, the same user and same role values can be used to indicate that the database name is the same as the username, or the user is a member of a role with the same name as the database.
The user part of the query specifies the database user’s name; again, the all value matches all users. The IP address, address, and IP subnet mask are used to identify the host from where the user tries to connect. The IP address can be specified using a Classless Inter-Domain Routing (CIDR) or dot-decimal notation. Finally, the password authentication methods can be trusted, MD5, reject, and so on.
The following are some typical examples of configuring a PostgreSQL authentication:
#TYPE DATABASE USER ADDRESS METHOD
Local all all trust
#TYPE DATABASE USER ADDRESS METHOD
Host all all 127.0.0.1/32 trust
host all all ::1/128 trust
#TYPE DATABASE USER ADDRESS METHOD
Host all all 192.168.0.53/32 reject
Host all all 192.168.0.1/24 trust
PostgreSQL provides a very convenient way to view the rules defined in the pg_hba.conf file by providing a view called pg_hba_file_rules as follows:
postgres=# SELECT row_to_json(pg_hba_file_rules, true) FROM pg_hba_file_rules limit 1;
row_to_json
-------------------------
{"line_number":84, +
"type":"local", +
"database":["all"], +
"user_name":["all"], +
"address":null, +
"netmask":null, +
"auth_method":"trust",+
"options":null, +
"error":null}
(1 row)
The listen_addresses option is defined in postgresql.conf. The PostgreSQL listen_addresses connection setting is used to identify the list of IP addresses that the server should listen to from client applications. The listen_addresses are comma-separated lists of hostnames or IP addresses. Changing this value requires a server restart. In addition, the following should be noted:
Connection refused
Is the server running on host <host_ip> and accepting
TCP/IP connections on port 5432?
Authentication best practices depend on the whole infrastructure set up, the application’s nature, the user’s characteristics, data sensitivity, and so on. For example, the following setup is common for start-up companies: the database application, including the database server, is hosted on the same machine and only used from one physical location by intracompany users.
Often, database servers are isolated from the world using firewalls; in this case, you can use the SCRAM-SHA-256 authentication method and limit the IP addresses so that the database server accepts connections within a certain range or set. Note that it is important not to use a superuser or database owner account to connect to the database because if this account was hacked, the whole database cluster would be exposed.
If the application server—business logic—and database server are not on the same machine, you can use a strong authentication method, such as LDAP and Kerberos. However, for small applications where the database server and application are on the same machine, the SCRAM-SHA-256 authentication method and limiting the listen to address to the localhost might be sufficient.
To authenticate an application, it is recommended to use only one user and try to reduce the maximum number of allowed connections using a connection pooling software to better tune the PostgreSQL resources. Another level of security might be needed in the application of business logic to distinguish between different login users. For real-world users, LDAP or Kerberos authentication is more desirable.
Furthermore, if the database server is accessed from the outer world, it is useful to encrypt sessions using SSL certificates to avoid packet sniffing.
You should also remember to secure database servers that trust all localhost connections, as anyone who accesses the localhost can access the database server.
Often, when designing an application, a login role is used
The database’s role system can also be used to partially implement this logic by delegating the authentication to another role after the connection is established or reused, using the SET SESSION AUTHORIZATION statement or SET ROLE command in a transaction block, as follows:
postgres=# SELECT session_user, current_user;
session_user | current_user
--------------+--------------
postgres | postgres
(1 row)
postgres=# SET SESSION AUTHORIZATION test_user;
SET
postgres=> SELECT session_user, current_user;
session_user | current_user
--------------+--------------
test_user | test_user
(1 row)
The SET ROLE requires a role membership, while SET SESSION AUTHORIZATION requires superuser privileges. Allowing an application to connect as a superuser is dangerous because the SET SESSION AUTHORIZATION and SET ROLE commands can be reset using the RESET ROLE and RESET SESSION commands, respectively, thereby allowing the application to gain superuser privileges.
To understand how the PostgreSQL role system can be used to implement authentication and authorization, we will use the role system and the car portal application. In the car portal application, several groups of users can be classified as web_app_user, public_user, registered_user, seller_user, and admin_user. The web_app_user is used to configure business logic connection tools; the public_user, registered_user, and seller_user are used to distinguish users. The public_user group can access only public information, such as advertisements, but cannot add ratings as registered_user nor create advertisements, since seller_user. admin_user is a super role to manage all of the application’s content, such as filtering out spams and deleting the users that do not adhere to the website’s policies. When the car web portal application connects to the database, the web_app_user user is used. After this, car_portal invokes the SET ROLE command based on the user class. This authentication method is known as proxy authentication.
The following examples demonstrate how a role system can be used to implement proxy authentication. The first step is to create roles and assign role memberships and privileges, as follows:
CREATE ROLE web_app_user LOGIN NOINHERIT;
CREATE ROLE public_user NOLOGIN;
GRANT SELECT ON car_portal_app.advertisement_picture, car_portal_app.advertisement_rating , car_portal_app.advertisement TO public_user;
GRANT public_user TO web_app_user;
GRANT USAGE ON SCHEMA car_portal_app TO web_app_user, public_user;
The NOINHERIT option for the web_app_user does not allow the user to inherit the permissions of role membership; however, web_app_user can change the role to a public user, as in the following example:
$ psql car_portal -U web_app_user
car_portal=> SELECT * FROM car_portal_app.advertisement;
ERROR: permission denied for relation advertisement
car_portal=> SET ROLE public_user;
SET
car_portal=> SELECT * FROM car_portal_app.advertisement;
advertisement_id | advertisement_date | car_id | seller_account_id
------------------+--------------------+--------+-------------------
(0 rows)
car_portal=> SELECT session_user, current_user;
session_user | current_user
--------------+--------------
web_app_user | public_user
(1 row)
In this article, we looked at several authentication methods in PostgreSQL such as password and trust. Finally, we looked at the role system and proxy authentication. If you enjoyed reading the article and want to learn more, be sure to check out the book ‘‘Learning PostgreSQL 11 – Third Edition’.
How to handle backup and recovery with PostgreSQL 11 [Tutorial]
Handling backup and recovery in PostgreSQL 10 [Tutorial]
Understanding SQL Server recovery models to effectively backup and restore your database
I remember deciding to pursue my first IT certification, the CompTIA A+. I had signed…
Key takeaways The transformer architecture has proved to be revolutionary in outperforming the classical RNN…
Once we learn how to deploy an Ubuntu server, how to manage users, and how…
Key-takeaways: Clean code isn’t just a nice thing to have or a luxury in software projects; it's a necessity. If we…
While developing a web application, or setting dynamic pages and meta tags we need to deal with…
Software architecture is one of the most discussed topics in the software industry today, and…