While working with databases, creating a separate user for each person who needs database access is a good practice. It assists us to control and monitor who can access which resources. Maintaining Postgres security is essential for database administrators therefore creating a user with a strong and unique password can reduce security risks.
This post will present a detailed guide on creating a user with a password in PostgreSQL through practical examples. So, let’s start!
How to Create a User With Password in PostgreSQL?
In PostgreSQL, the “CREATE USER” and “CREATE ROLE” commands are used to create a new user. To create a user with a password, you must execute any of these commands with the “PASSWORD” attribute as follows:
CREATE USER user_name WITH PASSWORD ‘user_password’;
Here in the above syntax:
CREATE USER is a statement, user_name represents a user to be created, WITH is a clause used to specify the “PASSWORD” privileges, and “user_password” represents the user's password.
Example 1: Creating a User/Role With a Password Attribute
Suppose we want to create a user named “joseph” with password privileges. For this purpose, we will use the CREATE USER statement with the PASSWORD attribute as follows:
CREATE USER joseph WITH PASSWORD ‘user_password’;
Let’s execute the “\du” command to show the newly created user:
The output snippet shows that a user named “joseph” has been created successfully.
Example 2: Creating a User With a Password Valid Until
Postgres allows us to create a user with a password along with validity/expiry date. The password will expire after a specified time period:
CREATE USER joseph WITH PASSWORD ‘user_password’ VALID UNTIL 2025-12-01;
Let’s execute the “\du” command to show the newly created user:
A user named “ambrose” has been created with the password attributes. The password will expire on “2025-12-01”.
Example 3: Creating a Superuser
To create a superuser, users need to issue the below-provided command:
CREATE USER mike SUPERUSER LOGIN PASSWORD '12345';
Let’s execute the “\du” command to show the newly created user:
The output shows that a user named “mike” is created with superuser attributes.
This is how you can create an ordinary or a superuser with a password in PostgreSQL.
Conclusion
In PostgreSQL, the “CREATE USER” and “CREATE ROLE” commands are used to create a new user. To create a user with a password, you must execute any of these commands with the “PASSWORD” attribute. You can create a user whose password will expire after the specified date using the VALID UNTIL clause and the CREATE USER or CREATE ROLE command. This blog explained how to create an ordinary or a superuser with the password attribute in PostgreSQL.
