How to Create Postgres User

PostgreSQL, often referred to as Postgres, is one of the most powerful, open-source relational database systems in use today. Known for its reliability, extensibility, and strict adherence to SQL standards, Postgres is the backbone of countless enterprise applications, data analytics platforms, and web services. At the heart of its security and access control lies the concept of database users entities that define who can connect to the database, what they can do, and under what conditions. Creating a Postgres user is not merely a technical task; it is a foundational practice that ensures data integrity, enforces least-privilege access, and safeguards sensitive information from unauthorized exposure.

In this comprehensive guide, you will learn how to create a Postgres user from the ground up whether you're managing a local development environment, a staging server, or a production deployment. Well walk through the exact commands, explain the underlying mechanisms, highlight common pitfalls, and provide best practices that align with industry standards. By the end of this tutorial, youll have the confidence and knowledge to create, manage, and secure Postgres users efficiently and securely.

Step-by-Step Guide

Prerequisites: Understanding Your Environment

Before creating a Postgres user, ensure you have the following:

• PostgreSQL installed on your system (version 9.6 or higher is recommended)
• Access to a superuser account (typically postgres)
• Terminal or command-line interface (CLI) access
• Basic familiarity with SQL and shell commands

To verify your PostgreSQL installation, open your terminal and run:

psql --version

If PostgreSQL is installed correctly, youll see output similar to:

psql (PostgreSQL) 15.4

If not, install PostgreSQL using your systems package manager:

• Ubuntu/Debian: sudo apt update && sudo apt install postgresql postgresql-contrib
• CentOS/RHEL: sudo yum install postgresql-server postgresql-contrib
• macOS (Homebrew): brew install postgresql

Step 1: Access the PostgreSQL Prompt as Superuser

By default, PostgreSQL creates a superuser account named postgres during installation. This account has full administrative privileges and is required to create new users.

To switch to the postgres system user and access the PostgreSQL prompt, run:

sudo -u postgres psql

You should now see the PostgreSQL prompt:

postgres=

This prompt indicates you are connected to the default database as the superuser. You are now ready to create users.

Step 2: Create a New User with CREATE USER

The primary SQL command to create a new user in PostgreSQL is CREATE USER. Heres the basic syntax:

CREATE USER username;

For example, to create a user named webapp:

CREATE USER webapp;

By default, this creates a user with no password and no special privileges. While this is technically valid, its not recommended for real-world use. Lets enhance this with essential options.

Step 3: Assign a Password Using CREATE USER WITH PASSWORD

To enforce authentication, assign a strong password during user creation:

CREATE USER webapp WITH PASSWORD 'MyStr0ngP@ssw0rd!';

Important: Avoid using simple, easily guessable passwords. Use a password manager to generate and store complex passwords. PostgreSQL stores passwords in an encrypted format using MD5 or SCRAM-SHA-256, depending on your configuration.

For enhanced security, prefer SCRAM-SHA-256 authentication. To ensure its enabled, check your pg_hba.conf file (discussed later) and confirm the authentication method is set to scram-sha-256.

Step 4: Grant Login Privileges

By default, users created with CREATE USER have login privileges. However, if you used CREATE ROLE (a more flexible alternative), you may need to explicitly allow login:

CREATE USER webapp WITH LOGIN PASSWORD 'MyStr0ngP@ssw0rd!';

The LOGIN attribute grants the user the ability to establish a connection to the database. Without it, the user can exist as a role but cannot authenticate.

Step 5: Create a User with No Login (Role for Permissions)

Sometimes, you need a user that cannot log in but can own database objects or be granted to other users. This is useful for permission delegation. For example:

CREATE ROLE read_only;

This creates a role named read_only that cannot log in. You can later grant this role to multiple users to centralize permissions management.

Step 6: Create a User with Specific Privileges

You can assign additional attributes during user creation:

• CREATEDB allows the user to create databases
• CREATEROLE allows the user to create and manage other roles
• NOSUPERUSER explicitly denies superuser privileges (default)
• SUPERUSER grants full administrative rights (use sparingly)

Example: Create a user who can create databases but is not a superuser:

CREATE USER data_analyst WITH LOGIN PASSWORD 'Analyst123!' CREATEDB;

Example: Create a superuser for administrative tasks (use only in trusted environments):

CREATE USER admin WITH LOGIN PASSWORD 'AdminPass456!' SUPERUSER;

?? Caution: Superuser privileges bypass all access controls. Never assign this to application accounts or non-administrative personnel.

Step 7: Verify the User Was Created

To confirm your user was created successfully, list all users in the system:

\du

This command displays all roles and their attributes. Output might look like:

                                   List of roles
Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
admin     | Superuser, Create DB                                       | {}
data_analyst | Create DB                                                | {}
postgres  | Superuser, Create DB, Create role, Replication, Bypass RLS | {}
webapp    |                                                            | {}

Each row shows the role name, attributes, and group memberships. Ensure your new user appears with the correct permissions.

Step 8: Grant Database Access

Creating a user does not automatically grant access to any database. You must explicitly grant permissions.

First, connect to the target database. If none exists, create one:

CREATE DATABASE myapp;

Then connect to it:

\c myapp

Now grant the user access to the database:

GRANT CONNECT ON DATABASE myapp TO webapp;

By default, users cannot read or write to tables. To allow basic read access to all tables in the public schema:

GRANT USAGE ON SCHEMA public TO webapp;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO webapp;

To allow write access:

GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO webapp;

To ensure future tables automatically inherit these permissions, use:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO webapp;

Step 9: Test the New User Connection

Exit the current session:

\q

Now test connecting as the new user:

psql -U webapp -d myapp -h localhost

Youll be prompted for the password. Enter it. If successful, youll see:

psql (15.4)
Type "help" for help.
myapp=>

If you receive an authentication error, check:

• That the password is correct
• That pg_hba.conf allows the connection type (local, host, etc.)
• That the user has LOGIN privilege

Step 10: Configure pg_hba.conf for Remote Access (Optional)

By default, PostgreSQL only allows local connections via Unix sockets. To allow remote connections (e.g., from an application server), you must modify the pg_hba.conf file.

Locate the file:

• Ubuntu/Debian: /etc/postgresql/15/main/pg_hba.conf
• CentOS/RHEL: /var/lib/pgsql/15/data/pg_hba.conf
• macOS (Homebrew): /usr/local/var/postgres/pg_hba.conf

Open the file with a text editor (requires sudo):

sudo nano /etc/postgresql/15/main/pg_hba.conf

Add a line to allow connections from a specific IP or network:

TYPE  DATABASE        USER            ADDRESS                 METHOD
host    myapp           webapp          192.168.1.10/32         scram-sha-256

This allows the user webapp to connect to the myapp database from IP 192.168.1.10 using SCRAM-SHA-256 authentication.

After editing, reload PostgreSQL to apply changes:

sudo systemctl reload postgresql

Restart if needed:

sudo systemctl restart postgresql

Step 11: Remove or Modify a User (Optional)

To delete a user:

DROP USER webapp;

To alter a users password:

ALTER USER webapp WITH PASSWORD 'NewSecurePass789!';

To revoke privileges:

REVOKE CONNECT ON DATABASE myapp FROM webapp;

Always ensure no active sessions are using the user before dropping them.

Best Practices

Use the Principle of Least Privilege

Never grant superuser or createdb privileges to application users. Only give users the minimum permissions required to perform their function. For example, a web application should only have read/write access to specific tables, not the ability to drop databases or create roles.

Use Roles for Permission Grouping

Instead of assigning permissions directly to individual users, create roles like read_only, data_writer, or reporting_user. Then assign users to these roles. This simplifies management and reduces redundancy.

CREATE ROLE data_writer;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO data_writer;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, UPDATE, DELETE ON TABLES TO data_writer;
CREATE USER john WITH LOGIN PASSWORD 'johnpass123!';
GRANT data_writer TO john;

Enforce Strong Password Policies

PostgreSQL does not enforce password complexity by default. Use external tools or application-level validation to ensure passwords are:

• At least 12 characters long
• Include uppercase, lowercase, numbers, and symbols
• Never reused across systems
• Stored in a secure secrets manager (e.g., HashiCorp Vault, AWS Secrets Manager)

Prefer SCRAM-SHA-256 Over MD5

MD5 is deprecated and vulnerable to rainbow table attacks. Ensure your pg_hba.conf uses scram-sha-256 for all connections. You can check the current setting with:

SHOW password_encryption;

Set it in postgresql.conf if needed:

password_encryption = scram-sha-256

Disable Remote Superuser Access

In pg_hba.conf, never allow superusers to connect remotely. Lines like:

host    all             postgres        0.0.0.0/0               md5

Should be removed or restricted to localhost only:

host    all             postgres        127.0.0.1/32            scram-sha-256

Use Connection Pooling for Applications

Application servers should use connection pools (e.g., PgBouncer, pgpool-II) to reduce the number of concurrent user connections. This improves performance and prevents hitting connection limits.

Enable Logging and Monitoring

Enable PostgreSQL logging to track user activity:

log_connections = on
log_disconnections = on
log_statement = 'all'

Use tools like pgAdmin, Prometheus + Grafana, or Datadog to monitor login attempts, query patterns, and resource usage.

Regularly Audit User Accounts

Quarterly, review all users and roles:

• Remove inactive users
• Revoke unused permissions
• Confirm roles are still necessary

Run this query to find users with no login:

SELECT rolname FROM pg_roles WHERE rolcanlogin = false;

Use SSL for Remote Connections

If users connect over the internet, enforce SSL. Generate or obtain an SSL certificate and configure:

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

In pg_hba.conf, use hostssl instead of host to require encrypted connections:

hostssl myapp webapp 192.168.1.0/24 scram-sha-256

Tools and Resources

Command-Line Tools

• psql The default PostgreSQL interactive terminal. Essential for user management.
• pg_dump and pg_restore Useful for exporting/importing user permissions as part of database backups.
• pg_isready Checks if the PostgreSQL server is accepting connections before attempting user login tests.

Graphical User Interfaces

• pgAdmin The most popular open-source GUI for PostgreSQL. Allows user creation via point-and-click interface under Login/Group Roles.
• DBeaver A universal database tool that supports PostgreSQL and provides a visual role and permission editor.
• TablePlus A modern, native macOS and Windows client with clean UI for managing users and schemas.

Configuration Files

• postgresql.conf Main server configuration file. Controls authentication methods, logging, and SSL.
• pg_hba.conf Host-Based Authentication file. Defines which users can connect from which IPs and with which methods.
• pg_ident.conf Maps operating system users to PostgreSQL roles (used for peer authentication).

Security Auditing Tools

• pgAudit An extension that provides detailed session and object audit logging. Install with CREATE EXTENSION pgaudit;
• PostgreSQL Security Scanner Open-source tools like AWS RDS PostgreSQL Security Checklist help validate configurations.
• OWASP Database Security Checklist A comprehensive guide to securing databases, including PostgreSQL-specific recommendations.

Documentation and Learning Resources

• Official CREATE USER Documentation
• pg_hba.conf Guide
• Role and Permission Management
• PostgreSQL: Up and Running by Regina Obe and Leo Hsu Excellent book covering user management and security.

Real Examples

Example 1: E-commerce Application User

Youre deploying an online store using PostgreSQL. The application needs to read product data and write orders, but must not access customer payment details.

Steps:

1. Create a dedicated database: CREATE DATABASE ecommerce;
2. Create a user: CREATE USER ecommerce_app WITH LOGIN PASSWORD 'eC0mmerc3App!2024';
3. Grant connection: GRANT CONNECT ON DATABASE ecommerce TO ecommerce_app;
4. Grant access to products and orders tables only:

\c ecommerce
GRANT USAGE ON SCHEMA public TO ecommerce_app;
GRANT SELECT ON products, categories TO ecommerce_app;
GRANT INSERT, UPDATE ON orders, order_items TO ecommerce_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO ecommerce_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, UPDATE ON TABLES TO ecommerce_app;

Do not grant access to the customers or payments tables. This enforces data segregation.

Example 2: Data Analyst with Read-Only Access

A data analyst needs to run reports on sales data but must not modify any tables.

Steps:

1. Create a read-only role: CREATE ROLE analyst_read;
2. Grant SELECT on all relevant tables: GRANT SELECT ON sales, customers, regions TO analyst_read;
3. Create the user: CREATE USER alice WITH LOGIN PASSWORD 'Alice456!';
4. Assign the role: GRANT analyst_read TO alice;

Now Alice can connect and query, but cannot insert, update, or delete. If new tables are added, you can extend the roles permissions centrally.

Example 3: Multi-Tenant SaaS Application

Youre building a SaaS platform where each customer has a separate schema in one database.

Strategy:

• One superuser for administration
• One role per tenant: tenant_123
• Each tenants user is granted access only to their schema

CREATE ROLE tenant_123;
CREATE SCHEMA tenant_123 AUTHORIZATION tenant_123;
CREATE USER tenant123_user WITH LOGIN PASSWORD 'T123!Pass';
GRANT tenant_123 TO tenant123_user;
GRANT USAGE ON SCHEMA tenant_123 TO tenant123_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA tenant_123 TO tenant_123;

This ensures tenant isolation and prevents cross-tenant data leakage.

Example 4: Automated Backup User

You need a user for automated nightly backups using pg_dump.

Steps:

1. Create user: CREATE USER backup_user WITH LOGIN PASSWORD 'Backup!2024' NOCREATEDB NOCREATEROLE;
2. Grant read access to all databases: GRANT CONNECT ON DATABASE myapp TO backup_user;
3. Grant SELECT on all tables (use a script or loop):

DO $$
DECLARE
r RECORD;
BEGIN
FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP
EXECUTE 'GRANT SELECT ON ' || quote_ident(r.tablename) || ' TO backup_user';
END LOOP;
END $$;

Use this user in your cron job for backups:

pg_dump -U backup_user -h localhost myapp > backup.sql

FAQs

Can I create a PostgreSQL user without a password?

Yes, but its strongly discouraged. A user without a password can only connect using peer authentication (on Unix systems) or via SSL certificates. For security and portability, always assign a strong password.

Whats the difference between CREATE USER and CREATE ROLE?

CREATE USER is equivalent to CREATE ROLE ... LOGIN. CREATE ROLE creates a role that cannot log in by default. Use CREATE ROLE for permission groups; use CREATE USER for accounts that need to connect.

Why cant my new user connect even after creating them?

Most common reasons:

• Missing LOGIN privilege
• Incorrect password
• pg_hba.conf doesnt allow the connection type (e.g., local vs. host)
• Database doesnt exist or user lacks CONNECT privilege
• Firewall blocking port 5432

How do I reset a forgotten password?

If you have superuser access, use:

ALTER USER username WITH PASSWORD 'newpassword';

If youve lost superuser access, you may need to restart PostgreSQL in single-user mode and reset the password manually a complex process best avoided with proper password management.

Can I create a user that can only access one table?

Yes. Grant permissions on a specific table:

GRANT SELECT, INSERT ON mytable TO myuser;

Combine with REVOKE on other tables to restrict access further.

Is it safe to use the same user for multiple applications?

No. Each application should have its own dedicated user. This enables fine-grained auditing, limits blast radius in case of compromise, and simplifies permission revocation.

How do I see what permissions a user has?

Use:

\dp

to list table permissions, or query the system catalogs:

SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'mytable' AND grantee = 'myuser';

Can I create a user with time-based access restrictions?

PostgreSQL does not natively support time-based login restrictions. You must implement this at the application layer or use external tools like PAM authentication with time rules.

What happens if I delete a user who owns database objects?

PostgreSQL will refuse to drop a user who owns objects. You must first reassign ownership:

REASSIGN OWNED BY olduser TO newuser;
DROP USER olduser;

Conclusion

Creating a Postgres user is a critical skill for any database administrator, developer, or DevOps engineer working with PostgreSQL. Its not just about executing a command its about understanding access control, security posture, and system architecture. A poorly configured user account can lead to data breaches, compliance violations, or system downtime. Conversely, a well-managed user strategy enhances security, simplifies audits, and improves operational efficiency.

In this guide, youve learned how to create users with precise permissions, assign passwords securely, configure network access, and apply industry best practices. Youve seen real-world examples of how users are structured in applications, analytics, and SaaS platforms. You now know how to audit, monitor, and maintain user accounts over time.

Remember: users are not just technical entities they are access points. Treat each one with the same rigor youd apply to a server, a firewall rule, or a production deployment. Always follow the principle of least privilege. Always use strong passwords. Always log and monitor. Always review and revoke.

PostgreSQLs user management system is powerful and flexible. With the knowledge youve gained here, youre now equipped to leverage it securely and effectively whether youre managing a single database or a global fleet of instances.