path: root/doc/src/sgml/ref/create_user.sgml

<!--
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_user.sgml,v 1.8 1999/07/22 15:09:08 thomas Exp $
Postgres documentation
-->

<refentry id="SQL-CREATEUSER">
 <refmeta>
  <refentrytitle>
   CREATE USER
  </refentrytitle>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>
 <refnamediv>
  <refname>
   CREATE USER
  </refname>
  <refpurpose>
   Creates account information for a new user
  </refpurpose>
 </refnamediv>
 <refsynopsisdiv>
  <refsynopsisdivinfo>
   <date>1999-07-20</date>
  </refsynopsisdivinfo>
  <synopsis>
CREATE USER<replaceable class="PARAMETER"> username</replaceable>
    [ WITH PASSWORD <replaceable class="PARAMETER">password</replaceable> ]
    [ CREATEDB   | NOCREATEDB ] [ CREATEUSER | NOCREATEUSER ]
    [ IN GROUP     <replaceable class="PARAMETER">groupname</replaceable> [, ...] ]
    [ VALID UNTIL  '<replaceable class="PARAMETER">abstime</replaceable>' ]
  </synopsis>
  
  <refsect2 id="R2-SQL-CREATEUSER-1">
   <refsect2info>
    <date>1998-09-21</date>
   </refsect2info>
   <title>
    Inputs
   </title>
   <para>

    <variablelist>
     <varlistentry>
      <term><replaceable class="parameter">username</replaceable></term>
      <listitem>
       <para>
	The name of the user.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">password</replaceable></term>
      <listitem>
       <para>
	The WITH PASSWORD clause sets the user's password within
	the "<filename>pg_shadow</filename>" table. For this reason,
	<filename>"pg_shadow</filename>" is no
	longer accessible to the instance of
	<productname>Postgres</productname> that the
	<productname>Postgres</productname>
	user's password is initially set to NULL.
       </para>
       <para>
	When a
	user's password in the "<filename>pg_shadow</filename>"
	table is NULL, user
	authentication proceeds as it historically has (HBA,
	PG_PASSWORD, etc). However, if a password is set for a
	user, a new authentication system supplants any other
	configured for the <productname>Postgres</productname>
	instance, and the password
	stored in the "<filename>pg_shadow</filename>" table is used
	for authentication.
	For more details on how this authentication system
	functions see pg_crypt(3). If the WITH PASSWORD clause is
	omitted, the user's password is set to the empty
	string which equates to a NULL value in the authentication
	system mentioned above.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>CREATEDB</term>
      <term>NOCREATEDB</term>
      <listitem>
       <para> 
	These clauses define a user's ability to create databases.
	If CREATEDB is specified, the user being defined will
	be allowed to create his own databases. Using NOCREATEDB
	will deny a user the ability to create databases. If this
	clause is omitted,  NOCREATEDB is used by default.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>CREATEUSER</term>
      <term>NOCREATEUSER</term>
      <listitem>
       <para>
	These clauses determine whether a user will be permitted to
	create new
	users in an instance of <productname>Postgres</productname>.
	Omitting this clause will set the user's value of this
	attribute to be NOCREATEUSER.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">groupname</replaceable></term>
      <listitem>
       <para>
	A name of a group into which to insert the user as a new member.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">abstime</replaceable></term>
      <listitem>
       <para>
	The VALID UNTIL clause sets an absolute time after which the
	user's <productname>Postgres</productname>
	login is no longer valid. Please note that
	if a user does not have a password defined in the
	"<filename>pg_shadow</filename>"
	table, the valid until date will not be checked
	during user authentication. If this clause is omitted,
	a NULL value is stored in "<filename>pg_shadow</filename>" 
	for this attribute,
	and the login will be valid for all time.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
  </refsect2>
  
  <refsect2 id="R2-SQL-CREATEUSER-2">
   <refsect2info>
    <date>1998-09-21</date>
   </refsect2info>
   <title>
    Outputs
   </title>

   <para>
    <variablelist>
     <varlistentry>
      <term><computeroutput>
CREATE USER
       </computeroutput></term>
      <listitem>
       <para>
	Message returned if the command completes successfully.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
  </refsect2>
 </refsynopsisdiv>

 <refsect1 id="R1-SQL-CREATEUSER-1">
  <refsect1info>
   <date>1998-09-21</date>
  </refsect1info>
  <title>
   Description
  </title>
  <para>
   CREATE USER will add a new user to an instance of 
   <productname>Postgres</productname>.
  </para>

  <para>
   The new user will be given a <filename>usesysid</filename> of:

   <programlisting>
SELECT MAX(usesysid) + 1 FROM pg_shadow;
   </programlisting>

   This means that 
   <productname>Postgres</productname> users' <filename>usesysid</filename>s will not
   correspond to their operating
   system(OS) user ids. The exception to this rule is
   the <literal>postgres</literal> superuser, whose OS user id
   is used as the
   <filename>usesysid</filename> during the initdb process. 
   If you still want the
   OS user id and the <filename>usesysid</filename> to match
   for any given user,
   use the <application>createuser</application> script provided with
   the <productname>Postgres</productname> distribution.
  </para>
  
  <refsect2 id="R2-SQL-CREATEUSER-3">
   <refsect2info>
    <date>1998-09-21</date>
   </refsect2info>
   <title>
    Notes
   </title>
   <para>
    <command>CREATE USER</command> statement is a
    <productname>Postgres</productname> language extension.
   </para>
   <para>
    Use <command>DROP USER</command> or <command>ALTER USER</command>
    statements to remove or modify a user account.
   </para>
   <para>
    Refer to the <filename>pg_shadow</filename> table for further information.
   </para>
   <programlisting>
   Table    = pg_shadow
   +--------------------------+--------------------------+-------+
   |          Field           |          Type            | Length|
   +--------------------------+--------------------------+-------+
   | usename                  | name                     |    32 |
   | usesysid                 | int4                     |     4 |
   | usecreatedb              | bool                     |     1 |
   | usetrace                 | bool                     |     1 |
   | usesuper                 | bool                     |     1 |
   | usecatupd                | bool                     |     1 |
   | passwd                   | text                     |   var |
   | valuntil                 | abstime                  |     4 |
   +--------------------------+--------------------------+-------+
   </programlisting>
  </refsect2>
 </refsect1>
  
 <refsect1 id="R1-SQL-CREATEUSER-2">
  <title>
   Usage
  </title>
  <para>
   Create a user with no password:

   <programlisting>
CREATE USER jonathan
   </programlisting>
  </para>

  <para>
   Create a user with a password:

   <programlisting>
CREATE USER davide WITH PASSWORD jw8s0F4
   </programlisting>
  </para>

  <para>
   Create a user with a password, whose account is valid until the end of 2001.
   Note that after one second has ticked in 2002, the account is not
   valid:

   <programlisting>
CREATE USER miriam WITH PASSWORD jw8s0F4 VALID UNTIL 'Jan 1 2002'
   </programlisting>
  </para>

  <para> 
   Create an account where the user can create databases:

   <programlisting>
CREATE USER manuel WITH PASSWORD jw8s0F4 CREATEDB
   </programlisting>
  </para>
 </refsect1>
 
 <refsect1 id="R1-SQL-CREATEUSER-3">
  <title>
   Compatibility
  </title>
  
  <refsect2 id="R2-SQL-CREATEUSER-4">
   <refsect2info>
    <date>1998-09-21</date>
   </refsect2info>
   <title>
    SQL92
   </title>

   <para>
    There is no <command>CREATE USER</command> statement in SQL92.
   </para>
  </refsect2>
 </refsect1>
</refentry>

<!-- 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:
-->