How to Create User in PostgreSQL: Simple Steps

To create a user in PostgreSQL, use the CREATE USER username WITH PASSWORD 'password'; command. This adds a new user with the specified password who can then be granted permissions to access databases.

Syntax

The basic syntax to create a user in PostgreSQL is:

• CREATE USER username: defines the new user's name.
• WITH PASSWORD 'password': sets the user's password.

You can add options like CREATEDB to allow the user to create databases or SUPERUSER for full privileges.

sql

CREATE USER username WITH PASSWORD 'password';

Example

This example creates a user named alice with password secret123. It shows how to run the command and the expected result.

sql

CREATE USER alice WITH PASSWORD 'secret123';

Output

CREATE ROLE

Common Pitfalls

Common mistakes include:

• Not using single quotes around the password.
• Trying to create a user that already exists.
• Not granting any permissions after creating the user, so the user cannot access databases.

Always check if the user exists before creating, and grant necessary privileges.

sql

/* Wrong: missing quotes around password */
CREATE USER bob WITH PASSWORD secret;

/* Right: quotes included */
CREATE USER bob WITH PASSWORD 'secret';

Quick Reference

Command	Description
CREATE USER username WITH PASSWORD 'password';	Create a new user with a password
ALTER USER username WITH PASSWORD 'newpassword';	Change user's password
DROP USER username;	Remove a user
GRANT ALL PRIVILEGES ON DATABASE database TO username;	Give user full access to a database
REVOKE ALL PRIVILEGES ON DATABASE database FROM username;	Remove user's access to a database

Key Takeaways

Use CREATE USER username WITH PASSWORD 'password'; to add a new user.

Always enclose passwords in single quotes.

Check if the user exists before creating to avoid errors.

Grant appropriate permissions after creating the user for database access.

Use ALTER USER to change passwords and DROP USER to remove users.