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
Check the interface for command-line
psqlis 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 -U objectrocket -h 127.0.0.1 -d some_database
NOTE: Edit the name of the
-ddatabase 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 ROLEis an alternative way to generate a role or user name. Use this statement in SQL to do that:
CREATE ROLE newuser WITH PASSWORD 'weakPass';
\duat the command line in
psqlto 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:
- Next, you’ll create attributes for the role. Add
SUPERUSERrole, and then add
ALTER USER newuser WITH SUPERUSER;
ALTER USER newuser WITH CREATEDB;
Change the user password with the ‘ALTER ROLE’ keyword
- Replace the
some_userpassword with the
ALTER ROLEstatement in SQL.
ALTER ROLE newuser WITH PASSWORD 'new_pass';
- When finished, you should see a reply of
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.
VALID UNTILand 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
\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.
\passwordcommand that’s built-in. Use it like this to replace the password:
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.
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