How to Create Postgres User

PostgreSQL, often referred to as Postgres, is one of the most powerful, open-source relational database systems in the world. Known for its reliability, extensibility, and strict adherence to SQL standards, Postgres is the backbone of countless enterprise applications, web services, and data-intensive platforms. At the heart of securing and managing access to a Postgres database is the concept of users database roles that define who can connect, what they can read or modify, and how they interact with schemas, tables, and functions.

Creating a Postgres user is not merely a technical step it is a foundational act of database governance. Without properly configured users, databases are vulnerable to unauthorized access, data breaches, and operational chaos. Whether you're setting up a new application, migrating data, or scaling infrastructure, understanding how to create and manage Postgres users with precision is essential for any developer, DevOps engineer, or database administrator.

This comprehensive guide walks you through every aspect of creating a Postgres user from basic commands to advanced configurations, best practices, real-world examples, and troubleshooting. By the end, youll have the knowledge to confidently create, assign permissions, and maintain secure database users in any Postgres environment.

Step-by-Step Guide

Prerequisites

Before creating a Postgres user, ensure the following prerequisites are met:

• PostgreSQL is installed on your system. Verify this by running psql --version in your terminal.
• You have access to a superuser account (typically postgres) or another user with sufficient privileges to create roles.
• You are connected to the Postgres server either locally or remotely via a secure connection.

If PostgreSQL is not installed, download and install it from the official website (postgresql.org/download) or use your systems package manager (e.g., apt on Ubuntu, brew on macOS).

Step 1: Access the Postgres Command Line

To begin creating users, you must first access the Postgres interactive terminal, psql. This is typically done by switching to the default superuser account, postgres, and launching the client.

On Linux or macOS, open your terminal and run:

sudo -u postgres psql

This command switches to the postgres system user and starts the Postgres SQL shell. You should see a prompt like:

postgres=

If youre connecting remotely or using a different user, use:

psql -h hostname -U username -d database_name

Replace hostname, username, and database_name with your actual values. Youll be prompted for a password if authentication is enabled.

Step 2: List Existing Users (Roles)

Before creating a new user, its good practice to check what roles already exist. In Postgres, users are implemented as roles with login capability. To list all existing roles, run:

\du

This command displays a table showing role names, attributes (like superuser, create DB, etc.), and member roles. Look for existing users to avoid duplication or naming conflicts.

Step 3: Create a New User with CREATE USER

Postgres provides the CREATE USER command to define a new login role. The simplest form is:

CREATE USER username;

Replace username with your desired name for example:

CREATE USER app_user;

This creates a user with no password and no special privileges. While functional, this is not secure for production environments. Most real-world applications require a password and specific permissions.

Step 4: Assign a Password

To create a user with a password, use the WITH PASSWORD clause:

CREATE USER app_user WITH PASSWORD 'secure_password_123';

Always use strong, complex passwords. Avoid dictionary words, personal information, or common patterns. Consider using a password manager or generating a cryptographically secure random string.

Step 5: Grant Login and Connection Privileges

By default, a user created with CREATE USER has the LOGIN attribute enabled. You can explicitly specify this if needed:

CREATE USER app_user WITH LOGIN PASSWORD 'secure_password_123';

However, if you accidentally create a user without login privileges (e.g., using CREATE ROLE), you can grant it later:

ALTER USER app_user WITH LOGIN;

Also ensure the user can connect to the desired database. By default, users cannot connect to databases unless explicitly granted access. Use:

GRANT CONNECT ON DATABASE myapp_db TO app_user;

Replace myapp_db with the name of your target database.

Step 6: Grant Schema and Table Permissions

Connecting to a database doesnt grant access to its contents. You must explicitly grant permissions on schemas and tables.

To allow a user to use a schema (e.g., public), run:

GRANT USAGE ON SCHEMA public TO app_user;

To allow reading from tables:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_user;

To allow writing (insert, update, delete):

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

To grant these permissions on future tables automatically:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

This ensures that any new tables created in the public schema by other users will automatically grant the specified privileges to app_user.

Step 7: Create a User with Specific Attributes

Postgres allows granular control over user attributes. You can create users with specific capabilities during creation:

CREATE USER app_user WITH
LOGIN
PASSWORD 'secure_password_123'
CREATEDB
CREATEROLE
NOSUPERUSER
CONNECTION LIMIT 10;

Heres what each attribute means:

• LOGIN Allows the role to log in (equivalent to a user).
• CREATEDB Allows the user to create new databases.
• CREATEROLE Allows the user to create and manage other roles.
• NOSUPERUSER Restricts the user from bypassing permission checks (recommended for security).
• CONNECTION LIMIT 10 Limits the number of concurrent connections this user can open.

Use these attributes judiciously. For most application users, LOGIN, NOSUPERUSER, and a CONNECTION LIMIT are sufficient. Avoid CREATEDB and CREATEROLE unless absolutely necessary.

Step 8: Verify the User Was Created

After creation, verify the user exists and has the correct permissions:

\du app_user

This shows detailed information about the user, including attributes and group memberships.

To test login access, exit the current session (\q) and reconnect using the new user:

psql -U app_user -d myapp_db

If prompted, enter the password. If login succeeds, the user is configured correctly.

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

If your application connects to Postgres remotely, you must configure the client authentication file: pg_hba.conf.

Locate the file typically found at:

• Linux: /etc/postgresql/[version]/main/pg_hba.conf
• macOS (Homebrew): /usr/local/var/postgres/pg_hba.conf

Open the file and add a line to allow the user to connect from a specific IP or network:

host    myapp_db    app_user    192.168.1.0/24    md5

This line means:

• host TCP/IP connection
• myapp_db target database
• app_user target user
• 192.168.1.0/24 allowed IP range
• md5 password authentication (recommended over trust)

After editing, reload the configuration:

sudo systemctl reload postgresql

or

pg_ctl reload

Test the connection from a remote machine to ensure it works.

Step 10: Secure the User with SSL (Advanced)

For production environments, always require SSL connections. Edit postgresql.conf and set:

ssl = on

Then, in pg_hba.conf, change the authentication method to hostssl instead of host:

hostssl myapp_db app_user 192.168.1.0/24 md5

This ensures all connections are encrypted. Place valid SSL certificates in the Postgres data directory and restart the server:

sudo systemctl restart postgresql

Best Practices

1. Use the Principle of Least Privilege

Never grant superuser privileges to application users. Even if the application needs to create tables dynamically, use a separate superuser for migrations and restrict the runtime user to only the permissions it requires. A user with only SELECT on read-only tables should never have INSERT or DELETE rights.

2. Avoid the Default postgres User for Applications

The postgres superuser is meant for administrative tasks only. Never configure your web app, API, or backend service to connect using this account. Doing so exposes your entire database to catastrophic risk if the application is compromised.

3. Use Strong, Unique Passwords

Use password managers or tools like openssl rand -base64 32 to generate cryptographically secure passwords. Avoid reusing passwords across systems. Rotate passwords periodically using:

ALTER USER app_user WITH PASSWORD 'new_secure_password';

4. Implement Role-Based Access Control (RBAC)

Instead of assigning permissions directly to users, create roles that represent functional groups (e.g., read_only, data_writer, schema_admin), then assign users to those roles.

CREATE ROLE read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO read_only;
CREATE USER app_user WITH LOGIN PASSWORD '...';
GRANT read_only TO app_user;

This simplifies permission management. If you need to change permissions for 10 users, you modify one role instead of 10 individual users.

5. Limit Concurrent Connections

Use the CONNECTION LIMIT attribute to prevent connection exhaustion. For example:

CREATE USER api_user WITH LOGIN PASSWORD '...' CONNECTION LIMIT 5;

This protects your database from misbehaving applications or DDoS-style attacks that open too many connections.

6. Audit User Activity

Enable logging to monitor who is connecting and what queries are being executed. In postgresql.conf, set:

log_connections = on
log_disconnections = on
log_statement = 'all'

Review logs regularly for unusual access patterns or failed login attempts.

7. Disable Password Authentication for Local Trust (If Possible)

On internal systems, consider using peer authentication for local connections. In pg_hba.conf:

local   all             all                                     peer

This allows system users to connect as the matching database user without a password useful for scripts or cron jobs running under a dedicated system account.

8. Regularly Review and Revoke Unused Accounts

Remove users who no longer need access. Unused accounts are security liabilities. To delete a user:

DROP USER app_user;

Before dropping, ensure no objects are owned by the user. Use:

SELECT * FROM pg_roles WHERE rolname = 'app_user';

If objects exist, reassign ownership first:

REASSIGN OWNED BY app_user TO postgres;
DROP USER app_user;

9. Use Environment Variables for Credentials

Never hardcode database credentials in application source code. Use environment variables:

DATABASE_URL=postgresql://app_user:secure_password_123@localhost:5432/myapp_db

Most frameworks (Django, Rails, Node.js, etc.) support this pattern. Store these variables in secure configuration files or secret managers.

10. Integrate with External Identity Providers (Advanced)

For enterprise environments, integrate Postgres with LDAP, Kerberos, or OAuth2 using external authentication modules. This centralizes user management and enforces corporate policies.

Tools and Resources

Command-Line Tools

• psql The standard interactive terminal for Postgres. Essential for user management.
• pgAdmin A popular GUI tool with a visual interface for creating and managing users, roles, and permissions.
• pg_ctl Used to start, stop, and reload the Postgres server. Required when modifying pg_hba.conf or postgresql.conf.
• pg_dump and pg_restore Useful for exporting and importing user roles and permissions during migrations.

Configuration Files

• pg_hba.conf Client authentication configuration. Controls which users can connect from which IPs and with which authentication methods.
• postgresql.conf Server-wide settings. Includes SSL, logging, connection limits, and memory allocation.
• pg_ident.conf Maps system users to database roles when using peer or ident authentication.

Online Resources

• PostgreSQL CREATE ROLE Documentation Official reference for role creation syntax.
• pg_hba.conf Guide Detailed explanation of client authentication methods.
• pg_roles System Catalog Queryable metadata about all roles in the system.
• pgAdmin Official Site Download and documentation for the GUI tool.

Automation and Infrastructure-as-Code

For scalable deployments, automate user creation using:

• Ansible Use the postgresql_user module to create users declaratively.
• Terraform With the postgresql provider, manage users as part of infrastructure.
• Docker Compose Initialize users using startup scripts in a docker-entrypoint-initdb.d folder.

Example Docker initialization script (init-user.sql):

CREATE USER app_user WITH LOGIN PASSWORD 'secure_password_123';
GRANT CONNECT ON DATABASE myapp TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

Mount this file into your container:

volumes:
- ./init-user.sql:/docker-entrypoint-initdb.d/init-user.sql

Real Examples

Example 1: Web Application User

Youre deploying a Python Flask app that connects to a Postgres database named blog_db. You need a user named blog_app with read/write access to all tables in the public schema, limited to 20 connections.

Steps:

1. Connect as superuser: sudo -u postgres psql
2. Create the user: CREATE USER blog_app WITH LOGIN PASSWORD 'fJ8

   kL2$pQ9!' CONNECTION LIMIT 20;

3. Grant database access: GRANT CONNECT ON DATABASE blog_db TO blog_app;
4. Grant schema usage: GRANT USAGE ON SCHEMA public TO blog_app;
5. Grant table permissions: GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO blog_app;
6. Set default privileges: ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO blog_app;
7. Verify: \du blog_app

Configure your Flask apps SQLALCHEMY_DATABASE_URI to use:

postgresql://blog_app:fJ8
kL2$pQ9!@localhost:5432/blog_db

Example 2: Read-Only Analytics User

You want to allow a data analyst to query your production database without risking accidental data modification.

Steps:

1. Create a role: CREATE ROLE analytics_readonly;
2. Grant select access: GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics_readonly;
3. Set defaults: ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analytics_readonly;
4. Create user: CREATE USER analyst_jane WITH LOGIN PASSWORD 'secure_analyst_pass' CONNECTION LIMIT 5;
5. Assign role: GRANT analytics_readonly TO analyst_jane;

Now analyst_jane can run queries but cannot insert, update, or delete data.

Example 3: Migration User with Elevated Privileges

You need a user to run database migrations (e.g., with Alembic or Rails migrations) that create tables, indexes, and functions.

Steps:

1. Create user: CREATE USER migrate_user WITH LOGIN PASSWORD 'mig_2024!Xz' CREATEDB CREATEROLE;
2. Grant database access: GRANT CONNECT ON DATABASE myapp TO migrate_user;
3. Grant schema usage: GRANT USAGE ON SCHEMA public TO migrate_user;
4. Grant all privileges on future objects: ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO migrate_user;
5. Restrict connection limit: ALTER USER migrate_user CONNECTION LIMIT 3;

Use this user only during deployment. Never use it in runtime application code.

Example 4: Secure Remote Access with SSL

Youre hosting Postgres on AWS RDS and connecting from an EC2 instance. You want to ensure encrypted communication.

Steps:

1. In RDS Console, ensure SSL is enabled for the instance.
2. Create user: CREATE USER webapp WITH LOGIN PASSWORD 'rds_2024!Pw' CONNECTION LIMIT 15;
3. Grant permissions as needed.
4. In your app, configure the connection string to use SSL mode:

postgresql://webapp:rds_2024!Pw@your-db.rds.amazonaws.com:5432/myapp?sslmode=require

Test with:

psql "postgresql://webapp:rds_2024!Pw@your-db.rds.amazonaws.com:5432/myapp?sslmode=require"

FAQs

Can I create a Postgres user without a password?

Yes, but it is strongly discouraged for any environment outside of local development. A passwordless user with LOGIN can be created using CREATE USER username;. However, this user can only log in via peer or ident authentication (i.e., from the same system user), which limits its usefulness and security.

Whats the difference between CREATE USER and CREATE ROLE?

CREATE USER is equivalent to CREATE ROLE ... WITH LOGIN. Both create roles, but CREATE USER automatically enables the LOGIN attribute. Use CREATE ROLE when you want to create a group role without login capability (e.g., for RBAC).

Why cant my new user connect to the database?

Common reasons include:

• The user lacks CONNECT privilege on the database.
• pg_hba.conf does not allow connections from the clients IP or authentication method is misconfigured (e.g., trust instead of md5).
• SSL is required but not configured on the client.
• The database name is incorrect or does not exist.

Check logs in pg_log for connection rejection messages.

How do I change a users password?

Use the ALTER USER command:

ALTER USER username WITH PASSWORD 'new_password';

Ensure the new password meets your organizations complexity requirements.

Can I delete a user who owns database objects?

No. You must first reassign ownership of all objects to another user or drop them. Use:

REASSIGN OWNED BY old_user TO new_user;
DROP USER old_user;

Is it safe to use the postgres user in Docker containers?

No. Even in containers, avoid using the superuser for application connections. Create a dedicated user in your Docker initialization script. This follows security best practices regardless of deployment environment.

What authentication methods does Postgres support?

Postgres supports multiple methods:

• trust No password required (insecure, only for local dev).
• peer Matches system username to database username (Linux/macOS local).
• md5 Password hashed with MD5 (widely supported).
• scram-sha-256 Modern, secure password hashing (recommended for new deployments).
• cert SSL certificate authentication.
• ldap, kerberos Enterprise authentication systems.

Use scram-sha-256 when possible. Its more secure than MD5 and is the default in Postgres 10+.

How do I see what permissions a user has?

Use:

\dp table_name

to see permissions on a specific table, or query the system catalogs:

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

Conclusion

Creating a Postgres user is a fundamental yet critical task that directly impacts the security, scalability, and maintainability of your database infrastructure. From the simple CREATE USER command to advanced role-based access control and SSL encryption, every step in this process serves a purpose beyond mere functionality it enforces boundaries, protects data, and upholds operational integrity.

By following the step-by-step guide, adhering to best practices, leveraging the right tools, and learning from real-world examples, you transform from a user who merely creates accounts into a database steward who understands the weight of access control. Whether youre managing a single development database or a distributed, high-availability cluster, the principles remain the same: minimize privilege, maximize auditability, and never underestimate the value of a strong password.

As you continue working with Postgres, remember that user management is not a one-time setup. Its an ongoing discipline. Regularly review roles, rotate credentials, audit logs, and update configurations as your application evolves. The security of your data begins with the first user you create make sure its done right.