Every database cluster contains a set of database users. Those
users are separate from the users managed by the operating system on
which the server runs. Users own database objects (for example,
tables) and can assign privileges on those objects to other users to
control who has access to which object.
This chapter describes how to create and manage users and introduces
the privilege system. More information about the various types of
database objects and the effects of privileges can be found in the
PostgreSQL 7.3 User's Guide.
Database users are conceptually completely separate from
operating system users. In practice it might be convenient to
maintain a correspondence, but this is not required. Database user
names are global across a database cluster installation (and not
per individual database). To create a user use the CREATE
USER SQL command:
CREATE USER name
name follows the rules for SQL
identifiers: either unadorned without special characters, or
double-quoted. To remove an existing user, use the analogous
DROP USER command:
DROP USER name
For convenience, the programs createuser
and dropuser are provided as wrappers
around these SQL commands that can be called from the shell command
line:
createuser name
dropuser name
In order to bootstrap the database system, a freshly initialized
system always contains one predefined user. This user will have the
fixed ID 1, and by default (unless altered when running
initdb) it will have the same name as
the operating system user that initialized the database
cluster. Customarily, this user will be named
postgres. In order to create more users
you first have to connect as this initial user.
Exactly one user identity is active for a connection to the
database server. The user name to use for a particular database
connection is indicated by the client that is initiating the
connection request in an application-specific fashion. For example,
the psql program uses the
-U command line option to indicate the user to
connect as. Many applications assume the name of the current
operating system user by default (including
createuser and psql). Therefore it
is convenient to maintain a naming correspondence between the two
user sets.
The set of database users a given client connection may connect as
is determined by the client authentication setup, as explained in
Chapter 6. (Thus, a client is not
necessarily limited to connect as the user with the same name as
its operating system user, in the same way a person is not
constrained in its login name by her real name.) Since the user
identity determines the set of privileges available to a connected
client, it is important to carefully configure this when setting up
a multiuser environment.
