PostgreSQL provides two methods by which database users may be created. Each requires authentication as a superuser, for only
superusers can create new users.
The first method is through the use of the SQL command CREATE USER, which may be
executed by any properly authenticated PostgreSQL client (e.g.,
). The second is a command-line
, which may be more convenient for a system administrator, as it can be executed in a single
command without the need to interact with a PostgreSQL client.
The following sections document each of these methods.
The CREATE USER command requires only one parameter: the name of the new
user. There are also a variety of options that may be set, including a password, explicit system ID, group, and a set of
rights that may be explicitly allocated. Here is the complete syntax for CREATE USER:
[ WITH [ SYSID
[ PASSWORD '
' ] ]
[ CREATEDB | NOCREATEDB ]
[ CREATEUSER | NOCREATEUSER ]
[ IN GROUP
[, ...] ]
[ VALID UNTIL '
In this syntax,
is the name of the new user to be created. You cannot have two users
with the same name. By specifying the WITH keyword, either or both of the
SYSID and PASSWORD keywords may be applied.
Every other optional keyword may follow in the order displayed (not requiring the use of the
WITH keyword). The following is a detailed explanation of each optional keyword and its
Specifies that the system ID is to be set to the value of
. If omitted, a reasonable,
unique numeric default is chosen.
Sets the new user's password to
. If unspecified, the password defaults to
CREATEDB | NOCREATEDB
Specifying the CREATEDB keyword grants the new user the right to create new
databases, as well as the right to destroy databases which they own. Specifying
NOCREATEDB explicitly enforces the default, which is the lack of this right.
CREATEUSER | NOCREATEUSER
Grants the right to create new users, which
implicitly creates a superuser. Notice that a user with the rights to create other users will
, in all databases (including the rights to create a database, even
if NOCREATEDB was specified). NOCREATEUSER
explicitly enforces the default, which is the lack of this right.
Adds the new user to the group named
. Multiple group names may be specified by
separating them with commas. The group(s) must exist in order for the statement to succeed.
VALID UNTIL '
Sets the user's password to expire at
, which must be of a recognizable timestamp format.
After that date, the password must be reset, and the expiration moved forward.
VALID UNTIL '
Sets the user's password to be valid indefinitely.
By not specifying either CREATEDB or CREATEUSER,
users are implicitly "normal" with no special rights. They may not create databases or
other users, nor may they destroy databases or users. Such users may connect to databases in PostgreSQL, but they can only
perform the statements which they have been granted access to (see the Section called Granting Privileges
" for more on granting rights).
Example 10-1 creates a normal user named salesuser. It also
sets a password of
by the use of the WITH PASSWORD
clause. By omitting the VALID UNTIL clause, this password will never expires.
Example 10-1. Creating a normal user
CREATE USER salesuser
WITH PASSWORD 'N0rm4!';
The CREATE USER server message returned in Example 10-1
indicates that the user was added successfully. Other messages you may receive from this command are as follows:
ERROR: CREATE USER: permission denied
This message is returned if the user issuing the CREATE USER command is not a superuser.
Only superusers may create new users.
ERROR: CREATE USER: user name "salesuser" already exists
This message indicates that a user with the name salesuser already exists.
If you wish to create a user who has the ability to create databases within PostgreSQL but not create or destroy
PostgreSQL users, you may specify the CREATEDB keyword rather than
CREATEUSER. This allows the named user to arbitrarily create databases, as
well as drop any databases which they own. See Chapter 9, for more on this the topic of creating
and destroying databases.
Example 10-2 illustrates the creation of a user named dbuser who
has the right to create new databases. This is achieved by specifying the CREATEDB
keyword after the username. Notice also the use of the WITH PASSWORD and
VALID UNTIL keywords. These set the password for dbuser to
, which will be valid until November 11th, 2002.
Example 10-2. Creating a user with CREATEDB rights
CREATE USER dbuser CREATEDB
WITH PASSWORD 'DbuS3r'
VALID UNTIL '2002-11-11';
Resetting an expired user's password does not modify the VALID UNTIL
value. In order to re-active a user's access whose password has expired, both the WITH PASSWORD
and VALID UNTIL keywords must be provided to the ALTER USER
command. See the Section called Altering Users
" for more on this command.
VALID UNTIL settings are only relevant to systems which are not trusted; sites
which are trusted do not require passwords. See Chapter 8 for more on
You may wish to create an alternate superuser from the
user, though caution should be
exercised in creating superusers. These users are granted
within PostgreSQL, including
creating users, removing users, and destroying databases. Example 10-3 demonstrates the creation
of a PostgreSQL superuser named manager from the
Example 10-3. Creating a
CREATE USER manager CREATEUSER;
script is executed directly from the command line, and can operate in one of
two ways. If issued without any arguments, it will interactively prompt you for the username and each of the rights, and
attempt to make a local connection to PostgreSQL. Alternatively, you may choose to specify the options and
the username to be created on the command line.
As with other command-line applications for PostgreSQL, arguments may be supplied either in their short form (with
a single dash, and character), or in their long form (with two dashes, and the full name of the argument).
Here is the syntax for
in the syntax represents the name of the user you wish to create. Replace
with one or more of the following flags:
- -d, - -createdb
Equivalent to the CREATEDB keyword of the CREATE USER
SQL command. Allows the new user to create databases.
- -D, - -no-createdb
Equivalent to the NOCREATEDB keyword of the CREATE USER
SQL command. Explicitly indicates that the new user may not create databases. This is the default.
- -a, - -adduser
Equivalent to the CREATEUSER keyword of the CREATE USER
SQL command. Allows the new user to create users, and raises the status of the user to a superuser (enabling
rights within PostgreSQL).
- -A, - -no-adduser
Equivalent to the NOCREATEUSER keyword of the CREATE USER
SQL command. Explicitly indicates that the new user is not a superuser. This is the default.
, - -sysid=
Sets the new users system ID to
- -P, - -pwprompt
Results in a password prompt allowing you to set the password of the new user
, - -host=
will be connected to, rather than the localhost, or the host defined
by the PGHOST environment variable.
, - -port=
Specifies that the database connection will be made on port
, rather than the
default port (usually 5432).
, - -username=
will be the user who connects to PostgreSQL
(The default is to connect using the name of the system user executing the
- -W, - -password
Results in a password prompt for the connecting user, which happens automatically if the
file is configured not to
the requesting host.
- -e, - -echo
Causes the CREATE USER command sent to PostgreSQL to be displayed to the screen as it is
- -q, - -quiet
Prevents output from being sent to
(though errors will
still be sent to
If any of the
arguments are omitted,
will prompt you for each missing
argument. This is because PostgreSQL will not make any assumptions about the rights intended for the new user, nor about the new user's name. Example 10-4 creates a user named newuser, who has neither the right to create a
database, nor create users.
Example 10-4. Creating a user with createuser
[[email protected] ~]$
createuser -U manager -D -A newuser
Notice also the
flag passed to the
indicates that the user with which to connect to PostgreSQL is manager, not
jworsley as the script would otherwise assume, based on the name of the system account
invoking the script.
If you prefer to be interactively prompted for each setting, (instead of having to remember the meaning of each
flag or check the reference each time) you may simply omit the flags you are uncertain of. The
script will then prompt you for the basic
options include the PostgreSQL username, whether the user may create databases, and whether or not the user may add new
users to PostgreSQL.
Example 10-5 demonstrates using the
interactive mode. The net effect of this example is the same as the single line executed in Example 10-4.
Example 10-5. Interactively creating a user with createuser
[[email protected] ~]$
Enter name of user to add: newuser
Shall the new user be allowed to create databases? (y/n) n
Shall the new user be allowed to create more new users? (y/n) n