4 Ways To Create a User in PostgreSQL

Managing database access requires a firm understanding of how PostgreSQL identifies who can connect and what they can do.

Creating a user in PostgreSQL—technically a “role” with login privileges—is the first step in securing your data cluster and organizing team access. This guide explains four practical methods to add new identities to your system.

Master these approaches to maintain a secure and efficient database environment.

Method 1: Using the CREATE USER Command

The most direct way to add a user is through an SQL command.

In PostgreSQL, CREATE USER is a convenient shorthand for creating a role that is automatically granted the ability to log in.

Run this command within the psql terminal: CREATE USER username WITH PASSWORD 'secure_password'; Using this syntax ensures the role has the LOGIN attribute enabled by default.

You can further extend this command by adding attributes like CREATEDB if you want the user to be able to create their own databases.

Method 2: Applying the createuser Utility

If you prefer working from your operating system command prompt rather than an SQL shell, PostgreSQL includes the createuser wrapper program.

Use this syntax for quick user creation from the terminal: createuser username By default, this utility will prompt you for information or assume defaults based on your current environment.

It is an excellent choice for scripts or administrators who want to avoid logging into the database interactive terminal just to add a role.

See also: Mastering the Linux Command Line — Your Complete Free Training Guide

Method 3: Employing the CREATE ROLE Command

For more granular control, use the base CREATE ROLE command.

Unlike CREATE USER, this command creates a role that cannot log in by default unless you explicitly grant that privilege.

Run this to create a login-capable role: CREATE ROLE name WITH LOGIN PASSWORD 'string';

This method is often used when setting up complex hierarchies where some roles act as “groups” (no login) and others act as “users” (with login).

Method 4: Checking Permissions with \du

Once a user is created, you need to verify their attributes and existence.

The psql program provides specialized meta-commands for instant reporting. To list all roles and their global permissions: \du

The output displays a table showing which users have Superuser status, Create role privileges, or Replication rights.

For checking specific table-level permissions, you can use the \dp command.

Understanding Users vs. Roles

In modern PostgreSQL, there is no physical distinction between a “user” and a “group”—both are simply Roles.

• Users: Roles that have been granted the LOGIN attribute.
• Groups: Roles typically created without the LOGIN attribute, used to collect and manage privileges for multiple members. This unified system allows for flexible security where any role can be a member of another, inheriting its permissions automatically.

Step-by-Step Process

1. Launch your terminal or command prompt application.
2. Connect to PostgreSQL as a superuser (usually the postgres role) using: psql -U postgres.
3. Execute the creation command (e.g., CREATE USER joe WITH PASSWORD 'secret';).
4. Press Enter and look for the confirmation “CREATE ROLE.”
5. Verify the new user by typing \du to see them in the list of database roles.

Practical Tips

• Encryption Matters: Ensure your server uses scram-sha-256 for passwords, as it is more secure than the deprecated MD5 method.
• Mind Case Sensitivity: PostgreSQL automatically folds unquoted names to lower case. If you want a user named “Admin,” you must use double quotes: "Admin".
• Use Inheritance: Grant group roles to individual users to manage permissions efficiently for large teams.
• Bootstrap Safety: Only use the default postgres superuser to create other roles; avoid using it for daily database tasks.

Summary Table

Task	Recommended Command
Quick user creation	CREATE USER name WITH PASSWORD 'pass';
Terminal-based creation	createuser name
Create a group role	CREATE ROLE group_name;
Verify user list	\du
Grant group rights	GRANT group_role TO user_role;

FAQs

What is the quickest way to create a user in PostgreSQL? Use the command CREATE USER username; within psql. This automatically enables the LOGIN attribute, allowing the new identity to connect to the database cluster immediately.

Why can’t my new user log in? Check if the role has the LOGIN attribute. If you used CREATE ROLE without the LOGIN keyword, the identity exists but is blocked from connecting.

Are users tied to a specific database? No. Roles in PostgreSQL are global across the entire cluster. A user created while you are in “Database A” can also access “Database B,” provided they have been granted CONNECT privileges for it.

How do I delete a user? Use the command DROP ROLE username; or the shell wrapper dropuser username. Note that you cannot drop a role if it still owns objects in any database.

How do I change a user’s password? You can update an existing user’s password using the command: ALTER ROLE username WITH PASSWORD 'new_password';.

• 4 Ways To Check User Permissions in PostgreSQL
• 4 Ways To Create a User in PostgreSQL
• The PostgreSQL “Bloat” Trap: Why Your Database Grows Even When You Delete Data
• Why PostgreSQL is Different: 6 Architectural Ideas That Confuse Beginners
• Stop Manually Replying to Google Form Submissions — Automate It Instead