The purpose of this wikipage is to introduce design specification of the Security Enhanced PostgreSQL (SE-PostgreSQL) for developers.

SE-PostgreSQL is a security option to apply the security model and the security policy of SELinux on accesses to database objects using SQL queries.

The following links are useful to understand SELinux more.

Fedora SELinux User Guide
- It is a comprehensive SELinux documentation for end users, described from the viewpoint of people knowing nothing about SELinux.
SELinux by Example
- It is a book to introduce the security policy of SELinux and the way to write your own security policy module.

Brief overview

This chapter introduces the security model in SELinux, several corresponding terminologies and concepts.

Security model

Conceptually, access control is deciding on a set of allowed (or denied) actions between a certain subject (such as database client) and a certain object (such as a table) based on previously defined rules. We can consider an access control feature something like a function which returns a bool value (allowed or denied) for the given subject, object and required actions. However, the way to make this access control decision depends on the type of security features and security model on which they stand.

The native database privilege mechanism adopts ACL (Access Control List) model. In this model, a database object stores a list of pairs of the database role and allowed actions on the database object. It is checked when a user's query tries to access a certain database object. For example, we can grant SELECT permission to a certain user on a certain table. When the user tries to access the table, the native database privilege mechanism looks up a suitable entry from the table's ACL. If the matched entry allows the required actions (SELECT in this example), it allows the user's query to refer the table.

In this case, its criterion to make the decision is a pair of the database role and required actions on the database object to be accessed. Its access control rules are stored within database object itself, and the database owner can set up them discretionary, so this model is called DAC (Discretionary Access Control).

On the other hand, the most significant characteristic of SE-PostgreSQL is to enforce any database clients orthogonal access control rules managed by SELinux. SELinux is an operating system feature, and holds a massive set of access control rules called the security policy (See the Security policy for more details). It also contains the rules to control accesses on database object, not only kernel objects. When a database client tries to access a certain database object, SE-PostgreSQL needs to make its access control decision. At this time, SE-PostgreSQL asks SELinux whether the required access should be allowed, or not. Next, SELinux looks up a matched entry from the security policy, and replies a set of allowed actions between the database client and the database object required.

In this model, it is necessary to pay attention to the way to identify the database client and the database object to be checked. The security policy of SELinux contains rules on accesses to various kind of objects which also have various characteristics (e.g, some of them don't have any names, some of them are not persistent objects, ...). SELinux abstracts them using an identifier called security context, to handle and describe them in a consistent manner (See the Security context for more details). An access control rule in SELinux is described as a set of allowed actions on a pair of security contexts (one is the subject which tries to access, and other is the object to be accessed). The security policy is a massive set of these rules.

Accordingly, SE-PostgreSQL gives a pair of the security context of database client and database object to be accessed, when it requires SELinux an access control decision. Note that it means any database objects to be checked need to have its own security context managed correctly.

For example, when a database client labeled as system_u:system_r:httpd_t:s0 (which means a web server process in the default security policy) tries to refer a table labeled as system_u:object_r:sepgsql_ro_table_t:s0 (which means "read-only" table) using SELECT statement, SE-PostgreSQL requires SELinux to reply a set of allowed actions between the system_u:system_r:httpd_t:s0 and system_u:object_r:sepgsql_ro_table_t:s0. Then, it allows the client to access the table, if the set of allowed action contains db_table:{select} permission.

In this case, its criterion to make the decision is the pair of security contexts. Its access control rules are centralized and managed by operating system, so it cannot be set up even if resource owner. The access control rules are enforced any users without any exceptions, so this model is called MAC (Mandatory Access Control).

We introduce the MAC at the Mandatory access control. See the section for more details.

Security context

A security context is a colon separated short string in a common format independent from the type of object. The first field means SELinux user, the second is role, the third is type (also called a domain for processes), and the forth is level.

See the Fedora SELinux User Guide : Chapter 3. SELinux contexts for more details.

$ ls -Z /etc/hosts
-rw-r--r--. root root system_u:object_r:etc_t:s0       /etc/hosts

This shows the security context of a certain file.

$ id -Z
unconfined_u:unconfined_r:unconfined_t:s0

This shows the security context of the user shell process.

For example, when the above user tries to read /etc/hosts, SELinux looks up a set of allowed actions defined between unconfined_u:unconfined_r:unconfined_t:s0 and system_u:object_r:etc_t:s0. Then, SELinux prevents it if the matched rule does not contain file:{read} permission.

Note that the security context assigned to the process performs as if it were a set of privileges.

As introduced at the Security model, SELinux needs a pair of the security context to make its access control decision. It is the security context of the database client and the database object to be accessed.

SELinux provides an interface to retrieve the security context of the peer process which connects to server process. The getpeercon(3) shall return with the security context of the peer process for the given socket descriptor.

int getpeercon(int sockfd, security_context_t *context);

SE-PostgreSQL applies the security context retrieved from the getpeercon() as the security context of the database client. Note that it is decided orthogonally to the database authentication and database roles which the database client logged in.

On the other hand, it is necessary to assign a certain security context for each database object on which SE-PostgreSQL applies its access controls. See the Management of security context for more details.

Anyway, both of the database client and the database object must be labeled with a certain security context correctly, to apply access control rules based on the security policy of SELinux.

Security policy

The security policy is a massive set of access control rules in SELinux, and a rule defines a set of allowed actions between a pair of security contexts (subject and object).

There are a few types of rules in the security policy. The following example is a TE (Type Enforcement) rule which is the most major portion of the security policy.

allow httpd_t sepgsql_ro_table_t : db_table { getattr select lock };

This rule allows a few permissions between the httpd_t domain and the sepgsql_ro_table_t type. Both of the identifier (httpd_t and sepgsql_ro_table_t; leftside of the rule definition) is the third field of the security context. In the default security policy, web server process performs labeled with system_u:system_r:httpd_t:s0, and the system_u:object_r:sepgsql_ro_table_t:s0 is a candidate of the security context to be assigned on a table object.

When a web server process tries to update a certain table labeled as system_u:object_r:sepgsql_ro_table_t using a query, SE-PostgreSQL requires SELinux to give a set of allowed actions between system_u:system_r:httpd_t:s0 and system_u:object_r:sepgsql_ro_table_t:s0. Next, SELinux looks up its security policy for the given pair of security contextx, then, the above rule will be matched. This ruls allows { getattr select lock } permissions on table objects, but other permissions (such as { update }) are not explicitly allowed. Because SELinux uses white-list approach, any permissions without explicit allowance shall be denied.

Then, SE-PostgreSQL shall check a set of allowed actions replied. In this case, the web server process tried to update the table labeled as system_u:object_r:sepgsql_ro_table_t:s0, so it needs db_table:{update} permission, but it is not included within the set of allowed actions. Finally, the decision shall be denied. SE-PostgreSQL raises an error to prevent updatable accesses on the table.

Mandatory access controls

This chapter introduces specifications corresponding to mandatory access controls.

Common principles

The design goal of SE-PostgreSQL is to apply the security model and the security policy of SELinux, so is also inherits common principles in the security model.

Unbypassable permission checks

The native database privilege mechanism allows database superusers to bypass all the permission checks. It is a similar idea to the root on operating system.

However, SELinux does not allow a certain permission to override another permissions implicitly. If an operation requires two different permissions individually, the security policy has to allow both of them individually.

Here is an example:

SELECT a, b FROM t WHERE c = 'aaa'

This query tries to refer to the table t and columns a, b and c as a part of the WHERE clause.

The native database privilege mechanism checks whether the current database role has database superuser privilege, or not, at the first. If the database role has database superuser privilege, rest of permission checks shall be bypassed. Otherwise, it checks SELECT permission on the table t. If not allowed, it also checks SELECT permission on all the referenced columns.

SE-PostgreSQL checks all the required permissions individually. If the database client tries to perform as a database superuser, it checks db_database:{superuser} permission on the database. Then, it also checks db_table:{select} permission on the table t, and db_column:{select} permission on the column a, b and c individually, independent of the access control decision on db_database:{superuser} and db_table:{select}.

This reflects a principle in SELinux. Its security model makes decisions on whether the user can execute a certain operation on a certain object, or not, based only on the relationship between the user and the object accessed. It is never overridden by any other permissions implicitly, such as db_database:{superuser}. This characteristic is called analyzability of the security policy.

Independency from path to access

Here is one other principle. In the security model of SELinux, any permissions to be checked on a certain object are independent from the path to access the object.

For example, if the /tmp/aaa is a hard-link which shares a unique file entity with /etc/hosts, we can have two paths to access the file entity. But SELinux always makes identical access control decision independent from the path used to accesses.

Here is an example in databases:

CREATE VIEW v AS SELECT * FROM t WHERE a > 100

This view provides another path to access the table t. But any permissions to be checked by SE-PostgreSQL are not different from the case when we refer the table t without views, independent from the path to access.

(Please note that it is a separate issue whether we should assign view objects a characteristic object class to check permission to expand the view, or not.)

Here is a similar example:

SELECT * FROM pg_namespace

PostgreSQL allows to refer the system catalogs with regular DML statement. The db_schema object class shall be assigned on the tuples within the pg_namespace object class. In this case, any tuples invisible to the database client shall be filtered out from the result set due to the row-level access control. Here, the permission to be checked is db_schema:{getattr}, not db_tuple:{select}.

(BTW, SE-PostgreSQL disallow to modify system catalogs with regular DML due to the security reason, see the Restrictions for more details.)

Common object behavior

Object class is a terminology which means a certain type of object, such as files, sockets, processes, tables in databases and so on. Every object classes have its own set of permissions that reflect characteristics of the object class. However, several permissions are conventionally defined to handle common object behavior; creation, deletion, setting and getting attributes, and relabeling the security context that is a special case of setting attribute.

In addition, we note about the way to handle unlabeled objects independent from object classes.

Creation (create)

At the creation of database objects, SE-PostgreSQL shall assign a certain security context on the new database object. If an explicit security context is given (using SECURITY_CONTEXT option for instance), SE-PostgreSQL assigns it on the new object as long as it has correct format. Otherwise, SE-PostgreSQL assigns a default security context that is computed based on the security policy. Then, SE-PostgreSQL checks db_xxx:{create} permission on the new object itself labeled as the default or user given security context. Note that it anyway checks db_xxx:{create} permission independent from the way to give the security context of the new object.

Some of routines can create a temporary table to implement ALTER TABLE which changes definition of the column, and so on. In this case, it is not a user visible creation, so SE-PostgreSQL does not apply any permission checks on creation and deletion of the temporary tables. (However, it checks db_column:{setattr} permission on the column on which user tries to update the attribute.)

Deletion (drop)

At the deletion of database object, SE-PostgreSQL shall check db_xxx:{drop} permission on the object itself to be dropped. It is also checked, even if the database object is dropped due to the deletions in cascaded. An exception is the deletions due to the system internal operations, such as cleaning up temporary objects on session closed, removing tuples on table drops and so on. Because SE-PostgreSQL checks and controls user's query, but it does not prevent operations by system internals.

Get-attributes (getattr)

PostgreSQL allows users to refer the system catalogs which store attribute of database objects, using regular SELECT statement. In this case, db_xxx:{getattr} permission shall be checked as a part of row-level access controls. Note that db_tuple:{select} is not applied for the database objects which have its own object class due to the principle as noted before.

Set-attributes (setattr)

When attributes of database objects are updated, db_xxx:{setattr} permission shall be checked. Typically, this permission is checked on ALTER statement. Note that CREATE OR REPLACE FUNCTION statement can replace definition of an existing procedure. In this case, SE-PostgreSQL shall check the db_procedure:{setattr} permission although CREATE statement was given, because it only updates an existing definition. Some of ALTER statement requires multiple permissions at same time. For example, ALTER TABLE ... ADD COLUMN ... statement updates the definition of table and creates a new column, so it needs to check db_table:{setattr} and db_column:{create} permissions at the same time.

Relabeling (relabelfrom relabelto)

It is a special case of set attribute when user tries to change the security context of the database object. In this case, db_xxx:{relabelfrom} permission shall be checked on the object labeled as the older security context, in addition to db_xxx:{setattr} permission. The db_xxx:{relabelto} permission shall be also checked on the object labeled as the newer security context.

Unlabeled objects

If a user create a new database object while SE-PostgreSQL is disabled, no security context shall be assigned on the database object. If the user turned on SE-PostgreSQL later, a pseudo security context is applied on accesses to the unlabeled database objects. To be correct, if SE-PostgreSQL detects a certain database object has invalid or no security context, it is handled as if the pseudo security context (called unlabeled context) is assigned on. In the default security policy, system_u:object_r:unlabeled_t:s0 shall be used.

Database object

The db_database object class shall be assigned on the object within pg_database system catalog. It inherits the common object behaviors, and three characteristic permissions.

Here is no special instruction for the common object behaviors. These are checked on CREATE DATABASE, ALTER DATABASE and DROP DATABASE statements, then it raises an error if violated.

default security context

The default security context of db_database object class shall be computed based on the security context of the database client and the root directory of the database cluster which is labeled as system_u:object_r:postgresql_db_t:s0 in normal case.

access

The db_database:{access} permission shall be checked when the database client tries to connect a certain database during initialization of the server backend process. If violated, SE-PostgreSQL raises an error. It prevent the database client to log on a certain database without this privilege.

load_module

This is not supported at the CommitFest-2009Sep.

The db_database:{load_module} permission shall be checked when an external module is loaded due to the user's query; invocation of procedure implemented in the external module or LOAD statement. This permission is defined on a pair of the database and the external module, so not the database client. In other word, it checks capability of the database to load a certain external module. If violated, SE-PostgreSQL raises as error.

Note that this permission is not checked on loaded modules due to the shared_preload_libraries, because it is not controllable from the user's query. But it is also checked for local_preload_libraries, because the libpq protocol allows to suggest the backend on starting up, although it is not a query in strictly.

superuser

The db_database:{superuser} permission is checked when the database client tries to perform as a database superuser that can bypass all the DAC permission checks. If violated, SE-PostgreSQL prevent them to perform as a database superuser, so the client shall perform as unprivileged users. Note that this check does not raise an error.

SQL Enhancements

CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] dbowner ]
         [ TEMPLATE [=] template ]
         [ ENCODING [=] encoding ]
         [ LC_COLLATE [=] lc_collate ]
         [ LC_CTYPE [=] lc_ctype ]
         [ TABLESPACE [=] tablespace ]
         [ CONNECTION LIMIT [=] connlimit ] ]
         [ SECURITY_CONTEXT [=] security_context ]

It enables to create a new database labeled as security_context.

ALTER DATABASE name SECURITY_CONTEXT TO security_context

It enables to relabel the security context of database to security_context.

Schema object

The db_schema (or db_schema_temp for temporary namespace) object class shall be assigned on the object within pg_namespace system catalog. It inherits the common object behaviors, and three characteristic permissions.

Here is no special instruction for the common object behaviors. These are checked on CREATE SCHEMA, ALTER SCHEMA and DROP SCHEMA statements, then it raises an error if violated.

default security context

The default security context of db_schema object class shall be computed based on the security context of the database client and the database which stores the new schema.

search

The db_schema:{search} permission shall be checked when user's query tries to use any database objects stored in the schema object. If violated, this schema is skipped from the schema search path, but SE-PostgreSQL does not raise an error.

add_name

This is not supported at the CommitFest-2009Sep.

The db_schema:{add_name} permission shall be checked when user's query tries to create a new database object within the schema to be checked, tries to move the database object to the schema to be checked, or tries to rename the database object in the schema to be checked. If violated, SE-PostgreSQL raises an error.

remove_name

This is not supported at the CommitFest-2009Sep.

The db_schema:{remove_name} permission shall be checked when user's query tries to drop any database object within the schema to be checked, tries to move the database object from the schema to be checked, or tries to rename the database object in the schema to be checked.If violated, SE-PostgreSQL raises an error.

These characteristic three permissions are the analogy of directories on filesystem, and enables to control creation, deletion and usage of all the database object in a certain schema.

SQL Enhancements

CREATE SCHEMA schemaname [ AUTHORIZATION username ]
   [ SECURITY_CONTEXT security_context ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION username
   [ SECURITY_CONTEXT security_context ] [ schema_element [ ... ] ]

It enables to create a new schema labeled as security_context.

ALTER SCHEMA name SECURITY_CONTEXT TO security_context

It enables to relabel the security context of schema to security_context.

Table object

The db_table object class shall be assigned on the object within pg_class system catalog and its pg_class.relkind equals to RELKIND_RELATION. It inherits the common object behaviors, and six characteristic permissions.

The common object permissions are checked on CREATE TABLE, ALTER TABLE and DROP TABLE statements, then is raises an error if violated.

It is necessary to pay attention these operation can access multiple objects in same time. For example, when we run ALTER TABLE to add a new column to the table, it creates a new column object and updates the table object to increment pg_class.relnatts. In this case, both of db_table:{setattr} on the table and db_column:{create} on the new column need to be allowed.

default security context

The default security context of db_table object class shall be computed based on the security context of the database client and the schema which stores the new table.

select

The db_table:{select} permission shall be checked when user's query tries to refer certain tables using SELECT, COPY TO and any other kind of references such as WHERE and RETURNING clause with UPDATE statement. If violated, SE-PostgreSQL raises an error.

The target tables to be checked are equivalent to the ones picked up by the native database privilege mechanism. SE-PostgreSQL checks db_table:{select} permissions on all the tables reference by RangeTblEntry with ACL_SELECT being set on the requiredPerms. In the case of COPY TO, the target table is obvious.

update

The db_table:{update} permission shall be checked when user's query tries to update certain tables using UPDATE. Note that db_table:{select} is also necessary, if UPDATE has WHERE or RETURNING clause. If violated, SE-PostgreSQL raises an error.

The target tables to be checked are equivalent to the ones picked up by the native database privilege mechanism. SE-PostgreSQL checks db_table:{update} permission on all the tables reference by RangeTblEntry with ACL_UPDATE being set on the requiredPerms and modifiedCols being not empty. Note that ACL_UPDATE is aliased to ACL_SELECT_FOR_UPDATE which is set on SELECT ... FOR SHARE/UPDATE, so it is necessary to branch conditionally here, because the direction of information flow is contrary between these two statements.

insert

The db_table:{insert} permission shall be checked when user's query tries to insert into certain tables using INSERT, COPY FROM and SELECT INTO. Note that db_table:{select} is also necessary, if INSERT has RETURNING clause. If violated, SE-PostgreSQL raises an error.

The target tables to be checked are equivalent to the ones picked up by the native database privilege mechanism. SE-PostgreSQL checks db_table:{insert} permission on all the tables referenced by RangeTblEntry with ACL_INSERT on the requiredPerms. In the case of COPY TO and SELECT INTO, the target table is obvious. Note that the native database privilege mechanism does not check INSERT permission on the table newly created with SELECT INTO because it is obviously allowed. However, SE-PostgreSQL needs to check it, because the security policy may prevent to insert on the table labeled as the default security context. (It may be insane security policy, but SE-PostgreSQL needs to follow the decision.)

delete

The db_table:{delete} permission shall be checked when user's query tries to delete from certain tables using DELETE or TRUNCATE. Note that db_table:{select} is also necessary, if DELETE has WHERE or RETURNING clause. If violated, SE-PostgreSQL raises an error.

The target tables to be checked are equivalent to the ones picked up by the native database privilege mechanism. SE-PostgreSQL checks db_table:{delete} permission on all the tables referenced by RangeTblEntry with ACL_DELETE on the requiredPerms. In the case of TRUNCATE, the target table is obvious. SE-PostgreSQL considers the TRUNCATE is equivalent to unconditional deletion. So, it also checks whether here is no tuples unable to delete in row-level access controls.

lock

The db_table:{lock} permission shall be checked when user's query tries to acquire explicit table locks using LOCK or SELECT ... FOR SHARE/UPDATE. Note that it does not check anything on the implicit table locks acquired during regular operations.

reference

The db_table:{reference} permission shall be checked when user's query tries to set up FK constraint on the table. If violated, SE-PostgreSQL raises an error.

SQL Enhancement

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( [
  { column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ] [ SECURITY_CONTEXT column_context ]
    | table_constraint
    | LIKE parent_table [ { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES } ] ... }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace ]
[ SECURITY_CONTEXT table_context ]

It enables to create a new table labeled as security_context.

ALTER TABLE name SECURITY_CONTEXT TO security_context
ALTER TABLE name ALTER [ COLUMN ] column SECURITY_CONTEXT TO security_context

It enables to relabel the security context of the table to security_context.

Sequence object

The db_sequence object class shall be assigned on the object within pg_class system catalog and its pg_class.relkind equals to RELKIND_SEQUENCE. It inherits the common object behaviors, and three characteristic permissions.

Here is no special instruction for the common object behaviors. These are checked on CREATE SEQUENCE, ALTER SEQUENCE and DROP SEQUENCE statements, then it raises an error if violated.

default security context

The default security context of db_sequence object class shall be computed based on the security context of the database client and the schema which stores the new sequence.

get_value

The db_sequence:{get_value} permission shall be checked, when user's query tries to refer a certain sequence object using currval(), lastval() or SELECT statement. If violated, SE-PostgreSQL raises an error.

next_value

The db_sequence:{next_value} permission shall be checked, when user's query tries to fetch a value from a certain sequence object using nextval(). If violated, SE-PostgreSQL raises an error.

set_value

The db_sequence:{set_value} permission shall be checked, when user's query tries to set a discretionary value using setval(). If violated, SE-PostgreSQL raises an error.

SQL Enhancement

CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
    [ OWNED BY { table.column | NONE } ]
    [ SECURITY_CONTEXT security_context ]

It enables to create a new sequence labeled as security_context.

ALTER SEQUENCE name SECURITY_CONTEXT TO security_context

It enables to relabel the security context of the sequence to security_context.

Procedure object

The db_procedure object class shall be assigned on the object within pg_proc system catalog. It inherits the common object behaviors, and three characteristic permissions.

Here is no special instruction for the common object behaviors. These are checked on CREATE FUNCTION, ALTER FUNCTION and DROP FUNCTION statements, then it raises an error if violated.

default security context

The default security context of db_procedure object class shall be computed based on the security context of the database client and the schema which stores the new procedure.

execute

The db_procedure:{execute} permission shall be checked, when user's query tries to execute procedures which includes implementations of operator.

The target procedures to be checked are mostly equivalent to the pg_proc_aclcheck(..., ACL_EXECUTE), but it is not quite equivalent at a few points. The ACL_EXECUTE is often checked when user defines a new database object which internally uses a certain function, such as DefineOperator(), CreateConversionCommand() and so on.

The db_procedure:{execute} permission is defined between a certain function and a database client which actually invokes the function, but it does not mean a permission to allow everyone to invoke the function as a part of system internals, such as conversions. In this case, db_procedure:{install} should be used instead.

In addition, has_function_privilege() also checks ACL_EXECUTE, but db_procedure:{execute} should not be checked here.

entrypoint

The db_procedure:{entrypoint} permission shall be checked, when user's query tries to invoke a trusted procedure. Note that db_procedure:{execute} is also necessary to invoke the trusted procedure.

See the Trusted procedure section for more details.

install

This is not supported at the CommitFest-2009Sep.

The db_procedure:{install} permission shall be checked, when user's query tries to define a database object which can internally invoke user defined functions without db_procedure:{execute} checks on runtime.

This permission check minimizes the risk to install malicious functions as a part of system internal stuffs, then somebody implicitly invokes them.

In particular, these functions should be checked; type handlers, cast functions, conversion functions, foreign data wrapper validator, trigger functions and tsearch parser/template functions.

untrusted

The db_procedure:{untrusted} permission shall be checked, when user's query tries to create or replace a certain function with untrusted language (such as C), in addition to db_procedure:{create} or db_procedure:{setattr}.

Because SE-PostgreSQL cannot prevent accesses by system internal stuff, it is necessary to minimize the risk that a malicious functions are defined.

SQL Enhancement

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } defexpr ] [, ...] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( colname coltype [, ...] ) ]
  { LANGUAGE langname
    | WINDOW
    | IMMUTABLE | STABLE | VOLATILE
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | COST execution_cost
    | ROWS result_rows
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
    | SECURITY_CONTEXT security_context
  } ...
    [ WITH ( attribute [, ...] ) ]

It enables to create a new procedure labeled as security_context.

ALTER FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
    SECURITY_CONTEXT TO security_context

It enables to relabel the security context of the procedure to security_context.

Column object

The db_column object class shall be assigned on the object within pg_attribute system catalog and its attrelid points to a certain Table object. It inherits the common object behaviors, and four characteristic permissions. Note that db_column:{delete} permission is not defined.

The common object permissions are checked on CREATE TABLE, ALTER TABLE and DROP TABLE, then it raises an error if violated.

In most cases, the column objects are created and dropped at the same time with the table which contains the columns, so it is necessary the permissions on tables are also allowed, not only ones for columns. The ALTER TABLE has various options to create and drop columns, not only ones to set up column's attribute. In this case, db_column:{create} and db_column:{drop} shall be checked although ALTER statement was used.

default security context

The default security context of db_column object class shall be computed based on the security context of the database client and the table which contains the new column.

select

The db_column:{select} permission shall be checked on the columns, when user's query tries to refer certain columns using SELECT, COPY TO and any other kind of references such as WHERE or RETURNING clause with UPDATE statement. If violated, SE-PostgreSQL raises an error.

The target columns to be checked are equivalent to the ones picked up by the native database privilege mechanism, except for it is also checked even if db_table:{select} is allowed. SE-PostgreSQL checks db_column:{select} permissions on all the columns marked by the selectedCols of RangeTblEntry with ACL_SELECT on the requiredPerms. In the case of COPY TO, the target columns are obvious.

update

The db_column:{update} permission shall be checked on the columns, when user's query update certain columns using UPDATE. If violated, SE-PostgreSQL raises an error.

The target columns to be checked are equivalent to the ones picked up by the native database privilege mechanism, except for it is also checked even if db_table:{update} is allowed. SE-PostgreSQL checks db_column:{update} permissions on all the columns marked by the modifiedCols of RangeTblEntry with ACL_UPDATE on the requiredPerms.

insert

The db_column:{insert} permission shall be checked on the columns, when user's query update certain columns using INSERT, COPY TO and SELECT INTO. If violated, SE-PostgreSQL raises an error.

The target columns to be checked are equivalent to the ones picked up by the native database privilege mechanism, except for it is also checked even if db_table:{insert} is allowed. SE-PostgreSQL checks db_column:{insert} permissions on all the columns marked by the modifiedCols of RangeTblEntry with ACL_INSERT on the requiredPerms.

reference

The db_column:{reference} permission shall be checked on the columns, when user's query tries to set up FK constraints on the columns. If violated, SE-PostgreSQL raises an error.

SQL Enhancement

See the Table object for details.

Tuple object

The db_tuple object class shall be assigned on any objects stored in regular tables, but don't have its characteristic object class. It inherits the common object behaviors, except for its permission names, but no characteristic permissions.

select

The db_tuple:{select} permission shall be checked, when user's query tries to refer using SELECT, COPY TO and any other kind of references such as WHERE or RETURNING clause with UPDATE statement. It is an equivalent permission to db_xxx:{getattr} in other object class.

update

The db_tuple:{update} permission shall be checked, when user's query tries to update using UPDATE. It is an equivalent permission to db_xxx:{setattr} in other object class.

insert

The db_tuple:{insert} permission shall be checked, when user's query tries to insert using INSERT, SELECT INTO or COPY FROM. It is an equivalent permission to db_xxx:{create} in other object class. A default security context shall be assigned on the new tuples, if no explicit security context given. Then, it is checked on the tuples labeled.

delete

The db_tuple:{delete} permission shall be checked, when user's query tries to delete using DELETE or TRUNCATE. It is an equivalent permission to db_xxx:{drop} in other object class.

relabelfrom

It is an equivalent permission to db_xxx:{relabelfrom} in other object class.

relabelto

It is an equivalent permission to db_xxx:{relabelto} in other object class.

Tablespace object

The db_tablespace object class shall be assigned on the object within pg_tablespace system catalog.

It inherits the common object behaviors, and a characteristic permission. Here is no special instruction for the common object behaviors. These are checked on CREATE TABLESPACE, ALTER TABLESPACE and DROP TABLESPACE statements, then it raises an error if violated.

default security context

The default security context of db_tablespace object class shall be computed based on the security context of the database client and the root directory of the database cluster which is labeled as system_u:object_r:postgresql_db_t:s0 in normal case.

createon

The db_tablespace:{createon} permission shall be checked, when user's query tries to use a certain tablespace except for default one. It is equivalent to the ACL_CREATE on the native database privilege mechanism.

SQL Enhancement

CREATE TABLESPACE tablespacename [ OWNER username ]
    [ SECURITY_CONTEXT security_context ] LOCATION 'directory'

ALTER TABLESPACE name SECURITY_CONTEXT TO security_context

Largeobject object

The db_blob object class shall be assigned on the largeobject which consists of several pageframes stored in pg_largeobject system catalog. In inherits the common object behaviors, and four characteristic permissions.

default security context

The default security context of db_blob object class shall be computed based on the security context of the database client and the database which contains the new largeobejct.

read

The db_blob:{read} permission shall be checked, when user's query tries to read the contents of the largeobject using loread() or lo_export().

write

The db_blob:{write} permission shall be checked, when user's query tries to write the contents of the largeobject using lowrite(), lo_truncate() or lo_import().

import

The db_blob:{import} permission shall be checked, when user's query tries to import a certain filesystem object to the largeobject newly created. Note that db_blob:{write} and file:{read} on the source file shall be also checked.

export

The db_blob:{export} permission shall be checked, when user's query tries to export the largeobject to a certain filesystem object. Note that db_blob:{read} and file:{write} on the destination file shall be also checked.

Filesystem interactions

PostgreSQL allows users to access server side filesystem using a few interfaces.

read

The file:{read} permission shall be checked, when user's query tries to read a certain file on the server side filesystem. Note that this check is applied between the security context of the database client and the security context of the target file. This check is necessary to control information flows from the target file to the database client via PostgreSQL server process.

In particular, this permission is checked on COPY FROM filename, lo_import() and pg_read_file().

write

The file:{write} permission shall be checked, when user's query tries to write a certain file on the server side filesystem. Note that this check is applied between the security context of the database client and the security context of the target file. This check is necessary to control information flows from the database client to the target file via PostgreSQL server process.

In particular, this permission is checked on COPY TO filename and lo_export() function.

Management of security context

SE-PostgreSQL shall assign a security context on database objects, and manages them correctly.

This feature is as significant as making access control decisions, because it is the criterion of all the mandatory access controls.

security_context system column

The security_context system column is an interface to import and export the security context of database objects. It is declared as TEXT type, and writable although it is a system column.

This example shows a security context of a certain table.

postgres=# SELECT security_context, relname, relkind
                  FROM pg_class WHERE oid = 'drink'::regclass;
             security_context             | relname | relkind
------------------------------------------+---------+---------
 unconfined_u:object_r:sepgsql_table_t:s0 | drink   | r
(1 row)

The security_context system column can be used to give an explicit security context for newly inserted tuples.

postgres=# INSERT INTO drink (security_context, id, name, price)     \
               VALUES ('unconfined_u:object_r:sepgsql_table_t:s0:c0', 1, 'green tea', 150);
INSERT 16440 1
postgres=# SELECT security_context, * FROM drink;
              security_context               | id |   name    | price
---------------------------------------------+----+-----------+-------
 unconfined_u:object_r:sepgsql_table_t:s0:c0 |  1 | green tea |   150
(1 row)

The security_context system column can accept any text input as long as it has correct format as a security context.

postgres=# UPDATE drink SET security_context = 'unconfined_u:object_r:sepgsql_table_t:s0:c' || id;
UPDATE 1
postgres=# SELECT security_context, * FROM drink;
              security_context               | id |   name    | price
---------------------------------------------+----+-----------+-------
 unconfined_u:object_r:sepgsql_table_t:s0:c1 |  1 | green tea |   150
(1 row)

postgres=# UPDATE drink SET security_context = 'this is invalid security context';
ERROR:  Invalid security context: "this is invalid security context"

If the target list of SELECT INTO contains an attribute name as security_context, its value shall be used to give an explicit security context for the newly inserted tuples. It also accepts TEXT type value as long as it has correct format.

postgres=# SELECT 'unconfined_u:object_r:sepgsql_ro_table_t:s0:c' || id AS security_context, * INTO t FROM drink;
SELECT
postgres=# SELECT security_context, * FROM t;
                security_context                | id |   name    | price
------------------------------------------------+----+-----------+-------
 unconfined_u:object_r:sepgsql_ro_table_t:s0:c1 |  1 | green tea |   100
 unconfined_u:object_r:sepgsql_ro_table_t:s0:c2 |  2 | coke      |   120
 unconfined_u:object_r:sepgsql_ro_table_t:s0:c3 |  3 | juice     |   130
 unconfined_u:object_r:sepgsql_ro_table_t:s0:c4 |  4 | cofee     |   180
(4 rows)

pg_security system catalog

One of the characteristics of security context is massive number of objects shares a limited number of security contexts. SELinux always makes an identical access control decision on accesses to different objects labeled as an identical security context. It means there are no differences from the viewpoint of access controls.

A typical security context is a short text about 40 - 60 bytes length. If we store them as a text form for each database objects (including user tuples), it can be a factor to consume storage space uselessly.

The pg_security system catalog enables to store text formed security contexts, instead of any other tables.

Here is the definition of pg_security.

#define SecurityRelationId        3400

CATALOG(pg_security,3400) BKI_SHARED_RELATION BKI_WITHOUT_OIDS
{
    /* Identifier of the security attribute  */
    Oid     secid;

    /* OID of the database which refers the entry */
    Oid     datid;

    /* OID of the table which refers the entry */
    Oid     relid;

    /* Text representation of security attribute */
    text    secattr;
} FormData_pg_security;

Any database objects can have a security identifier (an Oid; 4-bytes length), instead of the security context in text form. It is stored in the variable length field of the HeapTupleHeader structure, as if oid doing.

struct HeapTupleHeaderData
+---------------------------------+ +0
| union {                         |
|     HeapTupleFields     t_heap; |
|     DatumTupleFields    t_datum;|
| }           t_choice;           |
+---------------------------------+ +12
| ItemPointerData t_ctid;         |
+---------------------------------+ +18
| uint16          t_infomask2;    |
+---------------------------------+ +20
| uint16          t_infomask;     |
+---------------------------------+ +22
| uint8           t_hoff;        o--------------------+
+---------------------------------+ +23               |
| NULL bitmaps                    |                   |
:                                 :                   |
|                                 |                   |
+---------------------------------+ +(t_hoff - 8)     |
| Oid     security identifier     |                   |
+---------------------------------+ +(t_hoff - 4)     |
| Oid     object identifier       |                   |
+---------------------------------+ +(t_hoff)   <-----+
| Data contents                   |
:                                 :

The security identifier of the database objects indicates the pg_security.secid. The security context in text form is associated with a combination of secid, datid (OID of the database which contains the database object referring the security context) and relid (OID of the relation which contains the database object referring the security context). The datid and relid is used to narrow down the scope of database objects which refers a certain security context, to reclaim it when it get being unreferenced.

This feature also enables to make access control decision quickly, not only reduction of the storage consumption.

The access vector cache (AVC; see Access vector cache for more details) enables to cache access control decision recently used in the userspace. If security context is represented as a security identifier, SE-PostgreSQL can looks up the cached entry using integer comparison, not text comparison. From the previous measurement, it has unignorable effects in performance.

New security context

If user gives a new security context which is not found on the pg_security using INSERT, CREATE or ALTER statement, the backend appends a new entry into the pg_security and assigns a new security identifier for the inserted or updated database objects.

From the user viewpoint, it is handled as if TEXT data is accepted.

Reclaim orphan entries

SE-PostgreSQL provides a function to reclaim orphan security contexts.

If a certain security context entry is not referenced in the relation identified by relid of the database identified by datid, we can remove the entry.

postgres=# select security_reclaim_context('t1');
 security_reclaim_context
--------------------------
                        6
(1 row)

It is equivalent to the following query, but user cannot modify pg_security directly, without using a certain function.

LOCK tablename IN SHARE MODE

DELETE FROM pg_security
    WHERE datid = MyDatabaseId
    AND relid = tablename::regclass
    AND secid NOT IN (SELECT security_context_to_secid(tablename) FROM tablename)

Mcstrans support

The security context has two forms. One is the raw-format which is used to communicate with SELinux in the kernel. The other is trans-format which is used to print the security context for users and to accept user given input.

An example of raw-format:

system_u:object_r:sepgsql_table_t:s0:c0

An example of trans-format:

system_u:object_r:sepgsql_table_t:Classified

The most right field of the security context is called range. It is a pair of the sensitivity (like s0) and category (like c0). If the mcstrans daemon is available on the system, it provides a facility to translate between two formats each other.

The range field is used to implement traditional multilevel security policy, so it is also used to communicate any other proprietary unix system with mac support, using labeled networking feature. Accordingly, it is necessary to translate the format of the range field when importing or exporting the security context.

When the mcstrans daemon is available on the system, SE-PostgreSQL can accept security context in the trans-format, and can print security context in the trans-format. However, it always stores the security context in the raw-format, because the trans-format is only available when mcstrans is available, and kernel cannot accept it.

Misc features

Row-level access controls

The row-level access controls perform as if a filter which drops any violated tuples on scanning the target tables.

See the following example:

postgres=# SELECT security_context, * FROM drink;
                security_context                | 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:Classified   |  3 | beer  |   240
 system_u:object_r:sepgsql_table_t:Classified   |  4 | wine  |   380
(4 rows)

If the database client can see the tuples labeled as Unclassified and Classified, it returns four tuples.

On the other hand, if the database client can see only Unclassified, any tuples labeled as Classified are filtered from the result set.

postgres=# SELECT security_context, * FROM drink;
                security_context                | 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
(2 rows)

It is also applied on UPDATE and DELETE statement on scanning the target tables, not only SELECT statement. SE-PostgreSQL makes its access control decision on the fetched tuples prior to evaluation of user given conditions using WHERE clause, because user can give malicious functions which leaks its arguments to others. (Note that we assume index access methods are trusted, so here is no matter that row-level access controls are applied after the index accesses.)

The INSERT statement does not scan on the target tables, so it is not available to implement it as a filter. If user tries to insert a tuple with a certain security context, SE-PostgreSQL shall raise an error on violations.

When user tries to relabel security context using UPDATE statement, it shall also raise an error on violations to db_tuple:{relabelfrom relabelto} permissions. Because it is necessary to check the permissions after all the before-row-triggers, SE-PostgreSQL cannot make its decision on the scanning time.

Exception

The foreign-key constraints are implemented as trigger functions, and it launches the secondary queries to check and keep referential integrity. In this case, it is undesirable to filter out invisible tuples, because it may reference the tuple which user tries to update or delete. Accordingly, SE-PostgreSQL switches the behavior on row-level access controls during the foreign-key constraint checks. It raises an error on access violation in this case. Note that it is necessary to make access control decision after all the given condition checks, because it may raise an error due to the unrelated tuples. Here, an assumption is system internal stuff never inject malicious functions in the WHERE clause.

Trusted Procedure

The idea of trusted procedure enables to switch the security context of the database client temporary, as if the database privilege mechanism provides security definer functions.

The security definer functions switches an effective database role during execution of the function, and it affects access control decisions by the database privilege mechanism. In a similar manner, the trusted procedures switches security context of the database client during execution of the function. Please remind the security context of the database client is equivalent to a set of privileges for mandatory access controls.

At the default security policy, sepgsql_trusted_proc_exec_t is the only previously-defined type as a trusted procedure. It enables to provide a secure method to access invisible or read-only data.

For example, when the customer table is defined as follows, unprivileged users cannot see the contents of customer.credit because it is labeled as sepgsql_secret_table_t which means all the accesses are denied from confined domains.

CREATE TABLE customer (
    cid     integer primary key,
    cname   varchar(32),
    credit  varchar(32)
        SECURITY_CONTEXT = 'system_u:object_r:sepgsql_secret_table_t:s0'
);

Because the customer.credit stores credit card numbers of customers, we don't want to expose them web applications. But online shopping application needs to show a part of numbers users to confirm it. In this case, we can declare a trusted procedure for the purpose as follows:

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 regexp_replace(credit, -[0-9]+, -xxxx, g) FROM customer WHERE cid = $1';

It performs as follows:

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

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

Column level access control prevents confined database client to access customer.credit. But, the security context of the database client is temporary switched during execution of the show_credit(), and it returns the secret data partially hidden.

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)

Audit logs

When SE-PostgreSQL prevents user's query due to the access violation, it generates audit logs as follows:

LOG:  SELinux: denied { select } scontext=unconfined_u:unconfined_r:httpd_t:s0  \
                      tcontext=system_u:object_r:sepgsql_secret_table_t:s0      \
                      tclass=db_column name=customer.credit
STATEMENT:  select * from customer;

It records a fact that a database client with unconfined_u:unconfined_r:httpd_t:s0 tries to refer the customer.credit column labeled with system_u:object_r:sepgsql_secret_table_t:s0, but failed.

The security policy provides two kind of rules to control generation of audit logs. One is auditdeny rule to generate audit logs on access denied events. The other is auditallow rule to generate it on access allowed events. In the default, all the access denied events are recorded, and others are not recorded.

In addition, the default security policy also turned off access denied logs on db_tuple object class to suppress flood of audit logs, because a user's query can access large number of tuples at one time.

Access vector cache

When SE-PostgreSQL communicates with SELinux in the kernel, it needs context-switching due to the system call invocation. However, it is a heavy operation in generally, so it is necessary to reduce the number of system call invocations to minimize the adverse effect in performance. Especially, a query can fetch massive number of tuples at the same time, it is important to make its decision quickly.

The idea of AVC (Access Vector Cache) is to cache access control decisions recently used. In most cases, massive number of objects tend to share a limited number of security contexts, and SELinux returns an identical result for a pair of identical security contexts. So, we can expect most of access control decisions does not require kernel invocations. (Empirically, more than 99.99% of access control decision hits AVC.)

When the client gives a query, SE-PostgreSQL needs to check its privileges on the target database objects. At first, it looks up the AVC for the given pair of security contexts. If found, it can make access control decision immediately. Otherwise, it invoke SELinux in the kernel according to the communication protocol.

The AVC also has a good characteristic. It enables to lookup the cache table using security identifier, without any string comparison in security context. It means we don't need to translate a security identifier into the corresponding security context in text form whenever SE-PostgreSQL makes its decision.

Restrictions

Please be aware that a few restrictions when using SE-PostgreSQL.

It applies a few hardwired rules on accesses to system catalogs and toast relations. It prevents to modify system catalogs using general DML statements (INSERT, UPDATE and DELETE), so users need to use regular DDL statements instead. It also prevents to access toast relations using general DML statements (including SELECT), so only internal stuffs can access them.

It needs to disable several kind of optimization for the security reason. A SQL function declared with SQL language can be flatten and inlined by optimizer, but SE-PostgreSQL restrains this kind of optimization, if the function is labeled as trusted procedure.

(TODO: Put here any other cases need to disable optimizations)

Interactions between row-level access controls and unique or foreign key constrains may give a hint for existence of invisible tuples, although user cannot see the data itself. For example, when we tries to insert a new tuple with duplicated key value to an invisible tuple, it will raise an error, so we can know the existence of invisible tuples. Such kind of irregular information flows are called covert channels. It is also mentioned in the common criteria, but not required except for an extreme environment in use. Note that SE-PostgreSQL does not care about elimination of covert channels.

SEPostgreSQL Development

Contents

Brief overview

Security model

Security context

Security policy

Mandatory access controls

Common principles

Common object behavior

Database object

Schema object

Table object

Sequence object

Procedure object

Column object

Tuple object

Tablespace object

Largeobject object

Filesystem interactions

Management of security context

security_context system column

pg_security system catalog

Mcstrans support

Misc features

Row-level access controls

Trusted Procedure

Audit logs

Access vector cache

Restrictions

Navigation menu

Page actions

Page actions

Personal tools

Navigation

Tools

Search