PostgreSQL user passwords are stored in the pg_shadow table. The use of a password allows defined users a way to identify themselves and access a database that they have been granted rights to. Managing passwords in PostgreSQL is typically done by using the CREATE USER command or modified using the ALTER USER command. If a password is not configured for a user, then the password will default to NULL and password authentication (if specified in the pg_hba.conf file) will always fail for that user. We have included basic information on setting PostgreSQL passwords in the following paragraph. For a more detailed explanation about defining passwords for users, see the User Management chapter.
The pg_shadow table is a PostgreSQL system wide table. This means that you do not have the ability to assign users 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. Thus, you will want to make sure that you make appropriate use of the GRANT command. The GRANT command will allow you to restrict access to tables within a given database. The pg_shadow table can be accessed from any PostgreSQL database. Outside of the methods mentioned previous you can also modify a user password by using an UPDATE query on the password information stored in the pg_shadow table.
Example 6-1. Using UPDATE to modify a users PostgreSQL password
booktown=# UPDATE pg_shadow SET passwd='js5429' WHERE usename='jd'; UPDATE 1 booktown=#
You will not want to use password only authentication with your PostgreSQL server unless your needs for security are very minimal. If you are using PostgreSQL you are more than likely going to have your database connected to the Internet in some fashion. As this is the case, we strongly suggest that you read the following sections on using the pg_hba.conf file. As mentioned previously, Using only the password method to authenticate users will allow any user who has been verified to have access to any database on the system.
|Operating System users versus PostgreSQL users|
Keep in mind that PostgreSQL has its own user and password tables. It is not a requirement that your PostgreSQL users match the users that are available to the Operating System.