SEPostgreSQL Specifications

From PostgreSQL wiki
Jump to navigationJump to search

SepgsqlLogo.png

This chapter introduces how SE-PostgreSQL applies its access controls.

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

Overview

This chapter introduces the design specifications of SE-PostgreSQL.

Scope of the access controls

SE-PostgreSQL performs as a reference monitor to SQL queries given from users.

It means SE-PostgreSQL monitors any accesses on database objects via the given queries from users, and checks their privileges on the required objects, without any exception even if he has database superuser privileges. It also means SE-PostgreSQL does NOT check the behavior of system internal stuff, such as references to the system caches. In addition, it does NOT check the accesses without SQL queries, such as dumping the raw file images.

It is important for any kind of security features to understand its target, scope, functionality and limitation.

Privileges of the client

SE-PostgreSQL applies an orthogonal privileges on the client independent from the existing database role mechanism and database authentication. It obtains the security context of the client process on the peer of the database connection, using getpeercon() provided by SELinux.

It means that user's queries need to be checked from two different viewpoint. The one is SE-PostgreSQL which makes its decision based on the security context and the security policy. The other is database ACL mechanism which makes its decision based on the database role and the database acl. So, SE-PostgreSQL requires the clients to be allowed from both of the security features.

The client's privilege is switched while it runs special procedures called trusted procedure, as if set-uid commands on operating system or security definer functions on databases. See the later section for more details.

How to prevent violated accesses

If SE-PostgreSQL is disabled on the build-time or run-time, it does not affect anything, and performs as if air. It is necessary to keep compatibility with existing systems.

If SE-PostgreSQL is enabled and the required accesses are allowed, it also does not affect anything, expect for invalidation of a few kind of optimization and a few of hardwired rules.

Otherwise, it raises an error to abort the query execution or returns a certain state from the security hook. Some of the security hooks caller handles the error state in appropriate way. For either cases, SE-PostgreSQL prevents violated accesses from users.


Access control behaviors

Tables/columns level access controls

The query analyzer picks up all the appeared tables and columns in the given queries to check their database acls. SE-PostgreSQL also applies its access controls on the tables and columns, and raises an error if the client does not have enough privileges.

In this section, we introduces what privileges are checked on what database objects using examples.

Example.1
SELECT name, price * 1.20 FROM drink WHERE id in (1,2,3) ORDER BY size;

This SELECT statement accesses four columns within drink table. The name and price are appeared in the target list either directly or indirectly as a part of the formula. The id and size is used in the WHERE and ORDER BY clause. User does not read the contents of the columns in this query, but it is considered as a reference to the columns. Needless to say, this query refers the drink table.

So, the client needs to have the following privileges at least.

  • db_table:{select} on the drink table
  • db_column:{select} on the name, price, id and size column
Example.2
SELECT name, price FROM drink WHERE id in (SELECT ordered FROM bill WHERE bid = 1234);

This SELECT statement contains the subquery which refers an another table. The concept is basically same as the prior example. This query refers name, price and id columns within the drink table, and ordered and bid within bill table.

So, the client needs to have the following privileges at least.

  • db_table:{select} on the drink table.
  • db_column:{select} on the name, price and id columns.
  • db_table:{select} on the bill table.
  • db_column:{select} on the ordered and bid columns.
Example.3
UPDATE drink SET name = 'coke', price = 1.2 * price WHERE id = 5;

This UPDATE statement tries to update the name and price columns. In addition, the price is also referred as a part of calculating formula and id is referred in the WHERE clause, without any updating. The price is considered to be referred and updated concurrently, and the drink table which contains price and id is also referred and updated concurrently.

So, the client needs to have the following privileges at least.

  • db_table:{select update} on the drink table.
  • db_column:{update} on the name column.
  • db_column:{select update} on the price column.
  • db_column:{select} on the id column.
Example.4
DELETE FROM drink RETURNING id, name, price;

An unconditional DELETE statement does not need any privileges on the columns, and no delete permission is fundamentally defined at db_column object class. So, only db_table:{delete} on the drink would be necessary, if it didn't have RETURNING clause.

The RETURNING clause provides users a chance to refer the deleted tuples and its contents, so it also requires db_table:{select} on the drink table and db_column:{select} on the returned columns. In the result, composite privileges are required as follows:

  • db_table:{select delete} on the drink
  • db_column:{select} on the id, name and price column
Example.5
CREATE VIEW expensive_drink AS
    SELECT id, name, price * 1.50 AS tax_price FROM drink WHERE price > 300;

SELECT * FROM expensive_drink WHERE id in (1,2,3);

This statement tries to refer the drink table via the expensive_drink view. SE-PostgreSQL checks the client's privileges on the expanded query, so it considers the given query is equivalent to the following form. It focuses on what database objects are accessed, not how database objects are accessed.

SELECT * FROM (SELECT id, name, price * 1.50 AS tax_price
                          FROM drink WHERE price > 300) WHERE id in (1,2,3)

So, the client needs to have the following privileges at least.

  • db_table:{select} on the drink table
  • db_column:{select} on the id, name and price columns

other permissions

The db_table:{lock} shall be checked when a client tries to acquire an explicit locks on the tables, not implicit ones. The explicit lock means the LOCK statement and SELECT ... FOR UPDATE/SHARE statement.

The db_table:{reference} and db_column:{reference} shall be checked when a client tries to set up a foreign-key constraint on both of the reference and referred tables.

Row level access controls

The row-level access controls perform as if a filter which drops any violated tuples while the executor scans the tables. It does not raise an error immediately on the references to violated tuples unlike the table/column level access controls, because it needs usability pains to avoid violated tuples.

An example table is defined as follows. It has eight tuples. Two of them are labeled as Secret, other two of them labeled as Classified, and the rest of them are labeled as Unclassidied.

<pre>
postgres=# SELECT security_label, * FROM drink;
                 security_label                 | id | name  | price
------------------------------------------------+----+-------+-------
 system_u:object_r:sepgsql_table_t:Unclassified |  1 | water |   100
 system_u:object_r:sepgsql_table_t:Unclassified |  2 | coke  |   120
 system_u:object_r:sepgsql_table_t:Unclassified |  3 | juice |   130
 system_u:object_r:sepgsql_table_t:Unclassified |  4 | cofee |   180
 system_u:object_r:sepgsql_table_t:Classified   |  5 | beer  |   240
 system_u:object_r:sepgsql_table_t:Classified   |  6 | sake  |   320
 system_u:object_r:sepgsql_table_t:Secret       |  7 | wine  |   380
 system_u:object_r:sepgsql_table_t:Secret       |  8 | tea   |   140
(8 rows)

If the client has a privileges to refer Unclassified information only, it shall get the following result set with unconditional SELECT statement.

postgres=# SELECT security_label, * FROM drink;
                 security_label                 | id | name  | price
------------------------------------------------+----+-------+-------
 system_u:object_r:sepgsql_table_t:Unclassified |  1 | water |   100
 system_u:object_r:sepgsql_table_t:Unclassified |  2 | coke  |   120
 system_u:object_r:sepgsql_table_t:Unclassified |  3 | juice |   130
 system_u:object_r:sepgsql_table_t:Unclassified |  4 | cofee |   180
(4 rows)

For another client who can refer Unclassified and Classified, it shall get the following result set with same query.

postgres=# SELECT security_label, * FROM drink;
                 security_label                 | id | name  | price
------------------------------------------------+----+-------+-------
 system_u:object_r:sepgsql_table_t:Unclassified |  1 | water |   100
 system_u:object_r:sepgsql_table_t:Unclassified |  2 | coke  |   120
 system_u:object_r:sepgsql_table_t:Unclassified |  3 | juice |   130
 system_u:object_r:sepgsql_table_t:Unclassified |  4 | cofee |   180
 system_u:object_r:sepgsql_table_t:Classified   |  5 | beer  |   240
 system_u:object_r:sepgsql_table_t:Classified   |  6 | sake  |   320
(6 rows)

In both cases, the tuples labeled as Secret were filtered due to lack of the privileges.

what permissions are required

The db_tuple object class has six kind of permissions. Needless to say, it requires the corresponding permission to the statement at least. For example, it checks db_tuple:{select} on the tuples to be fetched with SELECT statements, and it checks db_tuple:{update} on tuples to be updated with UPDATE statement.

It also checks db_tuple:{insert} permission on the newly inserted tuples with INSERT statement. The new tuples can have an explicitly given or a default security context come from the security policy.

In addition, when the given query contains references to the tuple, such as WHERE clause or RETURNING clause, it also needs db_tuple:{select} on the tuples. So, the target tuples needs to be read-writable when we execute a conditional UPDATE or DELETE, not only writer permissions.

Example.1
SELECT name, price - 100 FROM drink;

It requires only db_tuple:{select}.

Example.2
INSERT INTO drink (id, name, price) VALUES (7, 'wine', 380);

It requires only db_tuple:{insert}.

Example.3
UPDATE drink SET price = price * 1.2;

It requires db_tuple:{select update}, because it also refers via price column as a part of calculation formula.

Example.4
SELECT * FROM pg_proc WHERE oid = 'sepgsql_getcon'::regproc;

It is a special case, so requires db_procedure:{getattr}, because the tuples within pg_proc system catalog corresponds to db_procedure class objects.


Orders to be checked

SE-PostgreSQL applies the row level access controls during the table scanning prior to any user given conditions expect for index scans, because it does not allow to deliver user defined functions the contents of tuples before any checks. (Index access methods are assumed to be trusted.) It makes ensure a user cannot read the tuples without correct permissions, even if he add a self defined functions with little cost, which implicitly write out the argument and always returns true.

One exception of the first check policy is the secondary queries to check foreign key constraints. The foreign key constraints are implemented as trigger functions which executes the secondary query to check whether the new key can keep the constraints, or not. The row level control raises an error, during the steps to avoid to break referential integrity. In this case, it checks the row level permission at the last not to raises an error on unrelated tuples. We assume the built-in foreign key constraints don't inject malicious conditions.

The row level access controls are also applied prior to table joins. Please consider the following two tables.

postgres=# SELECT security_label, * FROM tbl_l;
                 security_label                 | a |  b
------------------------------------------------+---+-----
 system_u:object_r:sepgsql_table_t:Classified   | 1 | aaa
 system_u:object_r:sepgsql_table_t:Unclassified | 2 | bbb
 system_u:object_r:sepgsql_table_t:Unclassified | 3 | ccc
(3 rows)
postgres=# SELECT security_label, * FROM tbl_r;
                 security_label                 | x |  y
------------------------------------------------+---+-----
 system_u:object_r:sepgsql_table_t:Unclassified | 1 | xxx
 system_u:object_r:sepgsql_table_t:Unclassified | 2 | yyy
 system_u:object_r:sepgsql_table_t:Classified   | 3 | zzz
(3 rows)

When a unclassified user tries to join the tables with FULL OUTER JOIN option, it returns the following result, different from additional WHERE clauses.

postgres=# SELECT * FROM tbl_l FULL OUTER JOIN tbl_r ON a = x;
 a |  b  | x |  y
---+-----+---+-----
   |     | 1 | xxx
 2 | bbb | 2 | yyy
 3 | ccc |   |
(3 rows)

Misc database objects

Common database permissions

SELinux defines six common permissions for database objects, and any database object classes inherit them (except the class db_tuple due to the naming mismatch).

The db_xxx:{create}, db_xxx:{drop} and db_xxx:{setattr} permissions are corresponding to CREATE xxx, DROP xxx and ALTER xxx operations each other. They also correspond to INSERT, DELETE and UPDATE statement on a certain system catalog, and the db_xxx:{select} is also checked on the SELECT statement on them, instead of permissions within the db_tuple object class.

The db_xxx:{relabelfrom} and db_xxx:{relabelto} permissions are checked on the newer and older security context when a user tries to change the security context of the database object.

Databases

The tuples within pg_database system catalog are considered db_database object class.

It has four eigen permissions. See the Dynamic load libraries for install_module and load_module.

The db_database:{access} is corresponding to the ACL_CONNECT permission in the core. It is checked when the client logs on the database.

The db_database:{superuser} is corresponding to the database superuser privilege in the core. It is checked when the client tries to perform as database superuser. If not allowed, he cannot perform as a database superuser, but SE-PostgreSQL does not raise an error.

Schemas

The tuples within pg_namespace system catalog are considered db_schema or db_schema_temp object class.

They have three eigen permissions.

The db_schema:{search} is corresponding to the ACL_USAGE permission in the core. It is checked when the query lookups database objects under the namespace. The db_schema_temp:{search} is defined but ignored, due to the implementation reason.

The db_scheme:{add_object} and db_schema_temp:{add_object} are checked when an object is added to the namespace. The db_schema:{remove_object} and db_schema_temp:{remove_object} are checked when an object is removed from the namespace. Please note that it does not require any permissions when temporary objects are clean up after the session closed.

Procedures

The tuples within pg_proc system catalog are considered db_procedure object class.

It has three eigen permissions. See the Trusted procedure for db_procedure:{entrypoint} and Dynamic load libraries for db_procedure:{install}.

The db_procedure:{execute} is corresponding to the ACL_EXECUTE permission in the core.

Sequences

The tuples within pg_class system catalog and relkind = RELKIND_SEQUENCE are considered db_sequence object class.

It has three eigen permissions.

The db_sequence:{get_value} is checked on the sequence to be referred without any changes.

The db_sequence:{get_value} is checked on the sequence to be referred with an increment of the values.

The db_sequence:{get_value} is checked on the sequence to be set a given value.

Large objects

A largeobject consists of multiple tuples within pg_largeobject system catalog are considered db_blob object class.

It has four eigen permissions.

The db_blob:{read} is checked when we refer contents of the largeobject using loread(), lo_export() or SELECT ... FROM pg_largeobject.

The db_blob:{write} is checked when we modify contents of the largeobject using lowrite, lo_truncate() and lo_import(). Please note that we cannot touch pg_largeobject by hand due to the hardwired rules.

The db_blob:{export} and db_blob:{import} are checked on the largeobject to be exported or imported. See the Interactions with filesystem for more details.

Labeling the objects

See the Architecture->Management of the security context also, to understand what is the security context and how does it handled internally.

Using security_label system column

The security_label system column gives users a method to access the security context of every tuples. Every tuples actually have one or not security identifier, not a security context in text, which can indicate an entry within pg_security system catalog. The security_label system column is declared as TEXT type, and returns a security context in text, when we accesses the system catalog with SELECT statement for example.

Example:

postgres=# SELECT security_label, * FROM drink ORDER BY id;
                 security_label                  | id | name  | price
-------------------------------------------------+----+-------+-------
 system_u:object_r:sepgsql_table_t               |  1 | water |   100
 system_u:object_r:sepgsql_ro_table_t:Classified |  2 | coke  |   120
 system_u:object_r:sepgsql_table_t:Classified    |  3 | juice |   130
 system_u:object_r:sepgsql_ro_table_t            |  4 | cofee |   180
(4 rows)


When the fetched tuples have no label, the security_label system catalog returns an initial security context for unlabeled objects, instead of NULL. It is typically system_u:object_r:unlabeled_t:s0.

The security_label system column can be used as a target of INSERT, UPDATE and COPY FROM statement to set up an explicit security context of the tuples.

Example:

postgres=# INSERT INTO drink (security_label, id, name, price)
               VALUES ('system_u:object_r:sepgsql_table_t:Secret', 5, 'beer', 280);
INSERT 16493 1
postgres=# SELECT security_label, * FROM drink ORDER BY id;
                 security_label                  | id | name  | price
-------------------------------------------------+----+-------+-------
 system_u:object_r:sepgsql_secret_table_t        |  1 | water |   100
 system_u:object_r:sepgsql_ro_table_t:Classified |  2 | coke  |   120
 system_u:object_r:sepgsql_table_t:Classified    |  3 | juice |   130
 system_u:object_r:sepgsql_secret_table_t        |  4 | cofee |   180
 system_u:object_r:sepgsql_table_t:Secret        |  5 | beer  |   280
(5 rows)

SELECT INTO statement

The SELECT INTO statement enables to create a new table according to the field list of the given statement, and insert its result set into the new table. When a selected field is named security_label, its value is considered as an explicitly specified security context, instead of the conflict with a system column name.

Example:

postgres=# SELECT 'system_u:object_r:sepgsql_table_t:s0:c' || id AS security_context,
                  id, name, price INTO t1 FROM drink;
SELECT
postgres=# SELECT security_label, * FROM t1;
             security_label              | id | name  | price
-----------------------------------------+----+-------+-------
 system_u:object_r:sepgsql_table_t:s0:c1 |  1 | water |   100
 system_u:object_r:sepgsql_table_t:s0:c2 |  2 | coke  |   120
 system_u:object_r:sepgsql_table_t:s0:c3 |  3 | juice |   130
 system_u:object_r:sepgsql_table_t:s0:c4 |  4 | cofee |   180
 system_u:object_r:sepgsql_table_t:s0:c5 |  5 | beer  |   240
 system_u:object_r:sepgsql_table_t:s0:c6 |  6 | sake  |   320
(6 rows)

CREATE/ALTER with SECURITY_LABEL

Some of CREATE and ALTER statements provide a new SECURITY_LABEL=xxx option to specify the security context of the target object, instead of UPDATE the system catalogs by hand. See the Reference->Enhanced SQL statements for more details.

Default security context

When we create a new database object (such as a table, function, tuple and so on) without any explicit security context, SE-PostgreSQL assigns them a default security context instead of keeping it empty.

The security policy of SELinux also has a set of rules to provide a security context to be assigned in the default that means a case when user does not give any explicit security context. It determines the default security context based on the combination of the user's security context, the parent object's security context and the object class of the new objects. The parent object depends on the kind of new objects. For example, the parent object of a new column is the table which shall contain the new column. The References->Object classes and access vector section shows the details for each object classes.

Anyway, SELinux can returns a unique security context as the default context, when userspace application asked. SE-PostgreSQL assigns a default security context according to the suggestion from in-kernel SELinux.

Please note that it also checks the privileges for creation or insertion on the default security context, as if it was given from users due to the SELinux design. SELinux can give the default security context, but it is just a hint.

Detailed note

Trusted procedure

SELinux provides a facility to switch user's privileges while executing a certain executable, called domain transition. For example, when a system init script (initrc_t domain) forks a child process and executes /usr/bin/postgres (postgresql_exec_t), the child process performs as postgresql_t domain instead of initrc_t domain. We have similar ideas such as setuid programs or security defined functions. Both of them provides a way to switch user's privileges on their access control mechanism during trusted executables.

The trusted procedures is a procedure which causes a domain transition. We can use the feature to provide a secure way to access classified information typically. The sepgsql_trusted_proc_exec_t is declared as a trusted procedure which performs with administrative privileges. Please note that we can also set up a trusted procedure to switch user's privileges into more restrictive one, not only privileges escalation.

In this example, it does not allow unprivileged users to access the credit card number directly.

postgres=# SELECT sepgsql_getcon();
     sepgsql_getcon
-------------------------
 staff_u:staff_r:staff_t
(1 row)

postgres=# SELECT cid, cname, credit FROM customer;
ERROR:  SELinux: security policy violation

postgres=# SELECT cid, cname FROM customer;
 cid | cname
-----+-------
  10 | jack
  13 | adam
  14 | liza
(3 rows)

However, we can set up a trusted procedure to suggest a secure way to refer the credit card number. In this case, it replaces the numbers except for the first four characters, and the author considers it is enough secure to expose unprivileged users.

CREATE OR REPLACE FUNCTION show_credit (integer) RETURNS text
    LANGUAGE 'sql'
    SECURITY_CONTEXT = 'system_u:object_r:sepgsql_trusted_proc_exec_t:s0'
    AS 'SELECT substring(credit from ''^[0-9]+-'') || ''xxxx-xxxx-xxxx'' FROM customer WHERE CID = $1';
postgres=# SELECT cid, cname, show_credit(cid) FROM customer;
 cid | cname |     show_credit
-----+-------+---------------------
  10 | jack  | 1111-xxxx-xxxx-xxxx
  13 | adam  | 5555-xxxx-xxxx-xxxx
  14 | liza  | 9876-xxxx-xxxx-xxxx
(3 rows)

Unique constraint

SE-PostgreSQL also provides its access controls in the row level granularity, and it filters out violated tuples from the result set as if we see the table via a view, so it may seem to clients as if the violated tuples does not exist. But it does not mean it also prevent to infer the existence of invisible objects described at Covert channels.

The unique constraint, such as primary keys, does not allow multiple tuples in a table to share an identical key value. It works in the more fundamental level than the access controls, and forces their uniqueness of every tuples. It well keeps unique constraint independent from the row level access controls, but also means users can infer the existence of invisible primary key value. SE-PostgreSQL does not care about such kind of information leaks, because it is considered as a covert channel and too much requirement in civilian class security.

Example:

postgres=# SELECT security_label, * FROM drink;
                 security_label                 | id | name  | price
------------------------------------------------+----+-------+-------
 system_u:object_r:sepgsql_table_t:Unclassified |  1 | water |   100
 system_u:object_r:sepgsql_table_t:Unclassified |  2 | coke  |   120
 system_u:object_r:sepgsql_table_t:Unclassified |  3 | juice |   130
 system_u:object_r:sepgsql_table_t:Unclassified |  4 | cofee |   180
 system_u:object_r:sepgsql_table_t:Classified   |  5 | beer  |   240
 system_u:object_r:sepgsql_table_t:Classified   |  6 | sake  |   320
(6 rows)

In this example, an Unclassified client can get 4 tuples using unconditional SELECT statement, and 2 violated tuples shall be filtered out. However, here is certainly 2 tuples.

If the Unclassified client tries to insert a tuple with an identical primary key with invisible tuple, it shall get an error to notice key conflicting due to the duplicate key value violations.

postgres=# SELECT security_label, * FROM drink;
                 security_label                  | id | name  | price
-------------------------------------------------+----+-------+-------
 unconfined_u:object_r:sepgsql_table_t:SystemLow |  1 | water |   100
 unconfined_u:object_r:sepgsql_table_t:SystemLow |  2 | coke  |   120
 unconfined_u:object_r:sepgsql_table_t:SystemLow |  3 | juice |   130
 unconfined_u:object_r:sepgsql_table_t:SystemLow |  4 | cofee |   180
(4 rows)

postgres=# INSERT INTO drink VALUES (tea,'wine', 250);
ERROR:  duplicate key value violates unique constraint "drink_pkey"

Foreign key constraint

SE-PostgreSQL also provides its access controls in the row level granularity, and it filters out violated tuples from the result set as if we see the table via a view, so it may seem to clients as if the violated tuples does not exist. But it does not mean it also prevent to infer the existence of invisible objects described at Covert channels.

The foreign key constraint enables to keep referential integrity. It enables to make sure a primary key corresponding to any foreign keys is always exist, and prevent user's operation to break the integrity (or modify the corresponding foreign keys to keep the integrity).

This property is unchanged, even if row level access controls are available. However, please note that a part of tuples can be hidden from unprivileged clients. So, it may look like as if the corresponding key is not existing, depending on the privileges of clients.

PostgreSQL implements the foreign key constraints as a built-in trigger function which invokes secondary queries to check the referential integrity. For example, when we delete a tuple with PK referred by other tables, the trigger function runs secondary queries to check whether the other table refers the PK, or not. Then, it raises an error to prevent the deletion, if one or more tuples are still referring it. During the secondary query, the row level access controls raises an error on violated tuples, instead of filtering them, because it can make orphan foreign keys and shall break referential integrity at the raw table level.

Example:

postgres=# CREATE TABLE tbl_p (a INT PRIMARY KEY, b TEXT);

postgres=# CREATE TABLE tbl_f (x INT REFERENCES tbl_p(a), y TEXT);

postgres=# SELECT security_label, * FROM tbl_p;
                security_label                  | a |  b
------------------------------------------------+---+-----
 system_u:object_r:sepgsql_table_t:Unclassified | 1 | aaa
 system_u:object_r:sepgsql_table_t:Unclassified | 2 | bbb
(2 rows)

postgres=# SELECT security_label, * FROM tbl_f;
                  security_label                | x |  y
------------------------------------------------+---+-----
 system_u:object_r:sepgsql_table_t:Unclassified | 1 | xxx
 system_u:object_r:sepgsql_table_t:Classified   | 2 | yyy
(2 rows)

In this case, the tuple with (2, 'yyy') in tbl_f is hidden from unclassified users, so it looks like him it is available to delete the tuple with (2, 'bbb') in tbl_p, but SE-PostgreSQL prevents it to keep the referential integrity.

postgres=# DELETE FROM tbl_p WHERE a = 2;
ERROR:  SELinux: security policy violation
CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."tbl_f" x WHERE $1 OPERATOR(pg_catalog.=) "x" FOR SHARE OF x"

Interactions with filesystem

Some of SQL operations allows users to read from or write to the filesystem on the server side. It requires file:{read} or file:{write} permission between the client and the filesystem object.

  • COPY TO/FROM statement
  • lo_import() and lo_export() functions
  • pg_read_file() statement

Dynamic Load Libraries

PostgreSQL allows to load dynamic load libraries to implement user defined functions, however we need to pay mention for the risk that malicious binary modules performs as a daemon processes. From the definition of the reference monitor, we have no way to prevent anything from the system internal stuffs. Thus, the dynamic load libraries should be enough trusted.

When a shared library module is installed as an implementation of user defined functions, SE-PostgreSQL checks db_database:{install_module} permission of the client on the current database. This check is applied at once when a function is declared. When a shared library module is loaded to the process memory space, SE-PostgreSQL checks db_database:{load_module} permission between the database and the shared library module. Please note that the check is not to privileges of the client. It checks capability of the database to load a certain type of shared library module.

Truncate statement

PostgreSQL has TRUNCATE statement which enable to delete all the tuples within the target tables. It is much faster than DELETE statement, but logically same as unconditional DELETE from the viewpoint of access controls. It is impossible to apply row level access controls on TRUNCATE statement due to the implementation reasons, so SE-PostgreSQL checks db_tuple:{delete} privileges on all the tuples in the target tables, prior to the truncating.

Audit logs

SE-PostgreSQL can generate audit log records according to the security policy configuration, and write out it to the server log, as follows.

LOG:  SELinux: denied { select } \
        scontext=unconfined_u:unconfined_r:unconfined_t:s0 \
        tcontext=system_u:object_r:sepgsql_table_t:s0:c0 \
        tclass=db_table name=t

In normal cases, it generates the records when it made a decision for access denied. However, we can set up our local security policy to generate them when accesses are allowed. Please don't forget performance impact, if you tries to install auditallow policy.

We also have a plan to integrate the audit logs with system audit daemon in the future version, because it enables to collaborate with any existing SELinux utilities, such as setroubleshoot and audit2allow. However, it requires the PostgreSQL server process works with CAP_AUDIT_WRITE capability which is a part of the root privileges on the operating system. So, we need a feature to set up a file capability on installation time, but it is a future issue.

Limitation

Hardwired rules

SE-PostgreSQL prevents to modify (UPDATE, INSERT and DELETE) the following system catalogs due to the hardwired rules.

  • pg_largeobject
    • All the tuples in pg_largeobject which shares an identical loid need to be labeled with a single security context. So, it should be accessed using lo_xxx() functions only.
  • pg_rewrite
    • It can store the parsed Query structure which contains RangeTblEntry->requiredPerms. If it has modified arbitrarily, it would break access controls on references via VIEWs.
  • pg_security
    • It stores the text representation of the security context, so discretionary manipulation obviously breaks anything.

Interaction with optimization

The optimizer can make flatten functions defined with SQL language. But, SE-PostgreSQL restrains the optimization when the function is set up as trusted procedure, as if the core PostgreSQL restrains it on security definer functions.

Covert channels

Interactions between row level access controls and unique or foreign key constrains have characteristics to be kept in mind. When a user tries to insert a tuple with a primary key value which is already on the target table, but invisible from the user due to the row level access controls. This insertion shall be prevented due to the duplicated primary key, and raises an error. Foreign-key constrains also raises an error, when we try to delete a primary key referred from an invisible tuple, for example. Through these errors, unprivileged users can infer the existence of invisible tuples.

These information flows as called covert channels or illicit channels, which means information flows via irregular paths. The common criteria also mentions about covert channels in FDP_IFF class expect for FDP_IFF.1 and FDP_IFF.2 which are considered as civilian level security. In addition, the higher ones also does not require to eliminate all the illicit information flows expect for FPD_IFF.5 which is considered as an extreme requirement. Therefore, SE-PostgreSQL does not care about them.

We have a practical avoidance to be recommended. Consider to use non-natural keys to avoid unexpected information assets inferred. In this case, a malicious one may be able to infer the PK/FK value, but they are just junks.