As in most database systems, users and groups handle an important role within
PostgreSQL. Used correctly, users and groups can allow for fine-grained, versatile access control to your database
objects.
PostgreSQL stores both user and group data within its own system catalogs. These are different from the users and groups
defined within the operating system on which the software is installed. Any connection to PostgreSQL must be made with a
specific user, and any user may belong to one or more defined groups.
Users control the allocation of rights and track who is allowed to perform actions on
the system (and which actions they may perform). Groups exist as a means to simplify the allocation of these rights.
Both users and groups exist as global database objects, which means they are not tied to any particular database.
This chapter addresses the management and practical application of PostgreSQL users and groups.
In order to establish a connection to PostgreSQL, you must supply a basic form of identification. This is called a
username, as it identifies the user who the system will recognize as connected to a
database. Users within PostgreSQL are not necessarily related to users of the operating system (which are sometimes called
system accounts), though you may choose to name your PostgreSQL users after the system accounts that
will be accessing them.
Each user has an internal system ID to PostgreSQL (called a sysid), as well as a password, though
the password is not necessarily required to connect (depending on the configuration of the pg_hba.conf ;
(see Chapter 8, for more on this subject). The user's system ID is used to associate objects
in a database with their owner (the user who is allowed to grant and revoke rights on an object).
As well as being used to associate database objects with their owner, users may also have global
rights assigned to them when they are created. These rights determine whether or not a user is allowed to create and destroy
databases, and whether or not the user is a superuser (a user who is allowed all rights, in all
databases, including the right to create users). The assignment of these rights may be modified at any time by an existing
superuser.
PostgreSQL installs a single superuser by default named postgres. All other users must
be added by this user, or by another subsequently added superuser.
All user information is stored in a PostgreSQL system table called pg_shadow, shown
in Table 10-1. This table is only selectable by superusers, though a limited view of this table
called pg_user is accessible to normal users.
Table 10-1. The pg_shadow table
Column
Type
usename
name
usesysid
integer
usecreatedb
boolean
usetrace
boolean
usesuper
boolean
usecatupd
boolean
passwd
text
valuntil
abstime
The primary difference between the selectable data in pg_user and
pg_shadow is that the actual value of the passwd column
is not shown (it is replaced with a string of asterisks). This is a security measure to ensure that normal users are not
able to determine one another's passwords.
The usename column stores the name of the system user, which is a unique character
string (no two users may have the same name, as users are global database objects). Similarly, the
usesysid column stores a unique integer value associated with the user. The
usecreatedb and usesuper each correspond to the pair of
privileges which can be set upon creation of a user, as documented in the Section called Creating Users."
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., psql ). The second is a command-line
wrapper called createuser, 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:
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:
SYSID uid
Specifies that the system ID is to be set to the value of uid. If omitted, a reasonable,
unique numeric default is chosen.
PASSWORD 'password'
Sets the new user's password to password. If unspecified, the password defaults to
NULL.
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
therefore have all rights, 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.
IN GROUP groupname [, ...]
Adds the new user to the group named groupname. 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 'abstime'
Sets the user's password to expire at abstime, which must be of a recognizable timestamp format.
After that date, the password must be reset, and the expiration moved forward.
VALID UNTIL 'infinity'
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 N0rm4! 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
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:
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
DbuS3r, which will be valid until November 11th, 2002.
Example 10-2. Creating a user with CREATEDB rights
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.
Warning
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
host-based authentication.
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.
Example 10-3. Creating a
superuser
template1=# CREATE USER manager CREATEUSER;
CREATE USER
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:
-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
all 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.
-i SYSID, - -sysid=SYSID
Sets the new users system ID to SYSID.
-P, - -pwprompt
Results in a password prompt allowing you to set the password of the new user username.
-h HOSTNAME, - -host=HOSTNAME
Specifies that HOSTNAME will be connected to, rather than the localhost, or the host defined
by the PGHOST environment variable.
-p PORT, - -port=PORT
Specifies that the database connection will be made on port PORT, rather than the
default port (usually 5432).
-U USERNAME, - -username=USERNAME
Specifies that USERNAME will be the user who connects to PostgreSQL
(The default is to connect using the name of the system user executing the createuser script).
-W, - -password
Results in a password prompt for the connecting user, which happens automatically if the
pg_hba.conf file is configured not to trust the requesting host.
-e, - -echo
Causes the CREATE USER command sent to PostgreSQL to be displayed to the screen as it is
executed by createuser.
-q, - -quiet
Prevents output from being sent to stdout (though errors will
still be sent to stderr).
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.
Example 10-4. Creating a user with createuser
[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.
Example 10-5. Interactively creating a user with createuser
[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
Existing users may only be modified by PostgreSQL superusers. Possible modifications include each of the options
available at the creation of the user (e.g., password, password expiration date, global rights), except for the system ID
of an existing user, which may not be modified. Modification of existing users is achieved through the use of the
ALTER USER SQL statement.
Here is the syntax for ALTER USER:
ALTER USER username
[ WITH PASSWORD 'password' ]
[ CREATEDB | NOCREATEDB ]
[ CREATEUSER | NOCREATEUSER ]
[ VALID UNTIL 'abstime' ]
The required username argument specifies which user is to be modified. Any of the following
parameters may additionally be specified:
WITH PASSWORD 'password'
Sets username's password to password.
CREATEDB | NOCREATEDB
Grants or revokes from username the right to create databases.
CREATEUSER | NOCREATEUSER
Grants or revokes from username the status of superuser, which enables all
possible right within PostgreSQL (most notably the ability to create and destroy users and superusers).
VALID UNTIL 'abstime'
Sets username's password to expire at abstime, which must be of some valid
timestamp format. This value is only relevant for systems requiring password authentication, and is otherwise ignored
(e.g., for trusted sites).
A common function of ALTER USER is to reset the password (and potentially the
expiration date) of a user. If a PostgreSQL user had an expiration date set when their user was originally added, and that date has passed, and the user
requires password-based authentication, a superuser will have to reset both the password and the
expiration date to re-activate a user's ability to connect. If you want to cause a user's password to never expire, set it
to the special timestamp infinity.
Example 10-6 modifies a user named salesuser. The user's
password is set to n3Wp4s4 by the WITH PASSWORD clause, and set to
expire on January 1st, 2003 by the VALID UNTIL clause.
Example 10-6. Resetting a password
template1=# ALTER USER salesuser
template1-# WITH PASSWORD 'n3WP4s4'
template1-# VALID UNTIL '2003-01-01';
ALTER USER
At times you may wish to grant a user additional rights beyond those originally granted to them. The use of the
CREATEUSER keyword in Example 10-7 modifies the user
salesuser to have all rights in PostgreSQL, making the user into a superuser. Note
that this makes the CREATEDB right moot, as superusers can create databases
implicitly.
Example 10-7. Adding superuser rights
template1=# ALTER USER salesuser
template1-# CREATEUSER;
ALTER USER
Conversely, there may be times when a user no longer deserves rights that have been granted in the past. These rights
may be just as easily removed by a superuser with the NOCREATEDB and
NOCREATEUSER keywords.
Example 10-8. Removing superuser rights
template1=# ALTER USER salesuser
template1-# NOCREATEDB NOCREATEUSER;
ALTER USER
Warning
As any superuser may revoke rights from another superuser, or even remove another superuser, it is wise to be
extremely careful when granting the CREATEUSER right.
PostgreSQL users may at any time be removed from the system by authenticated superusers. The only restriction is that
a user may not be removed if any databases exist which are owned by that user. If a user owns a database, that database
must be dropped before the user can be removed from the system.
As with the creation of PostgreSQL users, there are two methods by which users may be removed. These are the
DROP USER SQL command, and the dropuser command-line executable.
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.
Example 10-9. Removing a user with DROP 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:
ERROR: DROP USER: permission denied
Indicates that the user initiating the command does not have the right to drop a user. Only superusers may drop
existing database users.
ERROR: DROP USER: user "salesuser" does not exist
Indicates that there is no such user with the name salesuser.
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.
Example 10-10. Removing a user with dropuser
[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.