This page in other versions: Unsupported versions: 6.4 / 6.5 / 7.0

CREATE USER

Name

CREATE USER — Creates account information for a new user
CREATE USER username
    [ WITH PASSWORD password ]
    [ CREATEDB   | NOCREATEDB ]
    [ CREATEUSER | NOCREATEUSER ]
    [ IN GROUP     groupname [, ...] ]
    [ VALID UNTIL  'abstime' ]
  

Inputs

username

The name of the user.

password

The WITH PASSWORD clause sets the user's password within the "pg_shadow" table. For this reason, "pg_shadow" is no longer accessible to the instance of Postgres that the Postgres user's password is initially set to NULL. When a user's password in the "pg_shadow" 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 Postgres instance, and the password stored in the "pg_shadow" 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 with equates to a NULL value in the authentication system mentioned above.

CREATEDB/NOCREATEDB

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.

CREATEUSER/NOCREATEUSER

These clauses determine whether a user will be permitted to create new users in an instance of Postgres. Omitting this clause will set the user's value of this attribute to be NOCREATEUSER.

groupname

A name of a group into which to insert the user as a new member.

abstime

The VALID UNTIL clause sets an absolute time after which the user's Postgres login is no longer valid. Please note that if a user does not have a password defined in the "pg_shadow" table, the valid until date will not be checked during user authentication. If this clause is omitted, a NULL value is stored in "pg_shadow" for this attribute, and the login will be valid for all time.

Outputs

CREATE USER

Message returned if the command completes successfully.

ERROR: removeUser: user "username" does not exist

if "username" not found.

Description

CREATE USER will add a new user to an instance of Postgres.

The new user will be given a usesysid of: 'SELECT MAX(usesysid) + 1 FROM pg_shadow'. This means that Postgres users' usesysids will not correspond to their operating system(OS) user ids. The exception to this rule is the 'postgres' user, whose OS user id is used as the usesysid during the initdb process. If you still want the OS user id and the usesysid to match for any given user, use the "createuser" script provided with the Postgres distribution.

Notes

CREATE USER statement is a Postgres language extension.

Use DROP USER or ALTER USER statements to remove or modify a user account.

Refer to the pg_shadow table for further information.

   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 |
   +--------------------------+--------------------------+-------+
   

Usage

Create a user with no password:

   CREATE USER jonathan
  

Create a user with a password:

   CREATE USER davide WITH PASSWORD jw8s0F4
  

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:

   CREATE USER miriam WITH PASSWORD jw8s0F4 VALID UNTIL 'Jan 1 2002'
  

Create an account where the user can create databases:

   CREATE USER manuel WITH PASSWORD jw8s0F4 CREATEDB
  

Compatibility

SQL92

There is no CREATE USER statement in SQL92.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group