Introducing PostgreSQL and sepgsql – SELinux

July 01, 2021

PostgreSQL is a popular, featureful, and mature relational database management system. Like Apache, it also enables a modular extension of its functionalities through loadable modules. The module we will investigate is called sepgsql, shorthand for Security Enhanced PostgreSQL or SEPostgreSQL. Through sepgsql, PostgreSQL enhances itself with SELinux support for additional access controls, offering fine-grained data flow controls based on SELinux policy rules.

Please be aware though that sepgsql does not implement a full mandatory access control system within PostgreSQL, as not all PostgreSQL statements will result in a policy check. While it augments the security posture of the PostgreSQL database, the module has a few limitations listed in its online documentation, available at https://www.postgresql.org/docs/10/sepgsql.html.

Reconfiguring PostgreSQL with sepgsql

Before we can install sepgsql, we need to have a working PostgreSQL system at our disposal. Most Linux distributions have readily available tutorials on how to deploy PostgreSQL, which often involves creating the databases associated with it.

In this chapter, we will assume that the database itself is available inside /var/lib/pgsql/data, the default location for a CentOS-based PostgreSQL installation. The PostgreSQL configuration files are also located inside this location.

To install sepgsql, the following steps should be executed:

  • Let’s first see whether the database is functioning properly by logging in as the (default) postgres superuser, and listing the currently available databases:
# su postgres -
$ psql postgres
psql (10.6)
Type "help" for help.
postgres=# \l
 List of databases
 Name | Owner | Encoding | ...
-----------+----------+-------------+ ...
 postgres | postgres | UTF8 | ...
 template0 | postgres | UTF8 | ...
 template1 | postgres | UTF8 | ...

If at any point a failure occurs, check the log file inside /var/lib/pgsql/data/log to get more information. This log file is the default log file for all PostgreSQL-related activities, as we will see when troubleshooting its SELinux support in the Troubleshooting sepgsql section.

  • Assuming PostgreSQL is working properly, let’s configure it to use the sepgsql module. This module is part of the contributed modules within PostgreSQL, and is maintained by the PostgreSQL community. In CentOS, the sepgsql module is part of the postgresql-contrib package, which can be easily added to the system using yum install postgresql-contrib if it is not present yet.
  • Edit the postgresql.conf file inside /var/lib/pgsql/data and search for the shared_preload_libraries statement. By default, it will be commented out, so uncomment it and add sepgsql inside:
shared_preload_libraries = 'sepgsql' # (change requires restart)
  • As mentioned, changing this parameter requires restarting the database. We will do that later, but first, we will shut down the database as our next steps will require an offline database:
# systemctl stop postgresql
  • Next, we need to reconfigure all databases and enable the sepgsql-related functions. We will cover these functions in the Using sepgsql specific functions section. To enable the functions, we have to become the postgres superuser again, and for each database available, we load a specific SQL file:
# su postgres -
$ export PGDATA=/var/lib/pgsql/data
$ for DBNAME in template0 template1 postgres; do 
 postgres --single -F -c exit_on_error=true $DBNAME < /usr/share/pgsql/contrib/sepgsql.sql > /dev/null;
 done

The databases listed in the example are the three default databases available in a brand-new installation. You can obtain the actual list of databases on the system through PostgreSQL’s shorthand \l command, which we used earlier to check whether the database is functioning properly.

  • Let’s validate whether sepgsql is working by starting the PostgreSQL database, logging in to PostgreSQL, and asking for our current context:
# systemctl start postgresql
# su postgres -
$ psql postgres
...
postgres# SELECT sepgsql_getcon();
 sepgsql_getcon()
-----------------------------------------------------
unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

What we did here was to execute the freshly installed sepgsql function sepgsql_getcon(), which retrieves the current context for the session.

Let’s further configure the database with a test account that we can use to validate the sepgsql controls.

Creating a test account

To validate whether the sepgsql controls are working, we should have a test account outside of the postgres superuser, and a local user that we can map to different SELinux contexts. As the SELinux context will heavily decide which privileges are associated with a session, we want to be able to show the impact of one context compared to the others.

First, inside PostgreSQL (with the postgres superuser), create a test account called testuser, and allow the account to authenticate with a given password:

postgres=# CREATE USER testuser PASSWORD 'somepassword';

We also need to configure the database to allow password-based authentication (as the default PostgreSQL setup will use system trust or another means of authentication). To accomplish that, edit the pg_hba.conf file inside /var/lib/pgsql/data with the following settings:

local		all	postgres					peer
local		all	testuser					md5
host		all	testuser		127.0.0.1/32	md5
host		all	testuser		192.168.100.0/24	md5

The pg_hba.conf file manages the host-based authentication rules for PostgreSQL. We update it to allow password-based authentication for the testuser account (which uses md5 as an identifier) while allowing the postgres superuser to continue to authenticate using peer trust.

With these changes in place, PostgreSQL allows password-based authentication of the testuser account both when the user initiates the communication over a local, socket-based interaction, as well as when a network-based communication is used.

We also need to tell the SELinux policy that regular users will be allowed to connect to the PostgreSQL service:

# setsebool -P selinuxuser_postgresql_connect_enabled on

While this would be sufficient for accessing the PostgreSQL service, it is not adequate to allow the regular user domain (user_t) to interact with sepgsql. To accomplish that, we need to adjust the SELinux policy so that the user_t domain is also associated with the sepgsql_client_type attribute, and that the user_r role can have the sepgsql-related types active.

We do this through a small CIL policy, as follows:

(typeattributeset cil_gen_require sepgsql_client_type)
(typeattributeset cil_gen_require user_t)
(typeattributeset cil_gen_require sepgsql_trusted_proc_t)
(typeattributeset cil_gen_require sepgsql_ranged_proc_t)
(typeattributeset sepgsql_client_type (user_t))
(roleattributeset cil_gen_require user_r)
(roletype user_r sepgsql_trusted_proc_t)
(roletype user_r sepgsql_ranged_proc_t)

It is also possible to accomplish this with a reference policy style module, as follows:

policy_module(local_sepgsql, 1.0)
gen_require(`
	role user_r;
	type user_t;
')
postgresql_role(user_r, user_t)

Assuming we stick with the CIL-based policy, let’s load the file (that is, local_sepgsql.cil) as an SELinux policy module:

# semodule -i local_sepgsql.cil

Don’t forget to restart the PostgreSQL service after changing the pg_hba.conf file.

Tuning sepgsql inside PostgreSQL

The sepgsql module introduces two configuration parameters that can be used to tweak sepgsql inside PostgreSQL:

  • The sepgsql.permissive parameter tells PostgreSQL not to enforce the SELinux policy rules inside PostgreSQL. This is similar to the permissive state of SELinux on the system, but covers the sepgsql-related functionality inside PostgreSQL alone.
  • The sepgsql.debug_audit parameter tells PostgreSQL to always log the SELinux-related decisions, even when they are to allow a statement to be processed. This is similar to the auditallow statements for SELinux on the system.

It is very important however to understand that sepgsql is a user-space object manager, the SELinux subsystem in the Linux kernel is not used for enforcing the access controls, only sepgsql is. The only purpose that the SELinux subsystem has is to allow PostgreSQL to query the active SELinux policy or obtain current SELinux context information.

Hence, the previous configuration parameters work mostly independently of the configuration of the system. While SELinux must be active on the system, it does not need to be in enforcing mode to have sepgsql enforce the rules inside PostgreSQL, nor does a permissive SELinux system make the enforcement of sepgsql permissive as well.

The sepgsql.debug_audit parameter does have some relationship with the system policy. We can add auditallow statements to the SELinux policy to force the logging of events even when they are allowed. What the sepgsql.debug_audit parameter does is force all events to be logged, something useful for troubleshooting sepgsql, as we will see next.

Troubleshooting sepgsql

Let’s enable the debug statements for an individual session and reinvoke the sepgsql_getcon function again:

# su postgres -c "/usr/bin/psql postgres"
postgres=# SET sepgsql.debug_audit = true;
SET
postgres=# SELECT sepgsql_getcon();
...

If you want to enable the configuration for the entire system, you can place the configuration inside the postgresql.conf file:

postgresql.conf

sepgsql.debug_audit = true

Inside the PostgreSQL logs, we will notice the following information:

STATEMENT: SET sepgsql.debug_audit = true
STATEMENT: SELECT sepgsql_getcon();
LOG: SELinux: allowed { execute } \
 scontext=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023 \
 tcontext=system_u:object_r:sepgsql_proc_exec_t:s0 \
 tclass=db_procedure name="pg_catalog.sepgsql_getcon()"

The first two lines log the statements that we have executed within the session, whereas the third line is the SELinux log event related to the execution of sepgsql_getcon.

The event tells us that the unconfined_t domain (source context) has attempted (and succeeded) to execute the database procedure (as indicated by the db_procedure class) labeled with the sepgsql_proc_exec_t type. The in-database function is the sepgsql_getcon function within the pg_catalog schema.

If a denial occurs, this will result in a similar event in the logs, but will also be made visible to the end user that triggered the denial, as PostgreSQL will show an error message like so:

ERROR: SELinux: security policy violation

Unlike the audit logging executed by, for instance, D-Bus (which results in USER_AVC events in the regular audit log), sepgsql will follow the log configuration of the PostgreSQL database itself, so keep a close eye out on this log file (or other log targets configured in PostgreSQL) when trying to troubleshoot sepgsql.

In this simple example, you might already have noticed that the event references a database-specific class (db_procedure). In the next section, we will look into the various classes, permissions, and types associated with sepgsql and thus supported by the SELinux policy.

Related Articles

How to add swap space on Ubuntu 21.04 Operating System

How to add swap space on Ubuntu 21.04 Operating System

The swap space is a unique space on the disk that is used by the system when Physical RAM is full. When a Linux machine runout the RAM it use swap space to move inactive pages from RAM. Swap space can be created into Linux system in two ways, one we can create a...

read more

Lorem ipsum dolor sit amet consectetur

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

8 − 2 =