Modify PostgreSQL password on Linux using command line








Website Administrators change password for PostgreSQL via different ways.


As part of our Server Management Services here at Ibmi Media, we regularly help our customers solve PostgreSQL related errors.


In this context, we shall learn how to change the password for PostgreSQL on Linux using different methods.



How to modify password for PostgreSQL on Linux?

With a command line tool such as Putty, a Linux user can easily change the password for PostgreSQL. We will outline the steps to take when changing PostgreSQL password.


Switching user from root to PostgreSQL user

By default, an installed PostgreSQL will be configured with a user named "postgres". 

Start by logging into your server as the root user, and then switch to PostgreSQL default user via the command below;


su - postgres


To make a connect to PostgreSQL, use the psql command;


psql


Adding and Modifying the password for PostgreSQL default User "postgres"

To change the password for the current user, use the command below;


\password


You will be prompted to enter a new password. Enter the password and enter it again to confirm. After this , quit the process with the command below;


\q


Alternatively, to do the above actions in one line of command, use the command below;


su -c "psql" - postgres


Then with the "ALTER ROLE" statement below, PostgreSQL user's password;


ALTER ROLE username
WITH PASSWORD 'password';
</code>


Sometimes, a user might want to set the password valid until a date and time. If this is the case, simply Use "VALID UNTIL" clause as shown below;


ALTER ROLE username
WITH PASSWORD 'new_password'
VALID UNTIL timestamp;


Omitting the "VALID UNTIL" clause will make the password to be valid all the time.


If for example you want to set the expiration date for the password of the superuser to a future date lets say January 10 2021, the following statement will do the job for you;


ALTER ROLE super
VALID UNTIL 'January 10, 2021';


After setting, do the verification to get more information about the user;


postgres=# \du super;
List of roles
Role name | Attributes | Member of
-----------+---------------------------------------------+-----------
super | Superuser, Cannot login +| {}
| Password valid until 2021-01-10 00:00:00+08 |


For your information, using the "ALTER ROLE" statement will make the password in the server to be in cleartext format. Additionally, in the server log or psql's command history will contain the cleartext password.


Need support to change postgresql password? We can help you.



Conclusion

This article will help you to change postgres user's password via the command line.


For Linux Tutorials

We create Linux HowTos and Tutorials for Sys Admins. Visit us on LinuxAPT.com

Also for Tech related tips, Visit forum.outsourcepath.com or General Technical tips on www.outsourcepath.com