Postgres change postgres password windows

Your privacy

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.

Sometimes you may need to change user password in PostgreSQL, for database management and website administration. In this article, we will look at how to change user password in PostgreSQL. You can use these steps to change postgres password in Ubuntu, Windows and other Linux systems. You can also use it to change default postgres password.

Here are the steps to change user password in PostgreSQL.

1. Log into PostgreSQL

Open terminal and run the following command to log into PostgreSQL. Replace username and dbname with your username and database name respectively.

$ sudo -u username psql dbname

Also read : How to change user to superuser in PostgreSQL

2. Change User Password

We will use the ALTER USER command to change user password. Here is its syntax.

ALTER ROLE username WITH PASSWORD 'password';

In the above command, you need to specify the username whose password you want to change, and also the new password for that user.

Run the following ALTER USER command to change user password. Replace testuser and newpassword with

ALTER USER testuser WITH PASSWORD 'newpassword';

Similarly, if you want to change password for postgres user, modify the above command as shown.

ALTER USER postgres WITH PASSWORD 'newpassword';

Also read : How to Copy data from one table to another in SQL

Sometimes you may need to expire a password after a specific date. In such cases, use VALID UNTIL clause with ALTER USER statement. Here’s the above SQL query modified to set password expiration date as December 31, 2020.

ALTER USER testuser WITH PASSWORD 'newpassword'
VALID UNTIL 'December 31, 2020';

As you can see, it is very easy to change user password in PostgreSQL in Ubuntu, CentOS and other systems.

Need a reporting tool for PostgreSQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

(Note: Not much of this is relevant to readers using PostgreSQL 9.2 or above from the EDB installers, which now have a greatly simplified default install using the NETWORK SERVICE, though you can still configure other accounts).

I have used net user postgres postgres
to reset the password for my database but instead of a success message I am getting "System error 5 has occurred. Access is denied."

You’ve reset (or tried to reset) the service account password. PostgreSQL won’t run as Administrator for security reasons and the installer generally sets it up with a «postgres» user account in PostgreSQL 9.1 and older¹. On Windows you can’t start a service as a user without saving the password of the user in the registry, so that’s what the installer does.

If you change the password for the Windows user account postgres, the PostgreSQL service can no longer start. So don’t do that, you’ll have to fix the service configuration to store the updated password.

Thankfully I think another mistake prevented you from doing that. It looks like you’re probably running your command prompt without using «Run as Administrator» on an unprivileged Windows user account or a machine with UAC, so it isn’t running with the access permissions required to change the password for the postgres user.

Before you try to change that password, make sure it’s really what you want to do. What’s the problem you’re trying to solve here? Are you attempting to install a database update or something else that’s asking for the password for the postgres Windows user?

Most likely you’re just trying to log in to the database. For that, you use the (unfortunately completely unrelated) password stored in the database its self. Since you’ve lost/forgotten it you’ll have to reset it:

• Find your pg_hba.conf, usually in C:\Program Files\PostgreSQL\9.1\data\pg_hba.conf
• If necessary, set the permissions on it so that you can modify it; your user account might not be able to do so until you use the security tab in the properties dialog to give yourself that right by using an admin override. Alternately, find notepad / notepad++ in your start menu, right click, choose «Run as administrator», then use File->Open to open pg_hba.conf that way.

• Edit it to set the «host» line for user «postgres» on host «127.0.0.1/32» to «trust». You can add the line if it isn’t there; just insert:

  host all postgres 127.0.0.1/32 trust
  host all postgres ::1/128      trust # if IPv6 is in use
  

  before any other lines. (You can ignore comments, lines beginning with #).

• Restart the PostgreSQL service from the Services control panel (start->run->services.msc)

• connect using psql or PgAdmin-III or whatever you prefer
• ALTER USER postgres PASSWORD 'postgres'
• remove the line you added to pg_hba.conf or change it back
• restart PostgreSQL again.

See: How do I reset the postgres password for PostgreSQL on Windows?

^1. 9.2 now uses the NETWORKSERVICE account, which doesn’t require a password, so this problem goes away.