<!--
$Header: /cvsroot/pgsql/doc/src/sgml/Attic/security.sgml,v 1.9 2000/05/25 15:32:03 momjian Exp $
-->

 <chapter id="security">
  <Title>Security</Title>

  <Para>
   Database security is addressed at several levels:

   <itemizedlist>
    <listitem>
     <para>
      Data base file protection. All files stored within the database
      are protected from reading by any account other than the
      <productname>Postgres</productname> superuser account.
     </para>
    </listitem>
    <listitem>
     <para>
      Connections from a client to the database server are, by
      default, allowed only via a local Unix socket, not via TCP/IP
      sockets. The backend must be started with the
      <literal>-i</literal> option to allow non-local clients to connect.
     </para>
    </listitem>
    <listitem>
     <para>
      Client connections can be restricted by IP address and/or user
      name via the <filename>pg_hba.conf</filename> file in <envar>PG_DATA</envar>.
     </para>
    </listitem>
    <listitem>
     <para>
      Client connections may be authenticated via other external packages.
     </para>
    </listitem>
    <listitem>
     <para>
      Each user in <productname>Postgres</productname> is assigned a
      username and (optionally) a password. By default, users do not
      have write access to databases they did not create.
     </para>
    </listitem>
    <listitem>
     <para>
      Users may be assigned to <firstterm>groups</firstterm>, and
      table access may be restricted based on group privileges.
     </para>
    </listitem>
   </itemizedlist>
  </para>

  <Sect1>
   <Title>User Authentication</Title>

   <Para>
    <firstterm>Authentication</firstterm>
    is the process by which the backend server and 
    <application>postmaster</application>
    ensure that the user requesting access to data is in fact who he/she
    claims to be.  
    All users who invoke <Productname>Postgres</Productname> are checked against the
    contents of the <literal>pg_user</literal> class to ensure that they are
    authorized to do so.  However, verification of the user's actual
    identity is performed in a variety of ways:

    <variablelist>
     <varlistentry>
      <term>
       From the user shell
      </term>
      <listitem>
       <para>
	A backend server started from a user shell notes the user's (effective)
	user-id before performing a 
	<function>setuid</function>
	to the user-id of user <replaceable>postgres</replaceable>.  
	The effective user-id is used
	as the basis for access control checks.  No other authentication is
	conducted.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>
       From the network
      </term>
      <listitem>
       <para>
	If the <Productname>Postgres</Productname> system is built as distributed,
	access to the Internet TCP port of the
	<application>postmaster</application>
	process is available to anyone.  The DBA configures the pg_hba.conf file
	in the PGDATA directory to specify what authentication system is to be used
	according to the host making the connection and which database it is
	connecting to.  See <citetitle>pg_hba.conf(5)</citetitle>
	for a description of the authentication
	systems available.  Of course, host-based authentication is not fool-proof in
	Unix, either. It is possible for determined intruders to also
	masquerade the origination host. Those security issues are beyond the
	scope of <Productname>Postgres</Productname>.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>

   <Sect2>
    <Title>Host-Based Access Control</Title>

    <Para>
     <firstterm>Host-based access control</firstterm>
     is the name for the basic controls PostgreSQL
     exercises on what clients are allowed to access a database and how
     the users on those clients must authenticate themselves.
    </para>

    <para>
     Each database system contains a file named
     <filename>pg_hba.conf</filename>, in its <envar>PGDATA</envar>
     directory, which controls who can connect to each database.
    </para>

    <para>
     Every client accessing a database
     <emphasis>must</emphasis>
     be covered by one of
     the entries in <filename>pg_hba.conf</filename>.
     Otherwise all attempted connections from that
     client will be rejected with a "User authentication failed" error
     message.
    </para>

    <para>
     The general format of the <filename>pg_hba.conf</filename>
     file is of a set of records, one per
     line.  Blank lines and lines beginning with a hash character
     ("#") are ignored.  A record is
     made up of a number of fields which are separated by spaces and/or tabs.
    </para>

    <para>
     Connections from clients can be made using Unix domain sockets or Internet
     domain sockets (ie. TCP/IP).  Connections made using Unix domain sockets
     are controlled using records of the following format:

     <synopsis>
local <replaceable>database</replaceable> <replaceable>authentication method</replaceable>
     </synopsis>

     where

     <simplelist>
      <member>
       <replaceable>database</replaceable>
       specifies the database that this record applies to.  The value
	<literal>all</literal>
	specifies that it applies to all databases.
      </member>
      <member>
       <replaceable>authentication method</replaceable>
       specifies the method a user must use to authenticate themselves when
       connecting to that database using Unix domain sockets.  The different methods
       are described below.
      </member>
     </simplelist>
    </para>

    <para>
     Connections made using Internet domain sockets are controlled using records
     of the following format.

     <synopsis>
host <replaceable>database</replaceable> <replaceable>TCP/IP address</replaceable> <replaceable>TCP/IP mask</replaceable> <replaceable>authentication method</replaceable>
     </synopsis>
    </para>

    <para>
     The <replaceable>TCP/IP address</replaceable>
     is logically anded to both the specified
     <replaceable>TCP/IP mask</replaceable>
     and the TCP/IP address
     of the connecting client.
     If the two resulting values are equal then the
     record is used for this connection.  If a connection matches more than one
     record then the earliest one in the file is used.
     Both the
     <replaceable>TCP/IP address</replaceable>
     and the
     <replaceable>TCP/IP mask</replaceable>
     are specified in dotted decimal notation.
    </para>

    <para>
     If a connection fails to match any record then the
     <firstterm>reject</firstterm>
     authentication method is applied (see below).
    </para>

    <sect3>
     <title>Authentication Methods</title>

     <para>
      The following authentication methods are supported for both Unix and TCP/IP
      domain sockets:

      <variablelist>
       <varlistentry>
	<term>trust</term>
	<listitem>
	 <para>
	  The connection is allowed unconditionally.
	 </para>
	</listitem>
       </varlistentry>

       <varlistentry>
	<term>reject</term>
	<listitem>
	 <para>
	  The connection is rejected unconditionally.
	 </para>
	</listitem>
       </varlistentry>

       <varlistentry>
	<term>crypt</term>
	<listitem>
	 <para>
	  The client is asked for a password for the user.  This is sent encrypted
	  (using <citetitle>crypt(3)</citetitle>)
	  and compared against the password held in the 
	  <filename>pg_shadow</filename> table.
	  If the passwords match, the connection is allowed.
	 </para>
	</listitem>
       </varlistentry>

       <varlistentry>
	<term>password</term>
	<listitem>
	 <para>
	  The client is asked for a password for the user.  This is sent in clear
	  and compared against the password held in the
	  <filename>pg_shadow</filename> table.
	  If the passwords match, the connection is allowed.  An optional password file
	  may be specified after the
	  <literal>password</literal>
	  keyword which is used to match the supplied password rather than the pg_shadow
	  table.  See
	  <citerefentry><refentrytitle>pg_passwd</refentrytitle></citerefentry>.
	 </para>
	</listitem>
       </varlistentry>
      </variablelist>
     </para>

     <para>
      The following authentication methods are supported for TCP/IP
      domain sockets only:

      <variablelist>
       <varlistentry>
	<term>krb4</term>
	<listitem>
	 <para>
	  Kerberos V4 is used to authenticate the user.
	 </para>
	</listitem>
       </varlistentry>

       <varlistentry>
	<term>krb5</term>
	<listitem>
	 <para>
	  Kerberos V5 is used to authenticate the user.
	 </para>
	</listitem>
       </varlistentry>

       <varlistentry>
	<term>ident</term>
	<listitem>
	 <para>
	  The ident server on the client is used to authenticate the user (RFC 1413).
	  An optional map name may be specified after the
	  <literal>ident</literal>
	  keyword which allows ident user names to be mapped onto
	  <productname>Postgres</productname> user names.
	  Maps are held in the file
	  <filename>$<envar>PGDATA</envar>/pg_ident.conf</filename>.
	 </para>
	</listitem>
       </varlistentry>
      </variablelist>
     </para>
    </sect3>

    <sect3>
     <title>Examples</title>

     <para>
      <programlisting>
# Trust any connection via Unix domain sockets.
local	trust
# Trust any connection via TCP/IP from this machine.
host	all	127.0.0.1	255.255.255.255		trust
# We don't like this machine.
host	all	192.168.0.10	255.255.255.0		reject
# This machine can't encrypt so we ask for passwords in clear.
host	all	192.168.0.3	255.255.255.0		password
# The rest of this group of machines should provide encrypted passwords.
host	all	192.168.0.0	255.255.255.0		crypt
      </programlisting>
     </para>
    </sect3>
   </sect2>
  </sect1>

  <sect1>
   <title>User Names and Groups</title>

   <para>
    To define a new user, run the
    <application>createuser</application> utility program.
   </para>

   <para>
    To assign a user or set of users to a new group, one must 
    define the group itself, and assign users to that group. In
    <application>Postgres</application> these steps are not currently
    supported with a <command>create group</command> command. Instead, 
    the groups are defined by inserting appropriate values into the
    <literal>pg_group</literal> system table, and then using the
    <command>grant</command> command to assign privileges to the
    group.
   </para>

   <sect2>
    <title>Creating Users</title>

    <para>
    </para>
   </sect2>

   <sect2>
    <title>Creating Groups</title>

    <para>
     Currently, there is no easy interface to set up user groups. You 
     have to explicitly insert/update the <literal>pg_group table</literal>.
     For example:

     <programlisting>
jolly=> insert into pg_group (groname, grosysid, grolist)
jolly=>     values ('posthackers', '1234', '{5443, 8261}');
INSERT 548224
jolly=> grant insert on foo to group posthackers;
CHANGE
jolly=>
     </programlisting>
    </para>

    <para>
     The fields in <filename>pg_group</filename> are:

     <variablelist>
      <varlistentry>
       <term>groname</term>
       <listitem>
	<para>
	 The group name. This a name and should be purely
	 alphanumeric. Do not include underscores or other punctuation.
	</para>
       </listitem>
      </varlistentry>

      <varlistentry>
       <term>grosysid</term>
       <listitem>
	<para>
	 The group id. This is an int4. This should be unique for
	 each group.
	</para>
       </listitem>
      </varlistentry>

      <varlistentry>
       <term>grolist</term>
       <listitem>
	<para>
	 The list of pg_user id's that belong in the group. This
	 is an int4[].
	</para>
       </listitem>
      </varlistentry>
     </variablelist>
    </para>
   </sect2>

   <sect2>
    <title>Assigning Users to Groups</title>

    <para>
    </para>
   </sect2>

  </sect1>

  <Sect1>
   <Title>Access Control</Title>

   <Para>
    <Productname>Postgres</Productname> provides mechanisms to allow users 
    to limit the access to their data that is provided to other users.

    <variablelist>
     <varlistentry>
      <term>
       Database superusers
      </term>
      <listitem>
       <para>
	Database super-users (i.e., users who have <literal>pg_user.usesuper</literal>
	set) silently bypass all of the access controls described below with
	two exceptions: manual system catalog updates are not permitted if the
	user does not have <literal>pg_user.usecatupd</literal> set, and destruction of
	system catalogs (or modification of their schemas) is never allowed.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>
       Access Privilege
      </term>
      <listitem>
       <para>
	The use of access privilege to limit reading, writing and setting
	of rules on classes is covered in
	<citetitle>grant/revoke(l)</citetitle>.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>
       Class removal and schema modification
      </term>
      <listitem>
       <para>
	Commands that destroy or modify the structure of an existing class,
	such as <command>alter</command>,
	<command>drop table</command>,
	and
	<command>drop index</command>,
	only operate for the owner of the class.  As mentioned above, these
	operations are <emphasis>never</emphasis>
	permitted on system catalogs.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
  </sect1>

  <Sect1>
   <Title>Functions and Rules</Title>

   <Para>
    Functions and rules allow users to insert code into the backend server
    that other users may execute without knowing it.  Hence, both
    mechanisms permit users to <firstterm>trojan horse</firstterm>
    others with relative impunity.  The only real protection is tight
    control over who can define functions (e.g., write to relations with
    SQL fields) and rules.  Audit trails and alerters on
    <literal>pg_class</literal>, <literal>pg_user</literal>
    and <literal>pg_group</literal> are also recommended.
   </para>

   <Sect2>
    <Title>Functions</Title>

    <Para>
     Functions written in any language except SQL 
     run inside the backend server
     process with the permissions of the user <replaceable>postgres</replaceable> (the
     backend server runs with its real and effective user-id set to
     <replaceable>postgres</replaceable>.  It is possible for users to change the server's
     internal data structures from inside of trusted functions.  Hence,
     among many other things, such functions can circumvent any system
     access controls.  This is an inherent problem with user-defined C functions.
    </para>
   </sect2>

   <Sect2>
    <Title>Rules</Title>

    <Para>
     Like SQL functions, rules always run with the identity and
     permissions of the user who invoked the backend server.
    </para>
   </sect2>

   <sect2>
    <title>Caveats</title>

    <para>
     There are no plans to explicitly support encrypted data inside of
     <Productname>Postgres</Productname> 
     (though there is nothing to prevent users from encrypting
     data within user-defined functions).  There are no plans to explicitly
     support encrypted network connections, either, pending a total rewrite
     of the frontend/backend protocol.
    </para>
    <para>
     User names, group names and associated system identifiers (e.g., the
     contents of <literal>pg_user.usesysid</literal>) are assumed to be unique
     throughout a database.  Unpredictable results may occur if they are
     not.
    </para>
   </sect2>
  </sect1>

  <sect1>
   <title>Secure TCP/IP Connection</title>

   <para>
    <note>
     <title>Author</title>
     <para>
      From e-mail by
      <ulink url="selkovjr@mcs.anl.gov">Gene Selkov, Jr.</ulink>
      written on 1999-09-08 in response to a
      question from Eric Marsden.
     </para>
    </note>
   </para>

   <para>
    One can use <productname>ssh</productname> to encrypt the network
    connection between clients and a
    <productname>Postgres</productname> server. Done properly, this
    should lead to an adequately secure network connection.
   </para>

   <para>
    The documentation for <productname>ssh</productname> provides most 
    of the information to get started.
    Please refer to
    <ulink url="http://www.heimhardt.de/htdocs/ssh.html">http://www.heimhardt.de/htdocs/ssh.html</ulink>
    for better insight.
   </para>

   <para>
    A step-by-step explanation can be done in just two steps.
   </para>

   <procedure>
    <title>Running a secure tunnel via ssh</title>

    <para>
     A step-by-step explanation can be done in just two steps.
    </para>

    <step performance="required" id="establish-tunnel">
     <para>
      Establish a tunnel to the backend machine, like this:

      <programlisting>
ssh -L 3333:wit.mcs.anl.gov:5432 postgres@wit.mcs.anl.gov
      </programlisting>

      The first number in the -L argument, 3333, is the port number of
      your end of the tunnel. The second number, 5432, is the remote
      end of the tunnel -- the port number your backend is using. The
      name or the address in between the port numbers belongs to the
      server machine, as does the last argument to ssh that also includes
      the optional user name. Without the user name, ssh will try the
      name you are currently logged on as on the client machine. You can
      use any user name the server machine will accept, not necessarily
      those related to postgres.
     </para>
    </step>

    <step performance="required">
     <para>
      Now that you have a running ssh session, you can connect a
      postgres client to your local host at the port number you
      specified in the previous step. If it's
      <application>psql</application>, you will need another shell
      because the shell session you used in
      <xref linkend="establish-tunnel"> is now occupied with
      <application>ssh</application>.

      <programlisting>
psql -h localhost -p 3333 -d mpw
      </programlisting>

      Note that you have to specify the <option>-h</option> argument
      to cause your client to use the TCP socket instead of the Unix
      socket. You can omit the port argument if you chose 5432 as your
      end of the tunnel.
     </para>
    </step>
   </procedure>
  </sect1>
</chapter>

<!-- Keep this comment at the end of the file
Local variables:
mode:sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"./reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:("/usr/lib/sgml/catalog")
sgml-local-ecat-files:nil
End:
-->
