How to Create a Postgres User (5 Methods)

August 7, 2025 / Tutorial

In PostgreSQL, a user is essentially a role with login privileges. Creating users is a common task for database administrators and developers to manage access and permissions. This guide covers five easy methods to create a PostgreSQL user, suitable for different skill levels and environments.

Method 1: Using the psql Command-Line Tool

Use the psql tool to connect to PostgreSQL and run SQL commands directly. It’s fast and ideal for users comfortable with terminal operations.

psql -U postgres
CREATE USER yogesh WITH PASSWORD 'securepass123';

Method 2: Using SQL Statements Within a Database Session

If you’re already inside a PostgreSQL session, you can run SQL statements to create users and assign permissions without switching tools.

CREATE USER demo WITH PASSWORD 'securepass123';
GRANT CONNECT ON DATABASE your_db TO demo;

Method 3: Using the createuser Command

This command-line utility simplifies user creation with flags for privileges. It’s useful for quick setups and scripting during installations.

createuser demo --pwprompt

Optional flags:

createuser demo --superuser --createdb

Method 4: Using pgAdmin (Graphical Interface)

pgAdmin offers a visual way to create users. You fill out forms for username, password, and privileges. No need to write SQL manually.

Steps:
Open pgAdmin and connect to your server.
Go to Login/Group Roles.
Right-click → Create → Login/Group Role.
Fill in the details and click Save.

Method 5: Using an SQL Script

Write user creation commands in a .sql file and run it using psql. This method is great for automation or creating multiple users.

Script (create_user.sql):

CREATE USER demo WITH PASSWORD 'securepass123';
GRANT CONNECT ON DATABASE your_db TO demo;

Run the script:

psql -U postgres -f create_user.sql
Conclusion

Choose the method that fits your workflow: command-line for speed, pgAdmin for ease, or scripts for automation. All methods help manage PostgreSQL users effectively.

You can also check the size of PostgreSQL databases and tables via SSH to manage your storage more efficiently.