PostgreSQL Change User Password

Introduction to the PostgreSQL change user password tutorial

There are many different types of situations where you’ll need to perform a PostgreSQL change user password. For example, when a user changes roles, that is when certain permissions must be are given or retracted. Another circumstance could be when an employee leaves the company or a new employee joins. It’s easy to make a user password change in PostgreSQL. Well, this tutorial will show you how to accomplish that in a few steps.

Prerequisites to changing the user password in Postgres

  • Install and run PostgreSQL if you haven’t previously.

  • Verify that PostgreSQL is installed correctly on your OS. Use the command postgres -V.

  • Check the interface for command-line psql is accessible with the command psql - V.

  • Make a sample PostgreSQL database. In it, create a table and add a few records. Ensure that you have permission to modify those records.

  • Finally, to prepare to make a PostgreSQL change user password, connect the PostgreSQL database to the server with this psql command:

psql -U objectrocket -h 127.0.0.1 -d some_database

NOTE: Edit the name of the -U user and -d database to match your user name and the sample database name you created.

Create a user role and password for your Postgres database

Stay connected to psql. Now, you’re going to construct a user role for user password change in PostgreSQL. You want to assign privileges to alter tables within a specific database.

  • First, use the following statement in SQL to establish a new user with a password that is encrypted:
CREATE USER newuser WITH ENCRYPTED PASSWORD '1234';

NOTE: Don’t be alarmed about the text case of the user names and roles. By default, they’ll format to all lowercase letters.

Use the SQL ‘CREATE ROLE’ keyword to create a new role with password

  • CREATE ROLE is an alternative way to generate a role or user name. Use this statement in SQL to do that:
CREATE ROLE newuser WITH PASSWORD 'weakPass';
  • Input \du at the command line in psql to display the role you just produced. You’ll be able to see the entire list of roles and their associated granted privileges.

If for a role of newuser, you see a ‘cannot login’ attribute, this means you need to give the new user privileges to log in. Without it, the new user won’t be able to access the database cluster.

NOTE: The PostgreSQL server log or history is where the passwords are saved and located.

Grant privileges and role attributes to Postgres user

  • The command to use for granting permissions is GRANT. This will give you the ability to give a user database access privileges for the tables you’ve created:
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO newuser;
  • You should see a response that looks like this:
GRANT
  • Next, you’ll create attributes for the role. Add SUPERUSER role, and then add CREATEDB.
ALTER USER newuser WITH SUPERUSER;
ALTER USER newuser WITH CREATEDB;

Screenshot of Postgres user role in psql with attributes and privileges

Change the user password with the ‘ALTER ROLE’ keyword

  • Replace the some_user password with the ALTER ROLEstatement in SQL.
ALTER ROLE newuser WITH PASSWORD 'new_pass';
  • When finished, you should see a reply of ALTER ROLE.

Create a temporary password for a PostgreSQL user

  • Sometimes, you’ll need to perform a PostgreSQL change user password by creating a temporary password. These types of passwords require an expiration clause.

  • Use VALID UNTIL and enter the date you want the password to inactivate. The user must create a new password before the expiration date you’ve indicated in the SQL statement:

ALTER ROLE newuser WITH PASSWORD 'finalPass' VALID UNTIL 'Feb 20, 2020';

Confirm that you set the temporary password.

*From the command line psql, type \du. The role should display as well as the temporary password.

Use the psql command to connect to PostgreSQL and change the user password

At any time, you can PostgreSQL change user password for a role you made.

  • psql has a \password command that’s built-in. Use it like this to replace the password:
\password newuser

You’ll see an Enter new password: message prompt. Input your new password. Next, you’ll be required to enter the new password again to verify that it matches the new one you inputted.

Screenshot of psql connection to Postgres user to alter role password

Conclusion to the PostgreSQL change user password tutorial

This concludes the user password change PostgreSQL tutorial. You learned how to create a new user and set up encrypted passwords. You also discovered how to grant database table access privileges. Setting up temporary passwords with expiration dates is a great way to help secure your databases too. Incorporate these PostgreSQL change user password steps into all of your projects starting today.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.