Postgres Password

Summary: in this tutorial, you will learn how to change the password for a user in PostgreSQL.

Change Postgres Password

To change the password of a PostgreSQL user, you use the ALTER ROLE statement as follows:

Apr 02, 2020 su - postgres then attempt a connection to PostgreSQL. Psql enter your password at the prompt. Password: the correct, valid response will be similar to the following. Psql (9.3.9) Type 'help' for help. Postgres=# Step #2: Add/Change the PostgreSQL Password for the User: postgres. PostgreSQL database passwords are separate from operating system user passwords. The password for each database user is stored in the pgauthid system catalog. Passwords can be managed with the SQL commands CREATE ROLE and ALTER ROLE, e.g., CREATE ROLE foo WITH LOGIN PASSWORD 'secret', or the psql command password.

In this statement, to change the password of a user:

Postgresql postgres password
  • First, specify the username who you want to change the password.
  • Second, provide the new password wrapped within single quotes (‘).

For example, the following statement changes the password of the super user to secret123.

Sometimes, you want to set the password valid until a date and time. In this case, you use the VALID UNTIL clause:

Postgres password change

Note that if you omit the VALID UNTIL clause, the password will be valid for all time.

Postgresql

The following statement sets the expiration date for the password of super user to December 31 2020:

To verify the result, you can view the detailed information of user:

Note that using the ALTER ROLE statement will transfer the password to the server in cleartext. In addition, the cleartext password may be logged in the psql’s command history or the server log.

In this tutorial, you have learned how to change the password of a PostgreSQL user using the ALTER ROLE statement.

The file .pgpass in a user's home directory or the file referenced by PGPASSFILE can contain passwords to be used if the connection requires a password (and no password has been specified otherwise). On Microsoft Windows the file is named %APPDATA%postgresqlpgpass.conf (where %APPDATA% refers to the Application Data subdirectory in the user's profile).

Postgres password hash

This file should contain lines of the following format:

(You can add a reminder comment to the file by copying the line above and preceding it with #.) Each of the first four fields can be a literal value, or *, which matches anything. The password field from the first line that matches the current connection parameters will be used. (Therefore, put more-specific entries first when you are using wildcards.) If an entry needs to contain : or , escape this character with . A host name of localhost matches both TCP (host name localhost) and Unix domain socket (pghost empty or the default socket directory) connections coming from the local machine. In a standby server, a database name of replication matches streaming replication connections made to the master server. The database field is of limited usefulness because users have the same password for all databases in the same cluster.

Postgres Password Lost

On Unix systems, the permissions on .pgpass must disallow any access to world or group; achieve this by the command chmod 0600 ~/.pgpass. If the permissions are less strict than this, the file will be ignored. On Microsoft Windows, it is assumed that the file is stored in a directory that is secure, so no special permissions check is made.