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. Renowned for its reliability, extensibility, and strict adherence to SQL standards, Postgres powers everything from small startups to enterprise-scale applications. At the heart of its security and access control lies the concept of database users also known as roles. Creating a Postgres user is not merely a technical step; its a foundational practice that ensures data integrity, enforces least-privilege access, and safeguards sensitive information from unauthorized exposure.

In this comprehensive guide, youll learn exactly how to create a Postgres user, from initial setup to advanced configurations. Whether youre a developer setting up a local environment, a DevOps engineer managing production databases, or a database administrator optimizing access policies, this tutorial provides clear, actionable steps backed by industry best practices. By the end, youll not only know how to create a user youll understand why each configuration matters and how to avoid common pitfalls that compromise security or performance.

Step-by-Step Guide

Prerequisites

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

• PostgreSQL is installed on your system. You can verify this by running psql --version in your terminal.
• You have access to a superuser account (typically postgres) with administrative privileges.
• You are logged into the system where PostgreSQL is running either locally or via SSH for remote servers.
• You understand the difference between operating system users and PostgreSQL roles. These are distinct entities, though they can share names.

If PostgreSQL is not installed, download and install it from the official website (postgresql.org/download) or use your systems package manager:

sudo apt install postgresql postgresql-contrib  
Ubuntu/Debian
brew install postgresql                        
macOS

Step 1: Access the PostgreSQL Prompt

To create a user, you must first connect to the PostgreSQL server. The default superuser account is usually named postgres. To access the PostgreSQL interactive terminal (psql), run:

sudo -u postgres psql

This command switches to the system user postgres and launches the PostgreSQL client. You should see a prompt like:

postgres=

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

psql -U username -h hostname -d database_name

Replace username, hostname, and database_name with appropriate values. Youll be prompted for the password if authentication is enabled.

Step 2: Create a New User (Role)

In PostgreSQL, users are implemented as roles. The CREATE ROLE command is used to define new roles with specific attributes. To create a basic user named myapp_user, run:

CREATE ROLE myapp_user;

This creates a role with default settings no login privileges, no password, and no superuser rights. Most often, youll want to grant login access:

CREATE ROLE myapp_user WITH LOGIN;

Now the role can authenticate and connect to the database. However, without a password, it cannot log in remotely or securely. To assign a password:

CREATE ROLE myapp_user WITH LOGIN PASSWORD 'secure_password_123';

Always use strong, unique passwords. Avoid dictionary words, and consider using a password manager to generate and store credentials securely.

Step 3: Grant Database Access

By default, a newly created role has no access to any database. You must explicitly grant permissions. First, connect to the target database:

\c mydatabase

Then grant usage on the schema and necessary privileges:

GRANT USAGE ON SCHEMA public TO myapp_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myapp_user;

These commands allow the user to read, write, and modify data in all existing tables within the public schema. If youre using custom schemas, replace public with the schema name.

To ensure future tables automatically inherit these permissions, use:

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

Step 4: Assign Role to a Database (Optional)

If you want the user to be able to connect to a specific database by default, grant connect privileges:

GRANT CONNECT ON DATABASE mydatabase TO myapp_user;

This is essential for applications that connect to Postgres using a connection string that specifies a database name. Without this, the user will receive a permission denied error when attempting to connect.

Step 5: Verify the User Was Created

To confirm the user exists and has the correct privileges, run:

\du

This lists all roles and their attributes. Look for myapp_user and verify that Login is enabled and the Member of field reflects any group roles.

To check database-specific privileges:

\dn+ public
\dt+
\dp

The \dp command shows access privileges for tables, views, and sequences confirming whether myapp_user has the intended read/write permissions.

Step 6: Test the Connection

Exit the current psql session with \q, then test the new users connection:

psql -U myapp_user -d mydatabase -h localhost

Youll be prompted for the password. If authentication succeeds and you land in the psql prompt, the user is configured correctly.

If you receive an error like FATAL: password authentication failed, check:

• The password was entered correctly.
• The pg_hba.conf file allows password authentication for the users connection type (local, host, etc.).
• The user has LOGIN privilege.

Step 7: Configure pg_hba.conf for Authentication

PostgreSQL uses the pg_hba.conf file (host-based authentication) to control how users connect. This file is typically located in the data directory find it using:

SHOW hba_file;

Open the file in a text editor (requires superuser privileges):

sudo nano /var/lib/postgresql/data/pg_hba.conf  
Linux path example

Look for lines like:

TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

To allow password authentication for local connections from myapp_user, ensure the method is set to md5 or scram-sha-256 (recommended for newer versions):

host    mydatabase      myapp_user      127.0.0.1/32            scram-sha-256
host    mydatabase      myapp_user      ::1/128                 scram-sha-256

After editing, reload the configuration:

sudo systemctl reload postgresql

or

SELECT pg_reload_conf();

in the psql prompt. Failure to reload means changes wont take effect.

Step 8: Create a Role with Limited Privileges (Advanced)

For enhanced security, avoid granting superuser privileges. Instead, create roles with minimal necessary permissions. For example, if your application only needs to read data:

CREATE ROLE readonly_user WITH LOGIN PASSWORD 'read_only_pass_456';
GRANT CONNECT ON DATABASE mydatabase TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;

For write-only access (e.g., log ingestion):

CREATE ROLE writer_user WITH LOGIN PASSWORD 'write_only_pass_789';
GRANT CONNECT ON DATABASE mydatabase TO writer_user;
GRANT USAGE ON SCHEMA public TO writer_user;
GRANT INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO writer_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, UPDATE ON TABLES TO writer_user;

Never grant CREATE, DROP, or ALTER privileges to application users unless absolutely necessary.

Best Practices

Use Roles, Not Just Users

PostgreSQL treats all users as roles. This allows for flexible permission hierarchies. Instead of assigning permissions directly to individual users, create role groups (e.g., app_readers, app_writers) and assign users to them. This simplifies permission management and reduces duplication.

CREATE ROLE app_readers;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readers;
CREATE ROLE myapp_user WITH LOGIN PASSWORD '...';
GRANT app_readers TO myapp_user;

Now, if you need to change permissions for all readers, modify the app_readers role once, and all members inherit the change.

Enforce Strong Password Policies

Use long, complex passwords generated by a cryptographically secure random generator. Avoid reusing passwords across systems. PostgreSQL supports scram-sha-256, which is significantly more secure than the deprecated md5 method. Ensure your pg_hba.conf and postgresql.conf enforce this:

password_encryption = scram-sha-256

in postgresql.conf. Restart the server after changing this setting.

Apply the Principle of Least Privilege

Every user should have the minimum permissions required to perform their task. Never grant superuser access to application accounts. Even for development, use a dedicated role with restricted privileges. This reduces the blast radius in case of credential leaks or SQL injection attacks.

Separate Environments

Create distinct users for development, staging, and production environments. Never use the same credentials across environments. This prevents accidental data modification or exposure during testing.

Regularly Audit User Permissions

Run periodic audits using:

\du
\dn+
\dp

Remove unused roles and revoke unnecessary privileges. Automate this process with scripts or database monitoring tools to maintain compliance and security hygiene.

Use Connection Pooling and SSL

For production deployments, always use SSL encryption for connections. Configure PostgreSQL to require SSL by setting:

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

in postgresql.conf. Combine this with connection pooling tools like PgBouncer to reduce connection overhead and improve scalability.

Avoid Default Schemas and Public Schema Abuse

By default, new databases create a public schema. While convenient, its a security risk if not properly managed. Create dedicated schemas for applications:

CREATE SCHEMA app_schema;
GRANT USAGE ON SCHEMA app_schema TO myapp_user;

Then create all application tables within app_schema. This isolates data and simplifies permission management.

Enable Logging for Access Monitoring

In postgresql.conf, enable logging to track user activity:

log_connections = on
log_disconnections = on
log_statement = 'mod'  
Logs INSERT, UPDATE, DELETE

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

Tools and Resources

Command-Line Tools

• psql The default PostgreSQL interactive terminal. Essential for manual user management and debugging.
• pgAdmin A web-based GUI for managing PostgreSQL databases. Ideal for visual role and permission configuration.
• pg_ctl Used to start, stop, and reload the PostgreSQL server. Useful for applying configuration changes.
• pg_dump and pg_restore For exporting and importing database structures and data, including role definitions.

GUI Applications

• pgAdmin 4 The official PostgreSQL administration tool. Navigate to Login/Group Roles under the server tree to create and manage users visually.
• DBeaver A universal database tool supporting PostgreSQL and many other databases. Offers intuitive role management panels.
• DataGrip A JetBrains IDE with excellent PostgreSQL support, including schema and user management features.

Infrastructure as Code (IaC) Tools

For scalable and repeatable deployments, automate user creation using IaC tools:

• Ansible Use the postgresql_user module to create users via playbooks.
• Terraform The postgresql provider allows declarative user and role management.
• Docker Compose Use init scripts to create users when containers start.

Example Ansible task:

- name: Create PostgreSQL user
community.postgresql.postgresql_user:
name: myapp_user
password: "{{ db_password }}"
login_host: localhost
login_user: postgres
state: present
priv: "CONNECT,USAGE"

Documentation and References

• PostgreSQL CREATE ROLE Documentation
• pg_hba.conf Configuration Guide
• System Catalogs for User Information
• Managing Privileges

Security Auditing Tools

• pgAudit An extension that logs all database activity for compliance and forensic analysis.
• PostgreSQL Security Scanner Open-source tools that scan for misconfigured roles, weak passwords, or excessive privileges.
• OWASP ZAP While primarily for web apps, it can detect SQL injection vulnerabilities that exploit poor user permissions.

Real Examples

Example 1: E-commerce Application User

Scenario: Youre deploying an online store with a backend API. The application needs to read product data, insert orders, and update inventory.

Steps:

1. Create the user:

CREATE ROLE ecommerce_api WITH LOGIN PASSWORD 'EcomP@ss2024!';

2. Grant connect to the database:

GRANT CONNECT ON DATABASE store_db TO ecommerce_api;

3. Create a dedicated schema:

CREATE SCHEMA ecommerce;

4. Grant schema access and table privileges:

GRANT USAGE ON SCHEMA ecommerce TO ecommerce_api;
GRANT SELECT ON ALL TABLES IN SCHEMA ecommerce TO ecommerce_api;
GRANT INSERT, UPDATE ON orders, inventory TO ecommerce_api;
ALTER DEFAULT PRIVILEGES IN SCHEMA ecommerce GRANT SELECT ON TABLES TO ecommerce_api;
ALTER DEFAULT PRIVILEGES IN SCHEMA ecommerce GRANT INSERT, UPDATE ON TABLES TO ecommerce_api;

5. Verify and test:

\du ecommerce_api
psql -U ecommerce_api -d store_db -h localhost

Result: The API can read products, insert orders, and update inventory but cannot drop tables, create users, or access financial audit logs.

Example 2: Data Analyst Read-Only Access

Scenario: A data analyst needs to run reports on sales data but must not modify any records.

Steps:

1. Create the role:

CREATE ROLE analyst_read WITH LOGIN PASSWORD 'Analyst
Read2024';

2. Grant database access:

GRANT CONNECT ON DATABASE sales_db TO analyst_read;

3. Grant read-only access:

GRANT USAGE ON SCHEMA public TO analyst_read;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst_read;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analyst_read;

4. Optional: Create a view for simplified reporting:

CREATE VIEW sales_summary AS
SELECT customer_id, SUM(amount) AS total_spent, COUNT(*) AS orders
FROM orders
GROUP BY customer_id;
GRANT SELECT ON sales_summary TO analyst_read;

Result: The analyst can query data but cannot alter, delete, or create objects preventing accidental or malicious data changes.

Example 3: CI/CD Pipeline Service Account

Scenario: A CI/CD pipeline needs to run database migrations during deployment.

Steps:

1. Create a service role:

CREATE ROLE ci_cd_user WITH LOGIN PASSWORD 'C1CdM1gr@t10n2024!';

2. Grant access to the target database:

GRANT CONNECT ON DATABASE app_db TO ci_cd_user;

3. Grant schema and table privileges:

GRANT USAGE ON SCHEMA public TO ci_cd_user;
GRANT CREATE ON SCHEMA public TO ci_cd_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO ci_cd_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO ci_cd_user;

4. Restrict access to local network only:

In pg_hba.conf:

host    app_db          ci_cd_user      192.168.1.0/24          scram-sha-256

Result: The CI/CD system can run migrations and update tables during deployment but cannot connect from the public internet.

FAQs

Can I create a PostgreSQL user without a password?

Yes, but its strongly discouraged. A user without a password can only authenticate via peer authentication (on local Unix sockets) or certificate-based methods. For remote access or secure environments, always assign a strong password or use SSL certificates.

Whats the difference between CREATE USER and CREATE ROLE?

In PostgreSQL, CREATE USER is an alias for CREATE ROLE ... WITH LOGIN. They are functionally identical. Modern PostgreSQL documentation recommends using CREATE ROLE for consistency and clarity.

How do I reset a PostgreSQL users password?

Use the ALTER ROLE command:

ALTER ROLE myapp_user WITH PASSWORD 'new_secure_password_987';

You must be connected as a superuser to execute this.

Can I delete a PostgreSQL user?

Yes, using DROP ROLE:

DROP ROLE myapp_user;

However, if the user owns any database objects (tables, functions, etc.), you must first reassign ownership or drop those objects. Use:

REASSIGN OWNED BY myapp_user TO postgres;
DROP OWNED BY myapp_user;
DROP ROLE myapp_user;

Why cant my new user connect even after granting privileges?

Most commonly, this is due to misconfigured pg_hba.conf. Check that the authentication method (e.g., scram-sha-256) and IP range match the connection attempt. Also verify the user has LOGIN privilege and the correct database name is specified.

Do I need to restart PostgreSQL after creating a user?

No. User creation is instantaneous. However, if you modify pg_hba.conf or postgresql.conf, you must reload the configuration using pg_ctl reload or SELECT pg_reload_conf();.

How do I list all users in PostgreSQL?

Use the \du command in psql. Alternatively, query the system catalog:

SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolcanlogin
FROM pg_roles
ORDER BY rolname;

Can I create a user that can access multiple databases?

Yes. Grant CONNECT on each database individually:

GRANT CONNECT ON DATABASE db1 TO myapp_user;
GRANT CONNECT ON DATABASE db2 TO myapp_user;

Permissions are database-specific. A user must be granted access to each database separately.

Is it safe to use the default postgres user for applications?

Never. The default postgres user is a superuser with unrestricted access. Compromise of this account gives full control over the entire database server. Always create dedicated, least-privilege roles for applications.

Conclusion

Creating a Postgres user is a deceptively simple task but one that carries profound implications for security, performance, and maintainability. Whether youre setting up a local development environment or securing a mission-critical production database, the principles remain the same: grant minimal access, enforce strong authentication, and audit regularly.

In this guide, youve learned how to create users with precise permissions, configure authentication methods, leverage role hierarchies, and apply industry-standard best practices. Youve seen real-world examples that demonstrate how to tailor user access for applications, analysts, and automation systems. Youve also been equipped with tools and resources to automate and monitor user management at scale.

Remember: a database is only as secure as its weakest user. By following the steps outlined here and continuously refining your approach based on evolving threats and requirements you ensure that your PostgreSQL deployments remain resilient, compliant, and trustworthy.

As you continue to work with PostgreSQL, make user management a routine part of your deployment pipeline. Document your roles, automate their creation, and treat permissions as code. In doing so, you dont just create users you build a foundation for secure, scalable, and sustainable data systems.