PostgreSQL offers an ALTER USER statement that is used to modify the attributes of a Postgres user. The ALTER USER command allows us to alter/change the user password, privileges, properties, etc. In Postgres, the superusers can use the ALTER USER command to modify the attributes of any Postgres users. However, ordinary users can only change their attributes using the ALTER USER statement.

This Postgres blog will cover the below-given aspects of the ALTER USER statement:

• How to Alter User Permissions in Postgres?
• How to Alter User Password in Postgres?
• How to Alter User's Validity Date in Postgres?
• How to Alter a User to Superuser in Postgres?

So, let’s begin!

How to Alter User Permissions in Postgres?

In PostgreSQL, use the “ALTER USER” statement with the help of the “WITH” clause to change the user’s permission:

ALTER USER user_name WITH user_privileges;

The above snippet shows that to change the user’s permission, you must use the ALTER USER statement followed by the user name. After that, specify the WITH clause followed by the permissions that you want to assign to that particular user.

Let’s do it practically.

Example: Change User Permissions

Let’s follow the below steps to change the user permissions:

Step 1: List of Users

Let’s log in as a superuser and execute the below command to see the list of users:

\du;

In the above snippet, you can see that the user named “sample_user” doesn’t have the privileges to create a role, database, user, etc.

Step 2: Change Permissions

Let’s assign CREATEDB and CREATEROLE privileges to the “sample_user” using ALTER USER statement:

ALTER USER sample_user 
WITH CREATEDB, CREATEROLE;

In the above snippet, the “ALTER ROLE” message shows that the “ALTER USER” statement executed successfully.

Step 3: Verify User Permissions

Let’s execute the “\du” command to verify the user permissions:

\du;

The above snippet proves that the user privileges have been altered/modified successfully.

How to Alter User Password in Postgres?

Use the ALTER USER statement with PASSWORD attribute and specify the modified password within the single quotation to alter the user’s password:

ALTER USER user_name
WITH PASSWORD 'modified_password';

Specify the modified password of your choice in place of the ‘modified_password’.

Example: Change the User Password in Postgres

Let’s suppose we want to change the password of the “hr_role” user. For this purpose, we will use the ALTER USER statement as follows:

ALTER USER hr_role
WITH PASSWORD '12345';

The above snippet proves that the password has been changed.

How to Alter User's Validity Date in Postgres?

To alter the user’s password validity date, use the ALTER USER with VALID UNTIL clause:

ALTER USER user_name
WITH PASSWORD 'modified_password'
VALID UNTIL 'expiry_date_time';

In the above-given syntaxes:

- The ALTER USER is the Postgres statement that modifies a particular role/user.
- user_name is a user to be modified.
- Specify the modified password of your choice in place of the ‘modified_password’.
- VALID UNTIL is used to specify the password validation until a specific date and time.

Example: Change a User’s Password Validity Date in Postgres

Let’s alter the password validity date of a user named ‘sample_user’:

ALTER USER sample_user
WITH PASSWORD '123456'
VALID UNTIL '2025-12-10 11:59:59';

Let’s verify the working of the ALTER USER statement:

\du sample_user;

The output proves that the “sample_user” has been altered successfully.

How to Alter a User to Superuser in Postgres?

Use the ALTER USER command with the SUPERUSER attribute to modify a user to superuser in Postgres:

ALTER USER user_name 
WITH SUPERUSER;

In the above snippet:

- user_name represents a user to be altered.
- WITH is an option used to specify the SUPERUSER attribute.

Example: Change User to Superuser in Postgres

Follow the steps provided below to change a particular user to a superuser in Postgres:

Step 1: List Users

Run the “\du” command to get the list of users:

\du;

The output shows that a user named “hr_role” is an ordinary user.

Step 2: Alter User to Superuser

Let’s suppose we want to alter an ordinary user “hr_role” to a super user:

ALTER USER hr_role 
WITH SUPERUSER;

The “ALTER ROLE” message proves that the ALTER USER statement was executed successfully.

Step 3: Verify Superuser

Let’s verify the superuser via the below command:

\du;

The output authenticates that the user named “hr_role” has been altered successfully.

That’s it from this Postgres guide.

Conclusion

A Postgres user's attributes can be changed using the ALTER USER statement. We can modify/alter a user's password, privileges, etc., with the ALTER USER command. The superusers in Postgres can change any Postgres user's attributes using the ALTER USER command. While ordinary users can only change their own attributes using the ALTER USER command. This Postgres guide has explained the working of ALTER USER statements via practical examples.