Creating a user with the CREATE USER SQL command

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:

  CREATE USER username
    [ WITH [ SYSID uid ]
           [ PASSWORD 'password' ] ]
    [ CREATEDB   | NOCREATEDB ]
    [ CREATEUSER | NOCREATEUSER ]
    [ IN GROUP groupname [, ...] ]
    [ VALID UNTIL 'abstime' ]

In this syntax, username 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 meaning:

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 N0rm4! by the use of the WITH PASSWORD clause. By omitting the VALID UNTIL clause, this password will never expires.

template1=# CREATE USER salesuser
template1-#             WITH PASSWORD 'N0rm4!';
CREATE USER

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:

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 DbuS3r, which will be valid until November 11th, 2002.

template1=# CREATE USER dbuser CREATEDB
template1-#             WITH PASSWORD 'DbuS3r'
template1-#             VALID UNTIL '2002-11-11';
CREATE USER

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.

You may wish to create an alternate superuser from the postgres user, though caution should be exercised in creating superusers. These users are granted every right within PostgreSQL, including creating users, removing users, and destroying databases. Example 10-3 demonstrates the creation of a PostgreSQL superuser named manager from the psql prompt.

template1=# CREATE USER manager CREATEUSER;
CREATE USER

Creating a user with the createuser script

The 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 createuser :

  createuser [ options ] [ username ]

The username in the syntax represents the name of the user you wish to create. Replace options with one or more of the following flags:

If any of the -d, -D, -a, -A, or username arguments are omitted, createuser 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.

[jworsley@booktown ~]$ createuser -U manager -D -A newuser
CREATE USER

Notice also the -U manager flag passed to the createuser script. This 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 createuser script will then prompt you for the basic createuser options. These 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 createuser script in interactive mode. The net effect of this example is the same as the single line executed in Example 10-4.

[jworsley@booktown ~]$ createuser
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
CREATE USER

Removing users with the DROP USER SQL command

A superuser may remove a user by issuing the DROP USER command from a valid PostgreSQL client. The psql program is most commonly used to achieve this task.

Here is the syntax for DROP USER:

  DROP USER username

In this syntax, username is the name of the user that you intend to permanently remove from the system. Example 10-9 shows the use of the psql client to connect to PostgreSQL as the manager user in order to remove the salesuser database user.

[jworsley@booktown ~]$ psql -U manager template1
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=# DROP USER salesuser;
DROP USER

The DROP USER server message indicates that the user was successfully removed from the system. Other messages that you might receive from this command include:

Removing users with the dropuser operating system command

The dropuser command operates much like the createuser script. It offers the same connection options, ensuring that it can be used remotely as well as locally, and requires only the username of the user to be removed from the system.

Here is the syntax for dropuser:

  dropuser [ options ] [ username ]

Each of the connectivity options is identical to those for createuser, described in the Section called Creating a user with the createuser script," earlier in this chapter. Example 10-10 demonstrates the same net effect as the SQL statement in Example 10-9 by connecting to the PostgreSQL backend as the manager user, and removing the user named salesuser.

[jworsley@booktown ~]$ dropuser -U manager salesuser
DROP USER

The output from dropuser is the same as the output for the SQL DROP USER command. If you omit the username that you wish to remove when you execute the script dropuser, you will be prompted interactively for the name of that user to be removed from the system.