How to Set a User Password in PostgreSQL (Create, Change, Reset)

A new analyst needs a PostgreSQL login, an app password is due for rotation, or nobody remembers the postgres password. Each case comes down to how you set a user password, and it’s one statement.

Quick answer: run ALTER ROLE user_name WITH PASSWORD 'new_password'; as a superuser or a CREATEROLE role. To create the user and password together, use CREATE ROLE user_name WITH LOGIN PASSWORD 'new_password';.

In this tutorial, I’ll walk you through the PostgreSQL set user password routine my team follows on PostgreSQL 18: create, change, expire, and fix the login errors that follow.

What You Need Before You Start

• PostgreSQL 18 running (see how to install PostgreSQL on Windows). I tested on 18.6.
• A superuser such as postgres, or a role with CREATEROLE and ADMIN OPTION on the roles you manage. Ordinary roles can only change their own password.
• The psql client (here’s how to connect to a PostgreSQL database).
• A demo database. Mine is dfq_pgpass, with dfq_pp_ roles for a small Seattle analytics team.

Step 1: Create a User With a Password (CREATE ROLE and CREATE USER)

A PostgreSQL user is just a role with the LOGIN attribute. CREATE USER is CREATE ROLE with LOGIN implied, so both lines below make a login role with a password.

-- Login roles for the Seattle analytics team
CREATE ROLE dfq_pp_megan WITH LOGIN PASSWORD 'Str0ng!Pass2026';
CREATE USER dfq_pp_alex WITH PASSWORD 'Alex!Pass2026';
CREATE ROLE dfq_pp_reporting;  -- group role, no login

SELECT rolname, rolcanlogin,
       rolpassword IS NOT NULL AS has_password
FROM pg_authid
WHERE rolname LIKE 'dfq_pp_%'
ORDER BY rolname;

Output:

CREATE ROLE
CREATE ROLE
CREATE ROLE
     rolname      | rolcanlogin | has_password 
------------------+-------------+--------------
 dfq_pp_alex      | t           | t
 dfq_pp_megan     | t           | t
 dfq_pp_reporting | f           | f
(3 rows)

Megan and Alex can log in. The reporting role can’t, because plain CREATE ROLE defaults to NOLOGIN, and it has no password. Important: a null password (or PASSWORD '') makes password logins always fail.

Create a user from the command line with createuser

The createuser utility wraps CREATE ROLE. I added --echo so you can see the SQL it sends.

$bin = "C:\Program Files\PostgreSQL\18\bin"
& "$bin\createuser.exe" -h localhost -U postgres --echo --login --valid-until "2026-12-31 23:59:59-05" dfq_pp_diego
& "$bin\psql.exe" -h localhost -U postgres -d dfq_pgpass -c "\du dfq_pp_diego"

Output:

SELECT pg_catalog.set_config('search_path', '', false);
CREATE ROLE dfq_pp_diego NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN NOREPLICATION NOBYPASSRLS VALID UNTIL '2026-12-31 23:59:59-05';
                       List of roles
  Role name   |                 Attributes                  
--------------+---------------------------------------------
 dfq_pp_diego | Password valid until 2026-12-31 23:59:59-05

Diego’s role logs in, and its password expires at the end of 12/31/2026. Add -P and createuser prompts for the password and encrypts it before sending; --interactive asks about superuser, create-database and create-role rights.

Step 2: Change a User’s Password With ALTER ROLE

Same team, a month later: Megan’s password is due for rotation. ALTER USER is an alias for ALTER ROLE, so either works.

-- Rotate Megan's password, then log in with the new one
ALTER ROLE dfq_pp_megan WITH PASSWORD 'N3w!Pass2026';

\c "host=localhost dbname=dfq_pgpass user=dfq_pp_megan password=N3w!Pass2026"
SELECT current_user, session_user;

Output:

ALTER ROLE
You are now connected to database "dfq_pgpass" as user "dfq_pp_megan".
 current_user | session_user 
--------------+--------------
 dfq_pp_megan | dfq_pp_megan
(1 row)

The \c line opens a new session with the new password, and current_user returns dfq_pp_megan. I test every rotation this way before the app finds a typo.

Use \password in psql to keep the password out of logs

A password typed into ALTER ROLE travels in cleartext and can land in psql history or the server log. The \password meta-command prompts for it, encrypts it, and sends only the hash.

postgres=# \password dfq_pp_megan
Enter new password for user "dfq_pp_megan":
Enter it again:

With no name, \password changes your own password. It’s interactive, so scripts still use ALTER ROLE.

Step 3: Change the postgres Superuser Password

It’s the same statement: ALTER USER postgres WITH PASSWORD '...';, or better, \password postgres. On Linux, sudo -u postgres psql usually gets you in through peer authentication first.

Reset a forgotten postgres password through pg_hba.conf

1. Back up pg_hba.conf (on Windows: C:\Program Files\PostgreSQL\18\data).
2. Change the 127.0.0.1/32 and ::1/128 lines from scram-sha-256 to trust.
3. Reload or restart the PostgreSQL service.
4. Run psql -h localhost -U postgres (no prompt now), then \password postgres.
5. Put scram-sha-256 back and reload again. Never leave trust in place.

Step 4: Expire a Password With VALID UNTIL

Adele Vance is a contractor through 2026, and Alex is moving to certificate login, so his password goes away with PASSWORD NULL.

-- Contractor access that ends with the year
CREATE ROLE dfq_pp_adele WITH LOGIN PASSWORD 'Adele!Pass2026'
  VALID UNTIL '2026-12-31 23:59:59-05';

ALTER ROLE dfq_pp_alex WITH PASSWORD NULL;  -- password login off

SELECT rolname, rolvaliduntil,
       rolpassword IS NOT NULL AS has_password
FROM pg_authid
WHERE rolname IN ('dfq_pp_adele', 'dfq_pp_alex');

Output:

CREATE ROLE
ALTER ROLE
   rolname    |     rolvaliduntil      | has_password 
--------------+------------------------+--------------
 dfq_pp_adele | 2026-12-31 23:59:59-05 | t
 dfq_pp_alex  |                        | f
(2 rows)

VALID UNTIL 'infinity' removes the end date. It limits only the password, not certificate logins. When someone leaves for good, delete the user in PostgreSQL instead.

Step 5: Check the pg_hba.conf Password Authentication Method

A password only matters if pg_hba.conf asks for one. The pg_hba_file_rules view shows what the server loaded, without opening the file.

SELECT line_number AS line, type, address, auth_method
FROM pg_hba_file_rules
WHERE 'all' = ANY (database)
ORDER BY line_number;

Output:

 line | type  |  address  |  auth_method  
------+-------+-----------+---------------
  113 | local |           | scram-sha-256
  115 | host  | 127.0.0.1 | scram-sha-256
  117 | host  | ::1       | scram-sha-256
(3 rows)

All lines use scram-sha-256, and SHOW hba_file; tells you where the file lives (here C:/Program Files/PostgreSQL/18/data/pg_hba.conf). If remote clients can’t connect at all, check the listen_addresses setting before the password.

Where Does PostgreSQL Store User Passwords?

Each password is stored as a hash in pg_authid, which only superusers can read. password_encryption (default scram-sha-256) picks the hash type when the password is set. Here I force md5 once, with -q to hide status lines:

SET password_encryption = 'md5';
ALTER ROLE dfq_pp_alex WITH PASSWORD 'Alex!Pass2026';
RESET password_encryption;

SELECT rolname, left(rolpassword, 18) AS stored_hash
FROM pg_authid
WHERE rolname LIKE 'dfq_pp_%' AND rolpassword IS NOT NULL
ORDER BY rolname;

Output:

psql:md5-vs-scram.sql:2: WARNING:  setting an MD5-encrypted password
DETAIL:  MD5 password support is deprecated and will be removed in a future release of PostgreSQL.
HINT:  Refer to the PostgreSQL documentation for details about migrating to another password type.
   rolname    |    stored_hash     
--------------+--------------------
 dfq_pp_alex  | md5f14fdbb8b3758a6
 dfq_pp_megan | SCRAM-SHA-256$4096
(2 rows)

PostgreSQL 18 accepts the MD5 password but warns that MD5 is deprecated. To fix it, set the same password again under the default:

-- Same password again, now under scram-sha-256
ALTER ROLE dfq_pp_alex WITH PASSWORD 'Alex!Pass2026';
SELECT left(rolpassword, 18) AS stored_hash
FROM pg_authid WHERE rolname = 'dfq_pp_alex';

Output:

ALTER ROLE
    stored_hash     
--------------------
 SCRAM-SHA-256$4096
(1 row)

Alex’s hash starts with SCRAM-SHA-256$4096 again. For a masked view you can share, use \du or pg_roles; here’s how to list users in PostgreSQL.

How Do You Fix “password authentication failed for user”?

After Step 2, I connected with Megan’s old password, just like an app with a stale connection string:

-- The app still sends Megan's OLD password
\c "host=localhost dbname=dfq_pgpass user=dfq_pp_megan password=Str0ng!Pass2026"

Output:

psql:old-password.sql:2: error: \connect: connection to server at "localhost" (::1), port 5432 failed: FATAL:  password authentication failed for user "dfq_pp_megan"

An expired password returns the very same message, even when the password is right:

-- Adele's password expired on 01/01/2026
ALTER ROLE dfq_pp_adele VALID UNTIL '2026-01-01';
\c "host=localhost dbname=dfq_pgpass user=dfq_pp_adele password=Adele!Pass2026"

Output:

ALTER ROLE
psql:expired-login.sql:3: error: \connect: connection to server at "localhost" (::1), port 5432 failed: FATAL:  password authentication failed for user "dfq_pp_adele"

The client never learns the real reason; the server log does (wrong, expired, or no password). For other causes, see our password authentication failed checklist.

PostgreSQL User Password Cheat Sheet

Task	Command	Notes
Create a login	CREATE ROLE name WITH LOGIN PASSWORD '...';	CREATE USER implies LOGIN
Create from the shell	createuser -P name	Prompts, encrypts
Change a password	ALTER ROLE name WITH PASSWORD '...';	ALTER USER is an alias
Change without cleartext	\password name	psql, interactive
Expire a password	ALTER ROLE name VALID UNTIL '2026-12-31';	'infinity' = no end
Remove a password	ALTER ROLE name WITH PASSWORD NULL;	Password logins fail
Check stored hashes	SELECT rolname, rolpassword FROM pg_authid;	Superuser only

Common Mistakes to Avoid With PostgreSQL Passwords

Forgetting LOGIN. CREATE ROLE name PASSWORD '...' without LOGIN can’t log in. Secrets in scripts: for live accounts I use \password or createuser -P.

Switching to scram-sha-256 too early. An scram-sha-256 line rejects old MD5 hashes, so reset those passwords first.

Losing roles in a restore. A dump doesn’t include roles; when you back up a PostgreSQL database, add pg_dumpall --globals-only.

Next Steps After Setting a User Password

A password only gets a user in. Next, grant privileges; PostgreSQL schemas decide what each login can touch. We’d suggest these guides too:

• Fix could not connect to PostgreSQL server
• Create a database in PostgreSQL
• Install PostgreSQL on Linux step by step
• Check which PostgreSQL version you’re running

Frequently Asked Questions