How to Alter User in PostgreSQL: Syntax and Examples

In PostgreSQL, you can alter a user using the ALTER USER command followed by the username and the changes you want to apply, such as changing the password or role attributes. For example, ALTER USER username WITH PASSWORD 'newpassword'; updates the user's password.

Syntax

The ALTER USER command modifies an existing PostgreSQL user. You specify the user name and the changes to apply. Common options include changing the password, setting connection limits, or changing role attributes like SUPERUSER or CREATEDB.

• ALTER USER username: The user to modify.
• WITH: Introduces the options to change.
• PASSWORD 'password': Sets a new password.
• SUPERUSER | NOSUPERUSER: Grants or revokes superuser status.
• CREATEDB | NOCREATEDB: Allows or disallows database creation.
• CONNECTION LIMIT n: Limits simultaneous connections.

sql

ALTER USER username WITH PASSWORD 'newpassword';
ALTER USER username WITH SUPERUSER;
ALTER USER username WITH CONNECTION LIMIT 5;

Example

This example shows how to change a user's password, grant superuser privileges, and set a connection limit.

sql

CREATE USER testuser WITH PASSWORD 'oldpass';

-- Change password
ALTER USER testuser WITH PASSWORD 'newpass123';

-- Grant superuser
ALTER USER testuser WITH SUPERUSER;

-- Set connection limit
ALTER USER testuser WITH CONNECTION LIMIT 3;

-- Check user attributes
\du testuser

Output

 Role name | Attributes           | Member of 
-----------+----------------------+-----------
 testuser  | Superuser            | {}        
(1 row)

Common Pitfalls

Common mistakes when altering users include:

• Trying to alter a user that does not exist, which causes an error.
• Using ALTER USER without WITH keyword or incorrect syntax.
• Not having sufficient privileges to alter users (you must be a superuser or have appropriate rights).
• Forgetting to reload or reconnect for some changes to take effect.

sql

/* Wrong: missing WITH keyword */
ALTER USER testuser PASSWORD 'newpass';

/* Correct: */
ALTER USER testuser WITH PASSWORD 'newpass';

Quick Reference

Option	Description
PASSWORD 'password'	Change the user's password
SUPERUSER | NOSUPERUSER	Grant or revoke superuser status
CREATEDB | NOCREATEDB	Allow or disallow database creation
CONNECTION LIMIT n	Set max simultaneous connections
VALID UNTIL 'timestamp'	Set password expiration time

Key Takeaways

Use ALTER USER username WITH followed by options to modify a PostgreSQL user.

Common changes include updating passwords, roles, and connection limits.

Always ensure the user exists and you have sufficient privileges before altering.

Syntax errors often come from missing the WITH keyword.

Check user attributes after changes with \du or querying pg_roles.