how to create user in Postgresql – complete guide

In PostgreSQL, managing users effectively is more than just running a single command—it’s about setting up a secure, scalable hierarchy. Technically, PostgreSQL doesn’t distinguish between a “user” and a “group”; both are referred to as Roles.

A User is simply a role that has the LOGIN attribute enabled.

Key Takeaways

• Command: Use CREATE USER (which automatically includes login rights) or CREATE ROLE ... LOGIN.
• Security: Always assign a strong password and follow the Principle of Least Privilege.
• Best Practice: Create “Group Roles” for permissions and assign users to those groups rather than granting permissions to individuals.

---

Step-by-Step: How to Create a User in PostgreSQL

To follow these steps, you must be logged into your database as a superuser (typically the default postgres user).

1. Connect to your PostgreSQL Instance

Open your terminal or command prompt and connect using the psql utility:

Bash

psql -U postgres

2. Create the User with a Password

The simplest way to create a user is using the CREATE USER command. This automatically gives the role the ability to log in.

SQL

CREATE USER sam_dev WITH PASSWORD 'your_secure_password_here';

3. Grant Specific Privileges

By default, a new user has no permissions to access data in existing databases. You must grant them access to a specific database or schema.

SQL

-- Allow the user to connect to a specific database
GRANT CONNECT ON DATABASE project_db TO sam_dev;

-- Allow the user to use a specific schema
GRANT USAGE ON SCHEMA public TO sam_dev;

-- Allow the user to read data from all tables in that schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO sam_dev;

4. Verify the New User

You can list all existing users and their attributes by running:

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

SQL

\du

This will display a table showing the role name and its associated attributes (like Superuser, Create DB, etc.).

---

Advanced Options & Role Attributes

When creating a user, you can add specific “powers” using attributes:

Attribute	Description
SUPERUSER	Bypasses all permission checks (use with extreme caution).
CREATEDB	Allows the user to create new databases.
CREATEROLE	Allows the user to create, alter, or drop other roles.
VALID UNTIL	Sets an expiration date for the password (e.g., '2027-01-01').

Example of an Administrative User:

SQL

CREATE USER admin_bob WITH PASSWORD 'strong_pass' CREATEDB CREATEROLE;

---

Pro-Tips for Better Database Security

• Use SCRAM-SHA-256: Ensure your server is configured to use SCRAM-SHA-256 for password hashing instead of the older MD5.
• Group Roles: Instead of granting SELECT to 10 different users, create one role called readonly_group, grant it permissions, and then “add” users to it: GRANT readonly_group TO sam_dev;
• Never Use Plain Text: If you are scripting user creation, avoid putting passwords in shell history. Use environment variables or .pgpass files.

---

FAQ: Frequently Asked Questions

What is the difference between CREATE USER and CREATE ROLE?

In PostgreSQL, CREATE USER is an alias for CREATE ROLE with the LOGIN attribute already included. CREATE ROLE by default creates a role that cannot log in until you explicitly add the LOGIN keyword.

How do I change a user’s password?

If you need to update a password for an existing user:

SQL

ALTER USER sam_dev WITH PASSWORD 'new_secure_password';

How do I delete a user?

To remove a user, use the DROP USER command. Note that if the user owns objects (like tables), you must reassign or drop those objects first.

SQL

DROP USER sam_dev;

Can I limit how many connections a user can make?

Yes, you can prevent a single user from hogging all server resources:

SQL

ALTER ROLE sam_dev CONNECTION LIMIT 5;