SEPostgreSQL Administration

From PostgreSQL wiki
Jump to: navigation, search


This chapter introduces some of hints to administrate SELinux and SE-PostgreSQL.

  • List of chapters
  1. Introduction
  2. Architecture
  3. Specifications
  4. SELinux Overview
  5. Administration
  6. References
  7. Development

Installation and Setup

This section introduces two ways to install and setup SE-PostgreSQL on your system. The one is rpm installation, the other is build from the source tarball.

  • Linux kernel with SELinux enabled (2.6.24 or later)
  • selinux-policy 3.4.2 or later
  • policycoreutils 2.0.16 or later
  • libselinux 2.0.80 or later
  • checkpolicy (only installation from tarball)
  • libselinux-devel 2.0.80 or later (only installation from tarball)

Installation from RPM package

The SE-PostgreSQL package is now distributed via Fedora project. The sepostgresql package is available on Fedora 8 or later releases.

If you already set up a recent Fedora release on your system, the installation of SE-PostgreSQL is very simple, as follows:

[root@saba ~]# yum install sepostgresql
Loaded plugins: refresh-packagekit
Setting up Install Process
Parsing package install arguments
Resolving Dependencies
--> Running transaction check
---> Package sepostgresql.i386 0:8.3.4-2.1077.fc10 set to be updated
--> Processing Dependency: postgresql-server = 8.3.4 for package: sepostgresql
--> Running transaction check
---> Package postgresql-server.i386 0:8.3.4-1.fc10 set to be updated
--> Finished Dependency Resolution

Dependencies Resolved

 Package                 Arch       Version                 Repository    Size
 sepostgresql            i386       8.3.4-2.1077.fc10       rawhide       2.0 M
Installing for dependencies:
 postgresql-server       i386       8.3.4-1.fc10            rawhide       4.6 M

Transaction Summary
Install      2 Package(s)
Update       0 Package(s)
Remove       0 Package(s)

Total download size: 6.6 M
Is this ok [y/N]: y
Downloading Packages:
(1/2): sepostgresql-8.3.4-2.1077.fc10.i386.rpm                     | 2.0 MB     00:00
(2/2): postgresql-server-8.3.4-1.fc10.i386.rpm                     | 4.6 MB     00:01
Total                                                     1.3 MB/s | 6.6 MB     00:04
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing     : postgresql-server                                 [1/2]
  Installing     : sepostgresql                                      [2/2]

Installed: sepostgresql.i386 0:8.3.4-2.1077.fc10
Dependency Installed: postgresql-server.i386 0:8.3.4-1.fc10

If you don't use yum, you can get the package from Fedora mirrors:

The rpm installation script labels proper security context installed files and links its security policy module, so we don't need to do anything more.

At the next, you have to initialize the database cluster. In the default, SE-PostgreSQL deploys its database cluster at /var/lib/sepgsql, but it can be replaced by the configuration at /etc/sysconfig/sepostgresql.

The /etc/init.d/sepostgresql script with initdb initializes the database cluster, so it has to be completed before starting up server processes.

[root@saba ~]# service sepostgresql initdb
Initializing database:                            [  OK  ]

Then, it will get ready to start up server processes.

[root@localhost ~]# service sepostgresql start
Starting sepostgresql service:                    [  OK  ]

The initial databases are set up with sepgsql database role, add some more roles if necessary.

[root@localhost ~]# su - sepgsql
-bash-3.2$ createuser foo
Shall the new role be a superuser? (y/n)

Installation from source tarball

When we build and install SE-PostgreSQL from the source tarball, it needs a few more steps than RPM installation to work it correctly with SELinux, although most of them are also necessary to run PostgreSQL with SELinux.

Getting the sources

At first, download the PostgreSQL source tarball and the SE-PostgreSQL patch, from:

Then, extract it and apply the patch.

$ wget
$ wget
$ tar jxf postgresql-8.4beta2.tar.bz2
$ cd postgresql-8.4beta2
$ gunzip -c ../sepgsql-00-full-8.4beta2-r1911.patch.gz | patch -p1

Usual steps

On the configure script, you have to give the configure script --enable-selinux option to build it with SE-PostgreSQL feature.

$ ./configure --enable-selinux
$ make
$ make -C src/backend/security/sepgsql/policy

The sources of sepostgresql-devel.pp policy module are stored within src/backend/security/sepgsql/policy, so we need to run make independently on the directory. This policy module provides the booleans to turn on/off audit messages and to run the regression test correctly. If you cannot build them correctly, please report it to the author.

The make install should be run as root. The sepostgresql-devel.pp policy module can be installed using semodule command.

$ su
# make install
# /usr/sbin/semodule -i src/backend/security/sepgsql/policy/sepostgresql-devel.pp

In this memo, we assume the database cluster is deployed at /opt/sepgsql and owned by sepgsql.

# mkdir -p /opt/sepgsql
# chown sepgsql:users -R /opt/sepgsql


In the default, the configure script uses the /usr/local/pgsql as the top level directory of the installation. It mostly has a security context of system_u:object_r:usr_t:s0, so all the installed files inherit it, but incorrect.

Next, we need to assign appropriate a security context for each installed files. The /sbin/restorecon is a command to reset the security context to the default configuration in the security policy. The sepostgresql-devel.pp contains a set of pairs certain paths and its correct security context. The semanage enables to set up the default security context for the certain paths shown in regular expression. We have to give the default one for the database cluster because the policy module cannot know what path is preferable on build.

# /sbin/restorecon -R /usr/local/pgsql
# semanage fcontext -a -t postgresql_db_t '/opt/sepgsql(.*/)?'
# /sbin/restorecon -R /opt/sepgsql

If you would like to set up it without sepostgresql-devel.pp, confirm the following pair of certains paths and a security context. (It assumes /usr/local/pgsql as the prefix, and /opt/sepgsql as the database cluster.)

  • /usr/local/pgsql/bin/postgres should be labeled as postgresql_exec_t
  • /usr/local/pgsql/bin/initdb should be labeled as postgresql_exec_t
  • /usr/local/pgsql/bin/pg_ctl should be labeled as initrc_exec_t
  • rest of /usr/local/pgsql/bin/* should be labeled as bin_t
  • /usr/local/pgsql/lib/* should be labeled as lib_t
  • rest of /ust/local/pgsql/* should be labeled as usr_t
  • Files in database cluster should be labeled as postgresql_db_t
# semanage fcontext -a -t usr_t '/usr/local/pgsql(.*/)?'
# semanage fcontext -a -t bin_t '/usr/local/pgsql/bin(.*/)?'
# semanage fcontext -a -t lib_t '/usr/local/pgsql/lib(.*/)?'
# semanage fcontext -a -t postgresql_exec_t '/usr/local/pgsql/bin/postgres'
# semanage fcontext -a -t postgresql_exec_t '/usr/local/pgsql/bin/initdb'
# semanage fcontext -a -t initrc_exec_t '/usr/local/pgsql/bin/pg_ctl'
# /sbin/restorecon -R /usr/local/pgsql
# semanage fcontext -a -t postgresql_db_t '/opt/sepgsql(.*/)?'
# /sbin/restorecon -R /opt/sepgsql

Run initdb

At the last, run the initdb with --enable-selinux to initialize the database cluster with SE-PostgreSQL.

$ export PGDATA=/opt/sepgsql
$ /usr/local/pgsql/bin/initdb --enable-selinux
The files belonging to this database system will be owned by user "sepgsql".
This user must also own the server process.

The database cluster will be initialized with locale en_US.UTF-8.
The default database encoding has accordingly been set to UTF8.
The default text search configuration will be set to "english".

fixing permissions on existing directory /opt/sepgsql ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /opt/sepgsql/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.

Success. You can now start the database server using:

    /usr/local/pgsql/bin/postgres -D /opt/sepgsql
    /usr/local/pgsql/bin/pg_ctl -D /opt/sepgsql -l logfile start

Then, invoke the /usr/local/pgsql/bin/pg_ctl, not a /usr/local/pgsql/bin/postgres directly, because the default security policy does not allow user domain to translate to the server domain.

$ touch /opt/sepgsql/sepostgresql.log
$ chcon -t postgresql_log_t /opt/sepgsql/sepostgresql.log
$ pg_ctl -l /opt/sepgsql/sepostgresql.log start

If you collect server logs, the log files has to be labeled as postgresql_log_t.

GUC options

Now SE-PostgreSQL has two options to turn on/off its functionality.

sepostgresql = [on | off]
This options can be set only on the server starting up time.
It means SE-PostgreSQL is available, when it is on and the operating system enables SELinux.
Otherwise, SE-PostgreSQL is disabled, even if the binary was built with --enable-selinux.
The default value depends on the --enable-selinux option in initdb.
sepostgresql_row_level = [on | off] (default = on)
This option can be set only on the server starting up time.
It enables to turn on/off access controls on db_tuple object class.
The number of db_tuple class objects tends to be getting large, so if you want to reduce storage consumption a bit, you can turn off the option instead of security trade-off.
sepostgresql_mcstrans = [on | off] (default = on)
This option can be set everytime.
It enables to turn on/off mcstrans support on printing security contexts.

Labeled networks

As we mentioned before, SE-PostgreSQL applies the security context of the peer process as privileges of clients when it makes decisions on access controls. The libselinux provides getpeercon() API which enables to obtain the security context of the peer process for the given socket file descriptor. We don't need special configurations for the connection come from UNIX domain socket (local connection), however, an additional configurations called as "Labeled networks" are necessary for the connection come from TCP/IP socket (remove connection).


The labeled networks is an enhancement of IPsec. When we open a connection on IPsec'ed communication channel, an enhanced key exchange daemon (racoon) send an encryption key and the security context of the process to the peer side. The kernel working on the peer side stores the information, and returns it applications via getpeercon(). In addition, SELinux also provides a way to assign an alternative security context when labeled ipsec is not available. It is called as static fallback context.

Labeled IPsec

This section introduces the way to set up the labeled ipsec between two hosts, which is the simplest case.


The labeled networks requires the following packages.

  • Linux kernel, labeled networks enabled
  • ipsec-tools-0.6.5-6, or later

A kernel config option of CONFIG_SECURITY_NETWORK has to be enabled. This option is enabled on the recent kernel packages at Red Hat Enterprise Linux 5, Fedora 7 or later and so on.

Red Hat provides a well document, see the following document for more information.


At the following example, SE-PostgreSQL server process works on, and the database client process works on They can communicate each other without any proxies, routers and so on.

Add entries to SPD (Security Policy Database)

At first, we have to inform communications between and should be transported via IPsec tunnels. The following example requires outbound packets to from and its reverse should be IPsec'ed with ESP mode, and both of SPD entries has system_u:object_r:ipsec_spd_t:s0 as its security context.

Make a file to describe the following configuration at the server and client side. Please note the enumerated IP addresses are reversed at the client side.

Server side (

spdadd any
-ctx 1 1 "system_u:object_r:ipsec_spd_t:s0"
-P out ipsec esp/transport//require;

spdadd any
-ctx 1 1 "system_u:object_r:ipsec_spd_t:s0"
-P in ipsec esp/transport//require;

The setkey command enables to load the configuration as follows:

# setkey -f <SPD configuration file>

Edit the /etc/racoon/racoon.conf

At the next, edit the /etc/racoon/racoon.conf to add the configuration block to communicate between and In this example, we use pre-shared-key method to authenticate the remote host for simplification of explanation. Please note the enumelated IP addresses are reversed at the client side.

Server side (

# Racoon IKE daemon configuration file.
# See 'man racoon.conf' for a description of the format and entries.

path include "/etc/racoon";
path pre_shared_key "/etc/racoon/psk.txt";
path certificate "/etc/racoon/certs";
path script "/etc/racoon/scripts";

sainfo anonymous
        #pfs_group 2;
        lifetime time 1 hour ;
        encryption_algorithm 3des, blowfish 448, rijndael ;
        authentication_algorithm hmac_sha1, hmac_md5 ;
        compression_algorithm deflate ;

        exchange_mode aggressive, main;
        my_identifier address;
        proposal {
                encryption_algorithm 3des;
                hash_algorithm sha1;
                authentication_method pre_shared_key;
                dh_group 2 ;

Edit the /etc/racoon/psk.txt

At the next, we add an entry of pre-shared-key. The key-phrase has to be common between the server and client side.

Server side (

# file for pre-shared keys used for IKE authentication
# format is:  'identifier' 'key'
# For example:
#             flibbertigibbet
#      12345
#  micropachycephalosaurus            somethingsecrettext

Start racoon

Start the racoon daemon at the both of sides.

# service racoon start
Starting racoon: [ OK ]

If your configuration is correct, the security context of the peer process is delivered to the server process, and SE-PostgreSQL applies it as the privileges of client. We can confirm it using sepgsql_getcon() function.

Static fallback labels

Labeled networks enables to deliver the security context of peer processes, we recommend to apply the feature as far as possible. However, it also has a restriction that SELinux has to be enabled on the client side, so it makes impossible client processes to work on any other operating system. They don't assign processes a security attribute fundamentally.

SELinux provides an alternative way to assign a security context of client, called as static fallback labels. It associates a connection an alternative security context based on the source IP address and inbounding network device, when the labeled networks is unavailable.

It requires the following packages:

  • Linux kernel v2.6.25 or later
  • netlabel_tools v0.18 or later

In this example, we intend to assign an alternative security context staff_u:staff_r:staff_t:s0:c1 for connections come from via eth0, staff_u:staff_r:staff_t:s0:c2 for connections come from via eth1, and user_u:user_r:user_t:s0 for elsewhere.


The netlabelctl with unlbl sub-command enables to set up the configuration we intend. We can give an alternative security context for a network address with net-mask and a network interface. If it does not care inbounding network device, default should be placed instead.

# netlabelctl unlbl add interface:eth0 address: ¥
# netlabelctl unlbl add interface:eth1 address: ¥
# netlabelctl unlbl add default address: ¥

Backup and restore

PostgreSQL provides official backup utilities, pg_dump and pg_dumpall. The latest version of them has --security-label option to backup databases with security context. We can restore them via pg_restore command without any specific option.

No need to say, the backup and restore process is not an exception for access controls. The database administrator should have enough privileges to refer and restore them. If not so, the backup is aborted or violated tuples are filtered.

An example output:

$ pg_dump --security-label postgres
-- Name: customer; Type: TABLE; Schema: public; Owner: kaigai; Tablespace:

CREATE TABLE customer (
    cid integer NOT NULL,
    cname character varying(32),
    credit character varying(32) SECURITY_LABEL = 'system_u:object_r:sepgsql_secret_table_t:s0'
) SECURITY_LABEL = 'unconfined_u:object_r:sepgsql_table_t:s0';

ALTER TABLE public.customer OWNER TO kaigai;
-- Data for Name: customer; Type: TABLE DATA; Schema: public; Owner: kaigai

COPY customer (security_label, cid, cname, credit) FROM stdin;
unconfined_u:object_r:sepgsql_table_t:s0        10      jack    1111-2222-3333-4444
unconfined_u:object_r:sepgsql_table_t:s0        13      adam    5555-6666-7777-8888
unconfined_u:object_r:sepgsql_table_t:s0        14      liza    9876-5432-1098-7654

Reclaim orphan labels

The text representation of security contexts are managed with pg_security system catalog (or pg_shsecurity for shared database objects).

Security context has a characteristic that massive number of objects shares a limited number of security contexts in most cases. Because of the performance reason, we don't use reference counter on pg_security, so it is necessary to reclaim orphan security context later, if you cannot ignore increasing of orphan entries.

SE-PostgreSQL provides a function to reclaim orphan security context, named security_reclaim_label. When we invoke it without any argument, it tries to reclaim all the orphan entries in the current database. Otherwise, it can accept an argument to narrow down the entries referred by a certain table.

Only database superuser can execute this function.

postgres=# select security_reclaim_label('t1');
NOTICE:  secattr="unconfined_u:object_r:sepgsql_table_t:s0", secid=16402 on public.t1 was reclaimed
NOTICE:  secattr="system_u:object_r:sepgsql_ro_table_t:s0", secid=16403 on public.t1 was reclaimed
NOTICE:  secattr="unconfined_u:object_r:sepgsql_table_t:s0:c1", secid=16404 on public.t1 was reclaimed
NOTICE:  secattr="unconfined_u:object_r:sepgsql_table_t:s0:c2", secid=16405 on public.t1 was reclaimed
NOTICE:  secattr="system_u:object_r:sepgsql_ro_table_t:s0:c3", secid=16406 on public.t1 was reclaimed
NOTICE:  secattr="system_u:object_r:sepgsql_ro_table_t:s0:c4", secid=16407 on public.t1 was reclaimed
(1 row)

This example shows a case when we reclaim any orphan entries referred by public.t1.

postgres=# select security_reclaim_label();
NOTICE:  secattr="unconfined_u:object_r:sepgsql_table_t:s0", secid=16412 on public.t2 was reclaimed
NOTICE:  secattr="unconfined_u:object_r:sepgsql_table_t:s0:c1", secid=16414 on public.t2 was reclaimed
NOTICE:  secattr="unconfined_u:object_r:sepgsql_table_t:s0:c2", secid=16415 on public.t2 was reclaimed
NOTICE:  secattr="unconfined_u:object_r:sepgsql_table_t:s0", secid=16413 on public.t3 was reclaimed
NOTICE:  secattr="unconfined_u:object_r:sepgsql_table_t:s0:c1", secid=16416 on public.t3 was reclaimed
NOTICE:  secattr="unconfined_u:object_r:sepgsql_table_t:s0:c2", secid=16417 on public.t3 was reclaimed
(1 row)

If we don't gives any certain table on the function, it reclaims all the orphan entries. But this option has less lock-granularity, and may block other transaction during reclaims.

Desirable frequency of the reclaim depends on environment, but, we expect bimonthly reclaim shall be enough to maintain security labels.