NB SQL 9.0

From SELinux Wiki
Jump to: navigation, search

SELinux PostgreSQL Support (9.0)

This section gives an overview of the SE-PostgreSQL version 9.0.1 extensions to support SELinux in F-14 and how the database context information is managed. It assumes some basic knowledge of PostrgreSQL that can be found at the following web site:

http://wiki.postgresql.org/wiki/Main_Page

For a more in-depth overview of SE-PostgreSQL the "Security-Enhanced PostgreSQL Security Wiki" is recommended, however some areas require updating to reflect SE-PostgreSQL version 9.0.

SE-PostgreSQL Overview

SE-PostgreSQL adds SELinux mandatory access controls (MAC) to database objects such as databases, tables, columns, rows (tuples), procedures and blobs (binary large objects). Figure 1 shows a simple database with one table, two columns and three rows, each with their object class and associated security context. The database object classes and permissions are described in the Object Classes and Permissions section.

database (db_database)
security_label = 'unconfined_u:object_r:sepgsql_db_t:s0:c999'
table (db_table)
security_label = 'unconfined_u:object_r:sepgsql_table_t:s0:c10'
column 1 (db_column)
security_label = 'unconfined_u:object_r:sepgsql_table_t:s0:c20'
column 2 (db_column)
security_label = 'unconfined_u:object_r:sepgsql_table_t:s0:c30'
row 1 (db_tuple)
security_label = 'unconfined_u:object_r:sepgsql_table_t:s0:c100'
1:1 Information
1:2 Information
row 2 (db_tuple)
security_label = 'unconfined_u:object_r:sepgsql_table_t:s0:c200'
2:1 Information
2:2 Information
row 3 (db_tuple)
security_label = 'unconfined_u:object_r:sepgsql_table_t:s0:c300'
3:1 Information
3:2 Information

Figure 1: Database Security Context Information - Showing the security contexts that can be associated to a database, table, columns and rows. It is also possible to associate security contexts to procedures and blobs.


SE-PostgreSQL Database Example has a walk-through on how to install SE-PostgreSQL on F-14 with setting up a database, adding tables etc. to show how the security context is used to enforce access control.

To use SE-PostgreSQL each GNU / Linux user must have a valid PostgreSQL database role (not to be confused with an SELinux role). The default installation shown in the SE-PostgreSQL Database Example section automatically adds a user called sepgsql with a suitable database role.

If a client is connecting remotely and labeled networking is required, then it is possible to use IPSec or NetLabel as discussed in the SELinux Networking Support section (the "Security-Enhanced PostgreSQL Security Wiki" also covers these methods of connectivity with examples).

Using the SE-PostgreSQL Services diagram, the database client application (that could be provided by an API for Perl/PHP or some other programming language) connects to a database and executes SQL commands. As the SQL commands are processed by PostgreSQL, each operation performed on an object managed by the object manager (OM) is checked to see if this is allowed by the security policy or not. If the internal AVC does not hold the cached decision then the SELinux kernel Security Server is asked to resolve the query, with the result being cached internally by the OM.

Because PostgreSQL (and therefore SE-PostgreSQL) handles processes, files and directories as part of database operations, the OM also handles permissions for these objects where needed (see the sepostgresql-9.0.1-20101007.fc14.src rpm - selinux.c source code) by re-mapping these permissions internally.

SE-PostgreSQL supports SELinux services via the libselinux library, however it does not use the libselinux AVC API functions as it provides its own services. The AVC audits are logged into the sepostgresql.log file as described in the Logging Security Events section.

The SE-PostgreSQL extensions to support MAC access control are described in the SE-PostgreSQL Extensions section below.


SE-PostgreSQL Extensions

SE-PostgreSQL is implemented as a patch to the standard PostgreSQL service and in order to keep the intrusion to a mimimum, the 9.0.1 version has some minor features removed that were in the 8.4 version. It is expected that the 9.1 version will resolve these. The changes from 8.4 to 9.0 are:

  • The CREATE SQL command (e.g CREATE TABLE..) does not support adding a new security context. The ALTER command should be used instead (examples given below).
  • The SECURITY_CONTEXT keyword has now been replaced by SECURITY LABEL. See the ALTER command example given below.
  • The column name that references the security context (security_context) is now called security_label.
  • The internal table that contains security context strings and pointers (pg_security) is now called pg_seclabel.
  • A number of functions have been removed to keep the patch at a reasonable level.

The following sections describe the areas that have been extended to manage the security context information and enforce access control. There are a number of examples shown in the SE-PostgreSQL Database Example section that contains a walk-through of the installation, set-up and using SE-PostgreSQL to build a simple database with a single table, two columns and then adding a number of rows.

The main areas expanded are:

  • Adding an object manager that utilises SELinux support for policy enforcement via libselinux as shown in the SE-PostgreSQL Services diagram. This runs as the sepostgresql server (replacing the postgresql server).
The PostgreSQL internal tables (the system catalog) have also been enhanced to support security context information and are described in the Internal Tables section.
  • Extending SQL statements to support a security context field.
  • Adding additional SQL functions to support viewing and updating security context information.
  • Modifying utilities to support security context information.

The sections that follow give a brief overview of the extensions added to support SE-PostgreSQL.

Extended SQL Statements

The following SQL Statements have been extended to add a SECURITY LABEL TO 'security_context' field to support SE-PostgreSQL:

ALTER DATABASE
ALTER TABLE
ALTER FUNCTION


For example to create a table with a specific security context, execute:

testdb=# CREATE TABLE info ();
CREATE TABLE

The table will be created with the default context, therefore the ALTER command is used to set the required context as follows:

testdb=# ALTER TABLE info SECURITY LABEL TO 'unconfined_u:object_r:sepgsql_table_t:s0:c10';
ALTER TABLE


Additional SQL Functions

The following function has been added (with an example shown in SE-PostgreSQL Database Example section):

sepgsql_getcon Returns the client security context.

Additional Utilities

The pg_dump and pg_dumpall backup and restore utilities have been made SELinux-aware so that the security context is maintained.

An additional utility called sepg_ctl is also supplied that can be used to start, stop, restart, reload configuration files and report the status of a postgresql or sepostgresql server. sepg_ctl --help will list all the options.

Additional postgresql.conf Entries

The postgresql.conf file has the following additional entry added to manage the sepostgresql process[1]:

sepostgresql = [option]
sepostgresql The entry specifying the SE-PostgreSQL run time configuration.
option SE-PostgreSQL activation option that can be set to one of the following:

default - Follow the SELinux enforcement mode setting. enforcing - SE-PostgreSQL is always in enforcing mode. permissive - SE-PostgreSQL is always in permissive mode. disabled - SE-PostgreSQL is disabled. The default setting is 'default'.


Internal Tables

To support the overall database operation PostgreSQL has internal tables in the system catalog that hold information relating to user databases, tables etc. This section will only highlight the internal tables and their columns used by SE-PostgreSQL to support the object classes and security context entries using examples taken from the SE-PostgreSQL Database Example section.

Table 1 describes each of the tables used by SE-PostgreSQL to support security context relationships with example SELECT statements to retrieve the relevant information. The only internal table to actually hold security context strings is the pg_seclabel table as all others reference these strings using identifiers as described in Table 2.

Internal Table Name Object Object Class Comments
pg_database Database db_database The datname column holds the database name.
SELECT datname, security_label FROM pg_database WHERE datname = 'testdb';

datname  | security_label
---------+-------------------------------------
testdb   | unconfined_u:object_r:sepgsql_db_t:s0
pg_class Table db_table The relname column holds the table name.

The relnatts column holds the number of columns in this table.

The relfilenode column value is that contained in the pg_security.relid entry for each row of the table (as they are related).

SELECT relname, security_label, relnatts, relfilenode FROM pg_class WHERE relname = 'info';

relname  | security_label                              | relnatts   | relfilenode
---------+-----------------------------------------------+------------+-------------
info     | unconfined_u:object_r:sepgsql_table_t:s0:c10  | 2          | 16389
pg_attribute Column db_column The attname column holds the column name.

The attnum column holds the column number.

The attrelid column value is that contained in the pg_security.relid entry for each row of the table (as they are related).

SELECT attname, security_label, attnum, attrelid FROM pg_attribute WHERE attrelid = 'info'::regclass AND attnum > 0;

attname      | security_label                              | attnum | attrelid
-------------+-----------------------------------------------+--------+-----------
user_name    | unconfined_u:object_r:sepgsql_table_t:s0:c20  | 1      | 16389
email_addr   | unconfined_u:object_r:sepgsql_table_t:s0:c30  | 2      | 16389
pg_seclabel Row db_tuple The pg_seclabel table holds the security context strings and pointers for all objects including the rows (or tuples) as described in Table 2.

Table 1: PostgreSQL Internal Tables - Note that each table has other columns containing information, however only that relevant to the overview are described.


Table 2 describes each of the columns defined in the pg_seclabel table with example entries after the table.

pg_seclabel Column
Comment
secid The unique identifier for this security context. The context is unique for this database (the datid column) and related OID (the relid column for the table, procedure, row etc.).
datid The OID of the database to which this entry refers. This can be obtained from the pg_stat_database table as shown in the following example (that will list all contexts used by this instance of the database):
SELECT datname, secid, relid, label FROM pg_seclabel, pg_stat_database WHERE pg_seclabel.datid = pg_stat_database.datid AND
 datname='testdb';
relid The OID of an object (table, column etc.) or the related ID of a row.

This section will only describe the table, column and row entries for relid. There are many others that relate to internal OIDs used by PostgreSQL that are beyond the scope of this Notebook[2].

For tables an OID of '1259' is assigned. These relate to table names in the pg_class internal table.

For columns an OID of '1249' is assigned. These relate to column names in the pg_attribute internal table.

For rows inserted into a table this is the related pg_class.relfilenode and pg_attribute.attrelid entry for that table / column.

label Text string of the security context for the object (database, table etc.).

Table 2: pg_seclabel Table Columns


The following are example entries with comments taken from the pg_seclabel table columns that were displayed using SELECT * FROM pg_seclabel;:

# datid '1' is for an internal PostgreSQL database.
# relid '3764' is the pg_ts_template OID
# Therefore this context is assigned to a system template object.

secid | datid | relid | label 
------+-------+-------+--------------------------------------------
 3380 | 1     | 3764  | unconfined_u:object_r:sepgsql_sysobj_t:s0
# datid '1' is for an internal PostgreSQL database.
# relid '1255' is the pg_proc (procedure) OID
# Therefore this context is assigned to a system procedure object.

secid | datid | relid | label 
------+-------+-------+--------------------------------------------
3397  | 1     | 1255  | unconfined_u:object_r:sepgsql_db_t:s0
# datid '0' is assigned to an internal database.
# relid '1262' is the pg_database (database) OID
# Therefore this context entry is assigned to database objects. 
#
# Note that datid = 0 and relid = 1262 entries define contexts assigned to 
# database instances including 'testdb' (but see next example).

secid | datid | relid | label 
------+-------+-------+--------------------------------------------
3399  | 0     | 1262  | unconfined_u:object_r:sepgsql_db_t:s0
# This example is for the 'testdb' database after altering its context from the
# above default to 'unconfined_u:object_r:sepgsql_db_t:s0:c888' using:

ALTER DATABASE testdb SECURITY LABEL TO 'unconfined_u:object_r:sepgsql_db_t:s0:c888'

# This will insert an additional entry into the pg_seclabel table as follows:

secid | datid | relid | label 
------+-------+-------+--------------------------------------------
3400  | 0     | 1262  | unconfined_u:object_r:sepgsql_db_t:s0:c888
# datid '16384' is assigned by the system as the identifier for testdb database.
# relid '1259' is the pg_class (table) OID
# Therefore this entry is for a table in the testdb database.

secid | datid | relid | label 
------+-------+-------+--------------------------------------------
16385 | 16384 | 1259  | unconfined_u:object_r:sepgsql_table_t:s0:c10
# datid '16384' is assigned by the system as the identifier for testdb database.
# relid '1249' is the pg_attribute (column) OID
# Therefore this entry is for a column in a table in the testdb database.

secid | datid | relid | label 
------+-------+-------+--------------------------------------------
16386 | 16384 | 1249  | unconfined_u:object_r:sepgsql_table_t:s0:c20
# datid '16384' is assigned by the system as the identifier for testdb database.
# relid '16389' is a system pointer back to the table (pg_class.relfilenode) and
# column (pg_attribute.attrelid) in testdb database for a row of data.
# Therefore this entry represents the context for a row (tuple) of data in a
# table of the testdb database.

 secid | datid | relid | label 
-------+-------+-------+--------------------------------------------
16393  | 16384 | 16389 | unconfined_u:object_r:sepgsql_table_t:s0:c110

Logging Security Events

SE-PostgreSQL manages its own AVC audit entries in the /var/log/sepostgresql.log file and by default only errors are logged (i.e. it does not add AVC entries into the standard audit.log).


SE-PostgreSQL Database Example

Introduction

This section gives a run through installing and running a very simple database to show some of the SE-PostgreSQL features.

The areas covered are:

  • Install sepostgresql using yum. It assumes that postgresql or sepostgresql are not installed.
  • Initialise a database cluster so that sepostgresql can be started.
  • Create a database called testdb.
  • Using the PostgreSQL terminal client psql create a simple table with two columns and insert 4 rows (or tuples) of data demonstrating how to add and show the security context information associated with these objects. To enable the security context information to be distinguished between the various objects the following will be used:
Name Object Context used
Database (testdb) db_database unconfined_u:object_r:sepgsql_db_t:s0
Table (info) db_table unconfined_u:object_r:sepgsql_table_t:s0:c10
Column 1 (user_name) db_column unconfined_u:object_r:sepgsql_table_t:s0:c20
Column 2 (email_addr) db_column unconfined_u:object_r:sepgsql_table_t:s0:c30
Row 1 db_tuple unconfined_u:object_r:sepgsql_table_t:s0:c100
Row 2 db_tuple unconfined_u:object_r:sepgsql_table_t:s0:c110
Row 3 db_tuple unconfined_u:object_r:sepgsql_table_t:s0:c120
Row 4 db_tuple unconfined_u:object_r::unconfined_t:s0:c130
  • Finally run some sepostgresql specific functions and explain their results.


The following assumptions have been made:

  1. The user has a basic knowledge of databases and the SQL language.
  2. SE-PostgreSQL or PostgreSQL are not installed.
  3. The system used is Fedora 14 with the targeted policy (selinux-policy-targeted-3.9.7-16.fc14.noarch). This would have installed the postgresql policy modules by default.
  4. Generally when adding entries to a database SE-PostgreSQL will use a default security context, however in this walk-through all entries will have specific security context defined for them (except the database (testdb) that will use the SE-PostgreSQL default).

SE-PostgreSQL Walk-through

Install sepostgresql using yum. This will install all the required components including postgresql:

yum install postgresql
yum install sepostgresql

On the authors machine, the following were installed:

rpm -qa | grep postgresql

sepostgresql-9.0.1-2010007.fc14.i686
postgresql-8.4.6-1.fc14.i686
postgresql-libs-8.4.6-1.fc14.i686

Ensure SELinux is in enforcing mode:

setenforce 1

Once sepostgresql is installed a database cluster needs to be initialised. As part of the sepostgresql installation an init script (/etc/init.d/sepostgresql) was added that will manage this process:

service sepostgresql initdb
Initializing database:

For information, the database cluster will be built by the above process in /var/lib/sepgsql/data. Note that an sepgsql user and group were also added as a part of the installation process:

ls -lZ /var/lib/sepgsql
drwx------. sepgsql sepgsql system_u:object_r:postgresql_db_t:s0 backups
drwx------. sepgsql sepgsql unconfined_u:object_r:postgresql_db_t:s0 data

Once the database cluster has been initialised it can be started by:

service sepostgresql start
Starting sepostgresql service: [ OK ]

This demo will create the test database and tables etc. as the sepgsql user:

su - sepgsql

Optionally, once logged on as the sepgsql user, the PostgreSQL createuser command can be used to allow other GNU / Linux users to access PostgreSQL by:

createuser [login_name]

# for example:
createuser root
Shall the new role be a superuser? (y/n) y
# This would allow root to use the PostgreSQL commands to manage
# the database as a superuser.

Now the testdb database itself needs to be created by the PostgreSQL createdb command:

createdb testdb

Once created, the PostgreSQL interactive terminal (psql) needs to be loaded so that SQL statements can be run against the database:

# This command will load psql and connect it to the testdb database:
psql testdb

Now that psql is active and connected to the testdb database SQL statements can be run. The first one is to display the security context of the database that requires some knowledge of how SE-PostgreSQL holds its internal parameters. As explained in the SELinux PostgreSQL Support section the main internal tables of interest are pg_database, pg_class, pg_attribute and pg_seclabel, with pg_database holding the database name. Therefore if the following SQL statement is executed, the security context of the testdb database will be returned:

testdb=# SELECT datname, security_label FROM pg_database 
WHERE datname = 'testdb';

datname  | security_label 
---------+---------------------------------------
testdb   | unconfined_u:object_r:sepgsql_db_t:s0
(1 row)

Now a table (info) will be created that will have two columns (user_name and email_addr). A unique security context will be given to each object created as follows:

testdb=# CREATE TABLE info (user_name CHAR(10), email_addr CHAR(20));
CREATE TABLE

The above command will have created the table and its columns with the default context, therefore the ALTER SQL command will be used to set the context to those specified above:

testdb=# ALTER TABLE info SECURITY LABEL TO 'unconfined_u:object_r:sepgsql_table_t:s0:c10';
ALTER TABLE
testdb=# ALTER TABLE info ALTER COLUMN user_name SECURITY LABEL
TO 'unconfined_u:object_r:sepgsql_table_t:s0:c20';
ALTER TABLE
testdb=# ALTER TABLE info ALTER COLUMN email_addr SECURITY LABEL
TO 'unconfined_u:object_r:sepgsql_table_t:s0:c30';
ALTER TABLE

Now that the table has been created, the security context of each object can be displayed by querying SE-PostgreSQL internal tables.

The SQL statement to retrieve the table object info security context is as follows, note that the pg_class internal table holds the table name:

testdb=# SELECT relname, security_label FROM pg_class WHERE relname = 'info'; 

relname  | security_label 
---------+----------------------------------------------
info     | unconfined_u:object_r:sepgsql_table_t:s0:c10
(1 row)

The SQL statement to retrieve the column object user_name is as follows, note that the pg_attribute internal table holds the column name:

testdb=# SELECT attname, security_label FROM pg_attribute WHERE attname = 'user_name';

attname    | security_label 
-----------+----------------------------------------------
user_name  | unconfined_u:object_r:sepgsql_table_t:s0:c20
(1 row)

And the SQL statement to retrieve the column object email_addr is as follows:

testdb=# SELECT attname, security_label FROM pg_attribute WHERE attname = 'email_addr';

attname     | security_label 
------------+----------------------------------------------
email_addr  | unconfined_u:object_r:sepgsql_table_t:s0:c30
(1 row)

Now that the table and its columns have been created, it is now possible to insert information into the database. Each row (or tuple) will now be added with its own unique security context.

Insert Row 1:

testdb=# INSERT INTO info (security_label, user_name, email_addr) 
 VALUES ('unconfined_u:object_r:sepgsql_table_t:s0:c100', 'fred', 'fred@yahoo.com');
INSERT 0 1

Show Row 1 security context, note that only the table name info is specified (i.e. no internal table name required):

testdb=# SELECT user_name, email_addr, security_label FROM info;

user_name  | email_addr      | security_label 
-----------+-----------------+-------------------------------------------
fred       | fred@yahoo.com  | unconfined_u:object_r:sepgsql_table_t:s0:c100
(1 row)

Insert Rows 2 and 3 each with a unique security context:

testdb=# INSERT INTO info (security_label, user_name, email_addr) VALUES 
 ('unconfined_u:object_r:sepgsql_table_t:s0:c110', 'derf', 'derf@hotmail.com');
INSERT 0 1

testdb=# INSERT INTO info (security_label, user_name, email_addr) VALUES
 ('unconfined_u:object_r:sepgsql_table_t:s0:c120', 'george', 'george@hotmail.com');
INSERT 0 1

Show Rows 1, 2 and 3 security context:

testdb=# SELECT user_name, email_addr, security_label FROM info;

user_name | email_addr         | security_label 
----------+--------------------+--------------------------------------------
fred      | fred@yahoo.com     | unconfined_u:object_r:sepgsql_table_t:s0:c100
derf      | derf@hotmail.com   | unconfined_u:object_r:sepgsql_table_t:s0:c110
george    | george@hotmail.com | unconfined_u:object_r:sepgsql_table_t:s0:c120
(3 rows)

To demonstrate that SE-PostgreSQL will not allow entries to be entered unless the security context is valid, an entry will be made with a type of unconfined_t as this is not valid for the standard targeted policy. It is assumed that SELinux is in enforcing mode:

testdb=# INSERT INTO info (security_label, user_name, email_addr) VALUES 'unconfined_u:object_r:unconfined_t:s0:c130', 'hidden', 'hidden@hotmail.com');
ERROR: SELinux: security policy violation

Now to demonstrate that SE-PostgreSQL will not display information that is not allowed by the policy, set SELinux to permissive mode:

setenforce 0

Now insert the row again:

testdb=# INSERT INTO info (security_label, user_name, email_addr)
 VALUES 'unconfined_u:object_r:unconfined_t:s0:c130', 'hidden', 'hidden@hotmail.com');
INSERT 0 1

And then display the information:

testdb=# SELECT user_name, email_addr, security_label FROM info;

user_name | email_addr         | security_label 
----------+--------------------+-----------------------------------------
fred      | fred@yahoo.com     | unconfined_u:object_r:sepgsql_table_t:s0:c100
derf      | derf@hotmail.com   | unconfined_u:object_r:sepgsql_table_t:s0:c110
george    | george@hotmail.com | unconfined_u:object_r:sepgsql_table_t:s0:c120
hidden    | hidden@hotmail.com | unconfined_u:object_r:unconfined_t:s0:c130
(4 rows)

Now set SELinux to enforcing mode:

setenforce 1

And then display the information, note that the 4th row is not displayed:

testdb=# SELECT user_name, email_addr, security_label FROM info;

user_name | email_addr         | security_label 
----------+--------------------+-------------------------------------------
fred      | fred@yahoo.com     | unconfined_u:object_r:sepgsql_table_t:s0:c100
derf      | derf@hotmail.com   | unconfined_u:object_r:sepgsql_table_t:s0:c110
george    | george@hotmail.com | unconfined_u:object_r:sepgsql_table_t:s0:c120
(3 rows)


SE-PostgreSQL Functions

Because the SE-Postgresql services are implemented via a patch, for version 9.0.1 the number of functions has been limited, therefore the only function currently supported is sepgsql_getcon that will retrieve the client context as follows:

testdb=# SELECT sepgsql_getcon();

sepgsql_getcon 
-------------------------------------------------------
unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
(1 row)



  1. For the default installation described in the SE-PostgreSQL Database Example section, the configuration file is located at /var/lib/sepgsql/data/postgresql.conf.
  2. Note that the database context (OID = 1262 in the relid column) is listed as being under the datid of database '0'. The best way to retrieve the actual database context is by: SELECT security_label FROM pg_database WHERE datname = '...';