Passwords allow PostgreSQL users a way to identify themselves and prevent unauthorized individuals from
connecting with a user that is not theirs. As of PostgreSQL 7.1.x, user passwords are
stored in plain text in the pg_shadow system table. The structure of this table is
illustrated in Table 8-1. Note that while the passwords are stored as plain text, only PostgreSQL
superusers
are allowed to view the pg_shadow table.
Table 8-1. The pg_shadow table
|
Column
|
Type
|
|
usename
|
name
|
|
usesysid
|
integer
|
|
usecreatedb
|
boolean
|
|
usetrace
|
boolean
|
|
usesuper
|
boolean
|
|
usecatupd
|
boolean
|
|
passwd
|
text
|
|
valuntil
|
abstime
|
The pg_shadow table is a system table, and thus is accessible from any database. It
follows, therefore, that users are not assigned to a specific database. If a user exists in the
pg_shadow table, that user will be able to connect to any database on the server machine,
though not necessarily from any remote machine (depending on your configuration).
Users typically set passwords in PostgreSQL when the user is created (with the
CREATE USER command) or after the user has been created (using the
ALTER USER command). Alternatively, you may manually modify a user's password by using an
UPDATE statement. (For a more detailed explanation about defining passwords for users, see
Chapter 10.)
If a password is not set, a user's password defaults to NULL. If
password-based authentication is enabled in the
pg_hba.conf
file, connection attempts will always fail
for such a user. Conversely, if the host that establishes the connection is a
trusted
host (such as
localhost
, by default),
anyone
from the trusted host may connect as a user with a
NULL password. In fact, passwords are ignored entirely for trusted hosts.
Note: The GRANT command allows you to restrict or allow a variety of access types to
tables within a database. See Chapter 10 for more on this topic.
Unless your needs for security are very minimal, you will not want to rely on password-only authentication with your PostgreSQL server.
Using a password-only method to authenticate users will allow any verified user access to any database on the system, and authenticating with
a password over clear text can result in unauthorized individuals acquiring user passwords. If you are likely to have your database connected to
the Internet in some fashion, we strongly suggest that you read the following sections. These cover the use of the
pg_hba.conf
file and session encryption.
