Practical PostgreSQL

Managing Users

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.

Viewing Users

All user information is stored ...