create_user.sgml 8.44 KB
Newer Older
1
<!--
2
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_user.sgml,v 1.9 1999/10/07 16:40:36 momjian Exp $
3 4 5
Postgres documentation
-->

6 7 8
<refentry id="SQL-CREATEUSER">
 <refmeta>
  <refentrytitle>
9
   CREATE USER
10 11 12 13 14
  </refentrytitle>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>
 <refnamediv>
  <refname>
15
   CREATE USER
16 17
  </refname>
  <refpurpose>
18
   Creates account information for a new user
19
  </refpurpose>
20
 </refnamediv>
21 22
 <refsynopsisdiv>
  <refsynopsisdivinfo>
23
   <date>1999-07-20</date>
24 25 26 27
  </refsynopsisdivinfo>
  <synopsis>
CREATE USER<replaceable class="PARAMETER"> username</replaceable>
    [ WITH PASSWORD <replaceable class="PARAMETER">password</replaceable> ]
28
    [ CREATEDB   | NOCREATEDB ] [ CREATEUSER | NOCREATEUSER ]
29 30 31
    [ IN GROUP     <replaceable class="PARAMETER">groupname</replaceable> [, ...] ]
    [ VALID UNTIL  '<replaceable class="PARAMETER">abstime</replaceable>' ]
  </synopsis>
32
  
33 34 35 36 37
  <refsect2 id="R2-SQL-CREATEUSER-1">
   <refsect2info>
    <date>1998-09-21</date>
   </refsect2info>
   <title>
38
    Inputs
39 40
   </title>
   <para>
41

42 43
    <variablelist>
     <varlistentry>
44
      <term><replaceable class="parameter">username</replaceable></term>
45 46 47 48 49 50 51 52
      <listitem>
       <para>
	The name of the user.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
53
      <term><replaceable class="parameter">password</replaceable></term>
54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84
      <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>
85 86
      <term>CREATEDB</term>
      <term>NOCREATEDB</term>
87 88 89 90 91 92 93 94 95 96 97 98
      <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>
99 100
      <term>CREATEUSER</term>
      <term>NOCREATEUSER</term>
101 102 103 104 105 106 107 108 109 110 111 112
      <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>
113
      <term><replaceable class="parameter">groupname</replaceable></term>
114 115 116 117 118 119 120 121
      <listitem>
       <para>
	A name of a group into which to insert the user as a new member.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
122
      <term><replaceable class="parameter">abstime</replaceable></term>
123 124 125 126 127 128 129 130 131 132 133 134 135 136 137
      <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>
138 139
    </variablelist>
   </para>
140
  </refsect2>
141
  
142 143 144 145 146
  <refsect2 id="R2-SQL-CREATEUSER-2">
   <refsect2info>
    <date>1998-09-21</date>
   </refsect2info>
   <title>
147
    Outputs
148 149 150 151 152
   </title>

   <para>
    <variablelist>
     <varlistentry>
153 154 155
      <term><computeroutput>
CREATE USER
       </computeroutput></term>
156 157 158 159 160 161 162 163 164 165
      <listitem>
       <para>
	Message returned if the command completes successfully.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
  </refsect2>
 </refsynopsisdiv>
166

167 168 169 170 171
 <refsect1 id="R1-SQL-CREATEUSER-1">
  <refsect1info>
   <date>1998-09-21</date>
  </refsect1info>
  <title>
172
   Description
173 174
  </title>
  <para>
175
   CREATE USER will add a new user to an instance of 
176
   <productname>Postgres</productname>.
177
  </para>
178

179
  <para>
180
   The new user will be given a <filename>usesysid</filename> of:
181 182 183 184 185

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

186
   This means that 
187
   <productname>Postgres</productname> users' <filename>usesysid</filename>s will not
188 189
   correspond to their operating
   system(OS) user ids. The exception to this rule is
190
   the <literal>postgres</literal> superuser, whose OS user id
191 192 193 194 195
   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,
196 197
   use the <application>createuser</application> script provided with
   the <productname>Postgres</productname> distribution.
198
  </para>
199
  
200 201 202 203 204
  <refsect2 id="R2-SQL-CREATEUSER-3">
   <refsect2info>
    <date>1998-09-21</date>
   </refsect2info>
   <title>
205
    Notes
206 207
   </title>
   <para>
208 209
    <command>CREATE USER</command> statement is a
    <productname>Postgres</productname> language extension.
210
   </para>
211
   <para>
212 213 214
    Use <command>DROP USER</command> or <command>ALTER USER</command>
    statements to remove or modify a user account.
   </para>
215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232
   <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>
233
  </refsect2>
234
 </refsect1>
235
  
236 237
 <refsect1 id="R1-SQL-CREATEUSER-2">
  <title>
238
   Usage
239 240
  </title>
  <para>
241
   Create a user with no password:
242 243 244 245

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

248
  <para>
249
   Create a user with a password:
250 251

   <programlisting>
252
CREATE USER davide WITH PASSWORD "jw8s0F4"
253
   </programlisting>
254
  </para>
255

256 257 258 259
  <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:
260 261

   <programlisting>
262
CREATE USER miriam WITH PASSWORD "jw8s0F4" VALID UNTIL 'Jan 1 2002'
263
   </programlisting>
264
  </para>
265

266 267
  <para> 
   Create an account where the user can create databases:
268 269

   <programlisting>
270
CREATE USER manuel WITH PASSWORD "jw8s0F4" CREATEDB
271
   </programlisting>
272
  </para>
273
 </refsect1>
274
 
275 276
 <refsect1 id="R1-SQL-CREATEUSER-3">
  <title>
277
   Compatibility
278
  </title>
279
  
280 281 282 283 284
  <refsect2 id="R2-SQL-CREATEUSER-4">
   <refsect2info>
    <date>1998-09-21</date>
   </refsect2info>
   <title>
285
    SQL92
286
   </title>
287

288
   <para>
289
    There is no <command>CREATE USER</command> statement in SQL92.
290
   </para>
291 292
  </refsect2>
 </refsect1>
293
</refentry>
294 295 296 297

<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
298
sgml-omittag:nil
299 300 301 302 303 304 305 306 307 308 309
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:
310
-->