Setting Up User Authentication and Permissions in PostgreSQL

Security is a critical part of any database system. In PostgreSQL, user authentication and permissions determine who can connect to the database and what actions they are allowed to perform. Misconfigured roles or overly broad privileges can lead to data leaks, accidental data loss, or compliance violations.

In this article, you will learn how PostgreSQL handles users and roles, how authentication works, and how to configure permissions using best practices suitable for both development and production environments.

Understanding Roles in PostgreSQL

PostgreSQL uses a role-based access control (RBAC) system. A role can act as a user, a group, or both.

Key concepts:

• Roles replace the traditional concept of users and groups
• A role can own database objects
• Roles can be granted privileges to other roles

Create a role:

CREATE ROLE app_user;

Create a login role:

CREATE ROLE app_user LOGIN PASSWORD 'strong_password';

Authentication vs Authorization

These two concepts are often confused.

• Authentication: Verifies who the user is
• Authorization: Determines what the user can do

PostgreSQL handles authentication mainly through the pg_hba.conf file, while authorization is managed using privileges and roles.

PostgreSQL Authentication Methods

PostgreSQL supports multiple authentication methods.

Password-Based Authentication

• md5
• scram-sha-256 (recommended)

Example in pg_hba.conf:

host all all 0.0.0.0/0 scram-sha-256

Trust Authentication

• No password required
• Suitable only for local development

Peer Authentication

• Uses operating system user identity
• Common for local connections on Linux

Choose authentication methods carefully based on security requirements.

Managing pg_hba.conf

The pg_hba.conf file controls client authentication.

Key fields:

1. Connection type
2. Database
3. User
4. Address
5. Authentication method

After changes:

SELECT pg_reload_conf();

Incorrect configuration can lock you out, so always test carefully.

Creating Databases and Assigning Ownership

Create a database owned by a specific role:

CREATE DATABASE app_db OWNER app_user;

Ownership grants full control over the database and its objects.

Granting and Revoking Privileges

PostgreSQL permissions are granted using the GRANT command.

Grant access to a database:

GRANT CONNECT ON DATABASE app_db TO app_user;

Grant table privileges:

GRANT SELECT, INSERT, UPDATE ON TABLE orders TO app_user;

Revoke privileges:

REVOKE DELETE ON TABLE orders FROM app_user;

Always follow the principle of least privilege.

Using Roles as Groups

Roles can act as groups to simplify permission management.

Example:

CREATE ROLE read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
GRANT read_only TO app_user;

This approach makes permissions easier to manage at scale.

Default Privileges for New Objects

To ensure new tables inherit permissions:

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

Default privileges prevent permission issues when new objects are created.

Schema-Level Permissions

Schemas provide an additional security layer.

Grant schema usage:

GRANT USAGE ON SCHEMA public TO app_user;

Revoke schema creation:

REVOKE CREATE ON SCHEMA public FROM app_user;

This helps restrict unwanted object creation.

Managing Superuser and Elevated Roles

Superuser roles bypass all permission checks.

Best practices:

• Limit superuser access
• Use separate admin roles
• Avoid using superuser for applications

Create an admin role:

CREATE ROLE db_admin WITH CREATEDB CREATEROLE;

Auditing and Monitoring Access

To improve security:

• Log connection attempts
• Monitor role usage
• Review privileges periodically

Useful views:

SELECT * FROM pg_roles;
SELECT * FROM information_schema.role_table_grants;

Auditing helps detect misconfigurations and suspicious activity.

Common Security Mistakes

Avoid these common errors:

• Using superuser for applications
• Granting privileges to PUBLIC
• Weak password policies
• Ignoring role cleanup
• Not reviewing permissions regularly

Security issues often come from misconfiguration rather than software bugs.

Best Practices for PostgreSQL Authentication and Permissions

1. Use SCRAM-SHA-256 authentication
2. Apply the principle of least privilege
3. Separate application and admin roles
4. Use role groups for permission management
5. Secure pg_hba.conf
6. Review access regularly

Conclusion

Setting up user authentication and permissions in PostgreSQL is essential for building secure and reliable database systems. By understanding how roles, authentication methods, and privileges work together, you can protect sensitive data and reduce operational risks.

A well-designed permission model not only improves security but also makes database management easier and more scalable over time.