Postgres change password for postgres user windows

(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 FilesPostgreSQL9.1datapg_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.

1. Change the User Password in Postgres Using Windows
2. Change the User Password in Postgres Using Linux

In this tutorial, we will change the user password in Postgres.

Change the User Password in Postgres Using Windows

• Open the SQL Shell (psql) from the menus or search bar.
• Connect to the default database with the default port. If you set up with default settings, then the configuration should look like this:

    Server [localhost]:
    Database [postgres]:
    Port [5432]:
    Username [postgres]:
    Password for user postgres:

If you put the field blank and press empty, it will take the default values shown between the square bracket.

Then, log in with the default password you set during the installation. If you’re successful, then you will get the SQL command line:

psql (14.0)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=#

Now, if you want to see all the user lists, type the command:

postgres=# select * from USER;
   user
----------
 postgres
(1 row)

You need to alter the user information using ALTER to change the user password. The query format for changing the user password:

postgres=# ALTER USER postgres WITH PASSWORD 'ROOT';
ALTER ROLE
postgres=#

Instead of postgres, you can type your username, which you want to update, and instead of Root, you can use your defined password.

Or, there’s another method for changing the passwords without writing any SQL query. After opening the psql command line, type password user_name.

Then it will ask for a new password and again ask to retype the new password.

postgres=# password postgres
Enter new password: <Enter-your-password>
Enter it again: <Retype-your-password>
postgres=#

Change the User Password in Postgres Using Linux

You can enter the psql console without any password. Use the sudo command.

Here’s how you can log in without a psql password:

$sudo -u <username> psql <database>

Use the above method to change the password for a user in psql.

It’s a good practice to save the user password encrypted. You can use the following command:

postgres=# ALTER USER postgres WITH ENCRYPTED PASSWORD 'ROOT';

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!

Introduction to Postgres Change Password

In this article, we will learn how we can change the Postgres Change Password of the user if present and if not how we can assign a password to the user for further authenticated usage by him in the PostgreSQL database server. There are two methods to do so. The first method involves using the ALTER query statement to change the password and the second method is to use the meta-command password in PostgreSQL’s psql utility.

To proceed with changing the password process, we first need to understand how password mechanism works in PostgreSQL and what password policy is set to the default superuser which is most often user named Postgres.

In any Unix distribution system of PostgreSQL, there are two types of authentication methods namely ident and peer. The default authentication method depends on which version of PostgreSQL does it use and how is PostgreSQL installed on your machine.

Ident Authentication method: In this method, TCP port with 113 as port number authenticates the user’s credentials where the identification server of the operating system is running.

Peer Authentication Method: In the peer authentication method, the current user’s password of PostgreSQL is matched with the password of the operating system user’s password.

Syntax

Format 1:

ALTER USER name [ [ WITH ] option [ ... ] ] where option can be:
CREATEDB | NOCREATEDB
| CREATEUSER | NOCREATEUSER
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'newPassword'
| VALID UNTIL 'expirytime'

Explanation: Using the above alter command the password of the user can be changed and along with that other options can also be reassigned.

Name: It is the name of the user or role whose properties or password you want to change.

Option: We can change multiple parameters and privileges associated with the user using this format.

CREATEDB: This can be specified if you want to give the privilege to the user to create a new database

NOCREATEDB: This can be mentioned if you want to restrict the user from creating any new database.

CREATEUSER: This property can be specified to allow the user to create new users.

NOCREATEUSER: When this property is mentioned in the query in the above format the user won’t be able to create new users.

ENCRYPTED: This property determines whether the password stored in the pg_catalog’s pg_shadow table is stored in the form of an MD5 encrypted format.

UNENCRYPTED: The password is not stored in encrypted format in pg_catalog. If neither ENCRYPTED or UNENCRYPTED property is specified and neither this is done while user creation then the default password storing mechanism is decided based on password_encryption configuration variable.

PASSWORD: The new password is the string that you want to set as the password for the user. If this field is not specified and the user doesn’t have any previously set password to it then no authentication will be done for the user and the user can log in to the system without mentioning the password. But in case if you switch to a password authentication system then the user won’t be able to log in.

VALID UNTIL expiry time: This field can be used if you want to allow the set password up to some specific period. This field if the timestamp up to which you want to permit the assigned password to work.

Examples to Implement Postgres Change Password

Below are examples mentioned:

Example #1

We will firstly login to the system by Postgres default user. Here we have assigned a password to the Postgres role already. So, we will enter the password.

Code:

sudo su - postgres

Output:

Example #2

Further, let us check all the users which are present in the database server by firing the command using psql promo:

Code:

select username from pg_catalog.pg_user;

Output:

Example #3

Let us try to login using a user:

Code:

sudo su – a;

Output:

Example #4

As we have forgotten the password associated to that user assigned to it while its creation, we will reset it to pay by using the format 1 ALTER USER query in the following way:

Code:

ALTER USER a WITH ENCRYPTED PASSWORD 'payal';

Output:

As the output is ALTER ROLE. The password has been reset successfully.

Format 2:

ALTER USER name RENAME TO alteredName

This format is only used to alter the name of the user to some other name “alteredName”.

Example #5

Code:

ALTER USER a RENAME TO payal;

Output:

Example #6

Let us verify the available users in our database server now.

Code:

SELECT usename FROM pg_catalog.pg_user;

Output:

Example #7

Code:

ALTER USER name SET parameter { TO | = } { targetValue | DEFAULT }

the parameter can be any configuration property of PostgreSQL. You can see all the configuration properties by firing the command

SHOW ALL;

Output:

Example #8

ALTER USER name RESET parameter

This command is used to reset the value of any field related to the user. Example –

Now, in case if we want to reset the password of the payal user. we can do so by using the query statement :

Code:

ALTER USER payal RESET password;

Output:

That gives the output “ALTER ROLE” which means that password for payal user has been reset successfully.

Example #9

MetaCommand to change password: In PostgreSQL, we have this amazing functionality called meta-commands that can be used with the help of psql utility. MetaCommands are short commands which are provided to make the working

of database administrator easy and efficient. These metacommands internally fire the SQL commands which are basic like ALTER, CREATE, SELECT, etc. One such meta-command for changing the password of the user is available and named password. It asks to enter the password and then reenter the password for confirmation and then sets the entered password for that user.

password

Let us check the working of metacommand with the help of an example. Suppose we want to change the password of Postgres user after login to the Postgres database. Then we will query for the same in the following steps:

Code:

psql -d postgres -U postgres
password

Output:

Conclusion

We can change the password of the user either by using the ALTER command or metacommand password in PostgreSQL.

Recommended Articles

This is a guide to Postgres Change Password. Here we discuss an introduction to Postgres Change Password, syntax, examples with code and output. You can also go through our other related articles to learn more –

1. PostgreSQL Database
2. PostgreSQL Trunc()
3. PostgreSQL cluster
4. PostgreSQL Functions

Introduction

How to change postgres account password? In case of forgotten password, hence unable to access the database management GUI. This is my experience when using Windows. It might be the same with other operating system.

The Command Prompt Way

First let me put a note using command prompt technique. The GUI technique is much simpler and presented later in this same writeup.

To do this using command prompt. It is good to know that in windows there will be 2 account. One is postgres windows user account. The other is database admin account. Which is also called postgres.

ALTER USER Postgres WITH PASSWORD ‘<newpassword>’

Using pgAdmin to Graphically Reset the Password

This is simpler and I would say faster. Just accidentally found that if pg_hba.conf localhost method is set to trust. There is no need to enter password when connecting to the database using pgAdmin.

Some of the step are similar to when using command prompt technique.

2. Open up Services manager and restart postgresql service. 

In Closing

I learn the hard way about not writing up this stuff earlier. Wasting time looking for good tutorial on how to do this