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.
