<!-- doc/src/sgml/sepgsql.sgml -->

<sect1 id="sepgsql" xreflabel="sepgsql">
 <title>sepgsql</title>

 <indexterm zone="sepgsql">
  <primary>sepgsql</primary>
 </indexterm>

 <para>
  <filename>sepgsql</> is a loadable module that supports label-based
  mandatory access control (MAC) based on <productname>SELinux</> security
  policy.
 </para>

 <warning>
   <para>
     The current implementation has significant limitations, and does not
     enforce mandatory access control for all actions.  See
     <xref linkend="sepgsql-limitations">.
   </para>
 </warning>

 <sect2 id="sepgsql-overview">
  <title>Overview</title>

  <para>
   This module integrates with <productname>SELinux</> to provide an
   additional layer of security checking above and beyond what is normally
   provided by <productname>PostgreSQL</productname>.  From the perspective of
   <productname>SELinux</>, this module allows
   <productname>PostgreSQL</productname> to function as a user-space object
   manager.  Each table or function access initiated by a DML query will be
   checked against the system security policy.  This check is in addition to
   the usual SQL permissions checking performed by
   <productname>PostgreSQL</productname>.
  </para>

  <para>
   <productname>SELinux</productname> access control decisions are made using
   security labels, which are represented by strings such as
   <literal>system_u:object_r:sepgsql_table_t:s0</>.  Each access control
   decision involves two labels: the label of the subject attempting to
   perform the action, and the label of the object on which the operation is
   to be performed.  Since these labels can be applied to any sort of object,
   access control decisions for objects stored within the database can be
   (and, with this module, are) subjected to the same general criteria used
   for objects of any other type, such as files.  This design is intended to
   allow a centralized security policy to protect information assets
   independent of the particulars of how those assets are stored.
  </para>

  <para>
   The <xref linkend="sql-security-label"> statement allows assignment of
   a security label to a database object.
  </para>

 </sect2>
 <sect2 id="sepgsql-installation">
  <title>Installation</title>

  <para>
    <filename>sepgsql</> can only be used on <productname>Linux</productname>
    2.6.28 or higher with <productname>SELinux</productname> enabled.
    It is not available on any other platform.  You will also need
    <productname>libselinux</> 2.0.99 or higher and
    <productname>selinux-policy</> 3.9.13 or higher (although some
    distributions may backport the necessary rules into older policy
    versions).
  </para>

  <para>
   The <command>sestatus</> command allows you to check the status of
   <productname>SELinux</productname>.  A typical display is:
<screen>
$ sestatus
SELinux status:                 enabled
SELinuxfs mount:                /selinux
Current mode:                   enforcing
Mode from config file:          enforcing
Policy version:                 24
Policy from config file:        targeted
</screen>
   If <productname>SELinux</> is disabled or not installed, you must set
   that product up first before installing this module.
  </para>

  <para>
   To build this module, include the option <literal>--with-selinux</> in
   your PostgreSQL <literal>configure</> command.  Be sure that the
   <filename>libselinux-devel</> RPM is installed at build time.
  </para>

  <para>
   To use this module, you must include <literal>sepgsql</>
   in the <xref linkend="guc-shared-preload-libraries"> parameter in
   <filename>postgresql.conf</>.  The module will not function correctly
   if loaded in any other manner.  Once the module is loaded, you
   should execute <filename>sepgsql.sql</filename> in each database.
   This will install functions needed for security label management, and
   assign initial security labels.
  </para>

  <para>
   Here is an example showing how to initialize a fresh database cluster
   with <filename>sepgsql</> functions and security labels installed.
   Adjust the paths shown as appropriate for your installation:
  </para>

<screen>
$ export PGDATA=/path/to/data/directory
$ initdb
$ vi $PGDATA/postgresql.conf
  change
    #shared_preload_libraries = ''                # (change requires restart)
  to
    shared_preload_libraries = 'sepgsql'          # (change requires restart)
$ for DBNAME in template0 template1 postgres; do
    postgres --single -F -c exit_on_error=true $DBNAME \
      &lt;/usr/local/pgsql/share/contrib/sepgsql.sql &gt;/dev/null
  done
</screen>

  <para>
   Please note that you may see some or all of the following notifications
   depending on the particular versions you have of
   <productname>libselinux</> and <productname>selinux-policy</>:
<screen>
/etc/selinux/targeted/contexts/sepgsql_contexts:  line 33 has invalid object type db_blobs
/etc/selinux/targeted/contexts/sepgsql_contexts:  line 36 has invalid object type db_language
/etc/selinux/targeted/contexts/sepgsql_contexts:  line 37 has invalid object type db_language
/etc/selinux/targeted/contexts/sepgsql_contexts:  line 38 has invalid object type db_language
/etc/selinux/targeted/contexts/sepgsql_contexts:  line 39 has invalid object type db_language
/etc/selinux/targeted/contexts/sepgsql_contexts:  line 40 has invalid object type db_language
</screen>
   These messages are harmless and should be ignored.
  </para>

  <para>
   If the installation process completes without error, you can now start the
   server normally.
  </para>
 </sect2>

 <sect2 id="sepgsql-regression">
  <title>Regression Tests</title>

  <para>
   Due to the nature of <productname>SELinux</productname>, running the
   regression tests for <filename>sepgsql</> requires several extra
   configuration steps, some of which must be done as root.
   The regression tests will not be run by an ordinary
   <literal>make check</> or <literal>make installcheck</> command; you must
   set up the configuration and then invoke the test script manually.
   The tests must be run in the <filename>contrib/sepgsql</> directory
   of a configured PostgreSQL build tree.  Although they require a build tree,
   the tests are designed to be executed against an installed server,
   that is they are comparable to <literal>make installcheck</> not
   <literal>make check</>.
  </para>

  <para>
   First, set up <filename>sepgsql</filename> in a working database
   according to the instructions in <xref linkend="sepgsql-installation">.
   Note that the current operating system user must be able to connect to the
   database as superuser without password authentication.
  </para>

  <para>
   Second, build and install the policy package for the regression test.
   The <filename>sepgsql-regtest</> policy is a special purpose policy package
   which provides a set of rules to be allowed during the regression tests.
   It should be built from the policy source file
   <filename>sepgsql-regtest.te</>, which is done using
   <command>make</command> with a Makefile supplied by SELinux.
   You will need to locate the appropriate
   Makefile on your system; the path shown below is only an example.
   Once built, install this policy package using the
   <command>semodule</> command, which loads supplied policy packages
   into the kernel.  If the package is correctly installed,
   <literal><command>semodule</> -l</> should list sepgsql-regtest as an
   available policy package:
  </para>

<screen>
$ cd .../contrib/sepgsql
$ make -f /usr/share/selinux/devel/Makefile
$ sudo semodule -u sepgsql-regtest.pp
$ sudo semodule -l | grep sepgsql
sepgsql-regtest 1.03
</screen>

  <para>
   Third, turn on <literal>sepgsql_regression_test_mode</>.
   We don't enable all the rules in <filename>sepgsql-regtest</>
   by default, for your system's safety.
   The <literal>sepgsql_regression_test_mode</literal> parameter enables
   the rules needed to launch the regression tests.
   It can be turned on using the <command>setsebool</> command:
  </para>

<screen>
$ sudo setsebool sepgsql_regression_test_mode on
$ getsebool sepgsql_regression_test_mode
sepgsql_regression_test_mode --> on
</screen>

  <para>
   Fourth, verify your shell is operating in the <literal>unconfined_t</>
   domain:
  </para>
<screen>
$ id -Z
unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
</screen>

  <para>
   See <xref linkend="sepgsql-resources"> for details on adjusting your
   working domain, if necessary.
  </para>

  <para>
   Finally, run the regression test script:
  </para>
<screen>
$ ./test_sepgsql
</screen>

  <para>
   This script will attempt to verify that you have done all the configuration
   steps correctly, and then it will run the regression tests for the
   <filename>sepgsql</> module.
  </para>

  <para>
   After completing the tests, it's recommended you disable
   the <literal>sepgsql_regression_test_mode</literal> parameter:
  </para>

<screen>
$ sudo setsebool sepgsql_regression_test_mode off
</screen>

  <para>
   You might prefer to remove the <filename>sepgsql-regtest</> policy
   entirely:
  </para>

<screen>
$ sudo semodule -r sepgsql-regtest
</screen>
 </sect2>

 <sect2 id="sepgsql-parameters">
  <title>GUC Parameters</title>

  <variablelist>
   <varlistentry id="guc-sepgsql-permissive" xreflabel="sepgsql.permissive">
    <term><varname>sepgsql.permissive</> (<type>boolean</type>)</term>
    <indexterm>
     <primary><varname>sepgsql.permissive</> configuration parameter</primary>
    </indexterm>
    <listitem>
     <para>
      This parameter enables <filename>sepgsql</> to function
      in permissive mode, regardless of the system setting.
      The default is off.
      This parameter can only be set in the <filename>postgresql.conf</>
      file or on the server command line.
     </para>

     <para>
      When this parameter is on, <filename>sepgsql</> functions
      in permissive mode, even if SELinux in general is working in enforcing
      mode.  This parameter is primarily useful for testing purposes.
     </para>
    </listitem>

   </varlistentry>
   <varlistentry id="guc-sepgsql-debug-audit" xreflabel="sepgsql.debug_audit">
    <term><varname>sepgsql.debug_audit</> (<type>boolean</>)</>
    <indexterm>
     <primary><varname>sepgsql.debug_audit</> configuration parameter</>
    </indexterm>
    <listitem>
     <para>
      This parameter enables the printing of audit messages regardless of
      the system policy settings.
      The default is off, which means that messages will be printed according
      to the system settings.
     </para>

     <para>
      The security policy of <productname>SELinux</> also has rules to
      control whether or not particular accesses are logged.
      By default, access violations are logged, but allowed
      accesses are not.
     </para>

     <para>
      This parameter forces all possible logging to be turned on, regardless
      of the system policy.
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </sect2>

 <sect2 id="sepgsql-features">
  <title>Features</title>
  <sect3>
   <title>Controlled Object Classes</title>
   <para>
    The security model of <productname>SELinux</> describes all the access
    control rules as relationships between a subject entity (typically,
    a client of the database) and an object entity (such as a database
    object), each of which is
    identified by a security label.  If access to an unlabelled object is
    attempted, the object is treated as if it were assigned the label
    <literal>unlabeled_t</>.
   </para>

   <para>
    Currently, <filename>sepgsql</filename> allows security labels to be
    assigned to schemas, tables, columns, sequences, views, and functions.
    When <filename>sepgsql</filename> is in use, security labels are
    automatically assigned to supported database objects at creation time.
    This label is called a default security label, and is decided according
    to the system security policy, which takes as input the creator's label
    and the label assigned to the new object's parent object.
   </para>

   <para>
    A new database object basically inherits the security label of the parent
    object, except when the security policy has special rules known as
    type-transition rules, in which case a different label may be applied.
    For schemas, the parent object is the current database; for tables,
    sequences, views, and functions, it is the containing schema; for columns,
    it is the containing table.
   </para>
  </sect3>

  <sect3>
   <title>DML Permissions</title>

   <para>
    For tables, <literal>db_table:select</>, <literal>db_table:insert</>,
    <literal>db_table:update</> or <literal>db_table:delete</> is
    checked for all the referenced target tables depending on the kind of
    statement;
    in addition, <literal>db_table:select</> is also checked for
    all the tables that contain the columns referenced in the
    <literal>WHERE</> or <literal>RETURNING</> clause, as a data source
    of <literal>UPDATE</>, and so on.  For example, consider:

<synopsis>
UPDATE t1 SET x = 2, y = md5sum(y) WHERE z = 100;
</synopsis>

    In this case we must have <literal>db_table:select</> in addition to
    <literal>db_table:update</>, because <literal>t1.a</> is referenced
    within the <literal>WHERE</> clause.  Column-level permissions will also be
    checked for each referenced column.
   </para>

   <para>
    For columns, <literal>db_column:select</> is checked on
    not only the columns being read using <literal>SELECT</>, but those being
    referenced in other DML statements.
    Of course, it also checks <literal>db_column:update</> or
    <literal>db_column:insert</> on columns being modified by
    <literal>UPDATE</> or <literal>INSERT</>.
   </para>

   <para>
<synopsis>
UPDATE t1 SET x = 2, y = md5sum(y) WHERE z = 100;
</synopsis>
    In this case, it checks <literal>db_column:update</> on the column
    <literal>t1.x</> being updated, <literal>db_column:{select update}</>
    on the column <literal>t1.y</> being updated and referenced, and
    <literal>db_column:select</> on the column <literal>t1.z</>, since that is
    only referenced in the <literal>WHERE</> clause.
    <literal>db_table:{select update}</> will also be checked
    at the table level.
   </para>

   <para>
    For sequences, <literal>db_sequence:get_value</> is checked when we
    reference a sequence object using <literal>SELECT</>; however, note that we
    do not currently check permissions on execution of corresponding functions
    such as <literal>lastval()</>.
   </para>

   <para>
    For views, <literal>db_view:expand</> will be checked, then any other
    required permissions will be checked on the objects being
    expanded from the view, individually.
   </para>

   <para>
    For functions, <literal>db_procedure:{execute}</> is defined, but is not
    checked in this version.
   </para>

   <para>
    The client must be allowed to access all referenced tables and
    columns, even if they originated from views which were then expanded,
    so that we apply consistent access control rules independent of the manner
    in which the table contents are referenced.
   </para>

   <para>
    The default database privilege system allows database superusers to
    modify system catalogs using DML commands, and reference or modify
    toast tables.  These operations are prohibited when
    <filename>sepgsql</> is enabled.
   </para>
  </sect3>

  <sect3>
   <title>DDL Permissions</title>
   <para>
    When <xref linkend="sql-security-label"> is executed, <literal>setattr</>
    and <literal>relabelfrom</> will be checked on the object being relabeled
    with its old security label, then <literal>relabelto</> with the supplied
    new security label.
   </para>

   <para>
    In the case where multiple label providers are installed and the user tries
    to set a security label, but it is not managed by <productname>SELinux</>,
    only <literal>setattr</> should be checked here.
    This is currently not done due to implementation restrictions.
   </para>
  </sect3>

  <sect3>
   <title>Trusted Procedures</title>
   <para>
    Trusted procedures are similar to security definer functions or set-uid
    commands. <productname>SELinux</> provides a feature to allow trusted
    code to run using a security label different from that of the client,
    generally for the purpose of providing highly controlled access to
    sensitive data (e.g. rows might be omitted, or the precision of stored
    values might be reduced).  Whether or not a function acts as a trusted
    procedure is controlled by its security label and the operating system
    security policy.  For example:
   </para>

<screen>
postgres=# CREATE TABLE customer (
               cid     int primary key,
               cname   text,
               credit  text
           );
CREATE TABLE
postgres=# SECURITY LABEL ON COLUMN customer.credit
               IS 'system_u:object_r:sepgsql_secret_table_t:s0';
SECURITY LABEL
postgres=# CREATE FUNCTION show_credit(int) RETURNS text
             AS 'SELECT regexp_replace(credit, ''-[0-9]+$'', ''-xxxx'', ''g'')
                        FROM customer WHERE cid = $1'
           LANGUAGE sql;
CREATE FUNCTION
postgres=# SECURITY LABEL ON FUNCTION show_credit(int)
               IS 'system_u:object_r:sepgsql_trusted_proc_exec_t:s0';
SECURITY LABEL
</screen>

   <para>
    The above operations should be performed by an administrative user.
   </para>

<screen>
postgres=# SELECT * FROM customer;
ERROR:  SELinux: security policy violation
postgres=# SELECT cid, cname, show_credit(cid) FROM customer;
 cid | cname  |     show_credit
-----+--------+---------------------
   1 | taro   | 1111-2222-3333-xxxx
   2 | hanako | 5555-6666-7777-xxxx
(2 rows)
</screen>

   <para>
    In this case, a regular user cannot reference <literal>customer.credit</>
    directly, but a trusted procedure <literal>show_credit</> allows him
    to print the credit card numbers of customers with some of the digits
    masked out.
   </para>
  </sect3>

  <sect3>
   <title>Miscellaneous</title>
   <para>
    We reject the <xref linkend="sql-load"> command across the board, because
    any module loaded could easily circumvent security policy enforcement.
   </para>

  </sect3>
 </sect2>

 <sect2 id="sepgsql-limitations">
  <title>Limitations</title>

  <variablelist>
   <varlistentry>
    <term>Data Definition Language (DDL) Permissions</term>
    <listitem>
     <para>
      Due to implementation restrictions, DDL permissions are not checked.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term>Data Control Language (DCL) Permissions</term>
    <listitem>
     <para>
      Due to implementation restrictions, DCL permissions are not checked.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term>Row-level access control</term>
    <listitem>
     <para>
      <productname>PostgreSQL</> does not support row-level access; therefore,
      <filename>sepgsql</filename> does not support it either.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term>Covert channels</term>
    <listitem>
     <para>
      <filename>sepgsql</> does not try to hide the existence of
      a certain object, even if the user is not allowed to reference it.
      For example, we can infer the existence of an invisible object as
      a result of primary key conflicts, foreign key violations, and so on,
      even if we cannot obtain the contents of the object.  The existence
      of a top secret table cannot be hidden; we only hope to conceal its
      contents.
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </sect2>

 <sect2 id="sepgsql-resources">
  <title>External Resources</title>
  <variablelist>
   <varlistentry>
    <term><ulink url="http://wiki.postgresql.org/wiki/SEPostgreSQL">SE-PostgreSQL Introduction</ulink></term>
    <listitem>
     <para>
      This wiki page provides a brief overview, security design, architecture,
      administration and upcoming features.
     </para>
    </listitem>
   </varlistentry>
   <varlistentry>
    <term><ulink url="http://docs.fedoraproject.org/selinux-user-guide/">Fedora SELinux User Guide</ulink></term>
    <listitem>
     <para>
      This document provides a wide spectrum of knowledge to administer
      <productname>SELinux</> on your systems.
      It focuses primarily on Fedora, but is not limited to Fedora.
     </para>
    </listitem>
   </varlistentry>
   <varlistentry>
    <term><ulink url="http://docs.fedoraproject.org/selinux-faq">Fedora SELinux FAQ</ulink></term>
    <listitem>
     <para>
      This document answers frequently asked questions about
      <productname>SELinux</productname>.
      It focuses primarily on Fedora, but is not limited to Fedora.
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </sect2>

 <sect2 id="sepgsql-author">
  <title>Author</title>
  <para>
   KaiGai Kohei <email>kaigai@ak.jp.nec.com</email>
  </para>
 </sect2>
</sect1>
