PostgreSQL User Authentication and Authorization Framework: A Beginner’s Guide

/ Postgresql / 6 minutes of reading

As databases store increasingly sensitive information, understanding how to properly control who can access your data and what they can do with it becomes essential. In this guide, we’ll explore PostgreSQL’s robust authentication and authorization framework in a straightforward way that even beginners can understand.

Introduction: Why Authentication and Authorization Matter

Imagine your PostgreSQL database as a secure building. Authentication is like checking someone’s ID at the front door – it verifies who they are. Authorization is determining what rooms they’re allowed to enter once inside. Both are crucial for database security.

Without proper authentication and authorization:

  • Unauthorized users might access sensitive data
  • Legitimate users might accidentally damage data
  • You’ll have no audit trail of who did what
  • Your applications become vulnerable to attacks

Let’s break down how PostgreSQL handles both processes.

Understanding Authentication: Getting Through the Front Door

Authentication answers the question: “Are you who you claim to be?”

PostgreSQL Authentication Methods

PostgreSQL supports several authentication methods, each with different security levels:

  1. Password Authentication
    • md5: Traditional password hashing (older)
    • scram-sha-256: More secure password authentication (recommended)
  2. Trust Authentication
    • No password required (use only in controlled environments)
  3. Peer Authentication
    • Uses operating system username (local connections only)
  4. LDAP/Active Directory
    • Authenticates against enterprise directory services
  5. Certificate-based Authentication
    • Uses SSL certificates for enhanced security

The Client Authentication File (pg_hba.conf)

Authentication in PostgreSQL is controlled by a file called pg_hba.conf. Think of this as your security guard’s instruction manual – it tells PostgreSQL how to authenticate connection attempts.

Each line follows this format:

TYPE  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

For example:

# Allow local connections with password
host    all         all         127.0.0.1/32          scram-sha-256
# Allow specific IP subnet to connect as specific user with password
host    accounting  accounting  192.168.1.0/24        scram-sha-256
# Allow any user to connect to 'public_api' database from anywhere with password
host    public_api  all         0.0.0.0/0             scram-sha-256

PostgreSQL processes these rules from top to bottom, stopping at the first match. This is like your security guard checking a list of approved visitors.

Authentication in Action

Let’s see how this works:

  1. A client tries to connect with username “alice”
  2. PostgreSQL looks at the pg_hba.conf file
  3. It checks each rule from top to bottom until it finds a match
  4. It applies the authentication method specified by the matching rule
  5. If authentication succeeds, the connection is established

Setting Up User Accounts: Creating IDs

Before anyone can authenticate, you need to create user accounts in PostgreSQL.

Creating Users and Roles

In PostgreSQL, users are created as “roles” with login privileges:

-- Create a basic user
CREATE ROLE alice WITH LOGIN PASSWORD 'secure_password';

-- Create a superuser (admin)
CREATE ROLE admin WITH LOGIN PASSWORD 'very_secure_password' SUPERUSER;

Think of this like issuing ID cards to people who need access to your building.

Password Management

Always use strong passwords. You can enforce password quality with:

-- Set password expiration
ALTER ROLE alice VALID UNTIL '2023-12-31';

-- Force password change
ALTER ROLE alice PASSWORD 'new_password' VALID UNTIL 'infinity';

User Account Attributes

Users can have various attributes:

-- Create a role that can create databases but not other roles
CREATE ROLE developer WITH LOGIN PASSWORD 'dev_password' 
                      CREATEDB NOCREATEROLE;

-- Create a read-only user
CREATE ROLE reporter WITH LOGIN PASSWORD 'report_password' 
                      NOSUPERUSER NOCREATEDB NOCREATEROLE;

Authorization: Controlling What Users Can Do

Once users are authenticated, PostgreSQL needs to know what they’re allowed to do.

Role-Based Access Control

PostgreSQL uses a role-based model where privileges determine what actions a role can perform:

-- Grant table read access
GRANT SELECT ON employees TO reporter;

-- Grant all privileges on a table
GRANT ALL PRIVILEGES ON employees TO hr_admin;

-- Grant schema usage (needed to access objects in a schema)
GRANT USAGE ON SCHEMA accounting TO accounting_user;

This is like giving someone a key to specific rooms in your building.

Common Permission Types

PostgreSQL has several permission types:

  • SELECT: View data
  • INSERT: Add new data
  • UPDATE: Modify existing data
  • DELETE: Remove data
  • TRUNCATE: Empty a table
  • REFERENCES: Create foreign keys
  • TRIGGER: Create triggers
  • CREATE: Create new objects
  • CONNECT: Connect to a database
  • TEMPORARY: Create temporary tables
  • EXECUTE: Run functions and procedures

Revoking Privileges

You can also take away permissions:

-- Remove table access
REVOKE SELECT ON employees FROM reporter;

Advanced Authorization Techniques

For more sophisticated access control, PostgreSQL offers powerful features:

Row-Level Security (RLS)

Row-level security lets you control which rows a user can see or modify based on policies:

-- Enable RLS on table
ALTER TABLE customer_data ENABLE ROW LEVEL SECURITY;

-- Create policy that users can only see their own department's data
CREATE POLICY department_isolation ON customer_data
    USING (department = current_user);

This is like having doors within rooms that only certain keyholders can open.

Schema Organization

Organizing objects into schemas can simplify permission management:

-- Create schemas for different departments
CREATE SCHEMA accounting;
CREATE SCHEMA marketing;

-- Grant access to entire schema
GRANT USAGE ON SCHEMA accounting TO accounting_staff;
GRANT SELECT ON ALL TABLES IN SCHEMA accounting TO accounting_staff;

Role Inheritance and Groups

Roles can be members of other roles, inheriting their permissions:

-- Create group roles
CREATE ROLE accounting_staff;
CREATE ROLE accounting_managers;

-- Make managers inherit staff permissions
GRANT accounting_staff TO accounting_managers;

-- Assign users to roles
GRANT accounting_staff TO alice;
GRANT accounting_managers TO bob;

This simplifies permission management, like having access badges with different clearance levels.

Real-World Example: Multi-tenant Application

Let’s look at a practical example. Imagine you’re building a multi-tenant SaaS application where each client has their own data:

-- Create application schemas
CREATE SCHEMA app_common;  -- Shared functionality
CREATE SCHEMA client_a;    -- Client A's data
CREATE SCHEMA client_b;    -- Client B's data

-- Create role for application with minimal privileges
CREATE ROLE app_service WITH LOGIN PASSWORD 'app_password';

-- Create client-specific roles
CREATE ROLE client_a_user;
CREATE ROLE client_b_user;

-- Grant appropriate access
GRANT USAGE ON SCHEMA app_common TO app_service;
GRANT USAGE ON SCHEMA client_a TO client_a_user;
GRANT USAGE ON SCHEMA client_b TO client_b_user;

-- App service needs access to all schemas to operate
GRANT client_a_user TO app_service;
GRANT client_b_user TO app_service;

-- Enable RLS on client tables
ALTER TABLE client_a.data ENABLE ROW LEVEL SECURITY;
ALTER TABLE client_b.data ENABLE ROW LEVEL SECURITY;

-- Create policies
CREATE POLICY client_a_isolation ON client_a.data FOR ALL 
    USING (current_user = 'app_service' AND session_context('client_id') = 'client_a');

With this setup:

  1. The application connects as app_service
  2. It sets the session context to identify which client it’s acting for
  3. Row-level security ensures data isolation between clients

Best Practices for PostgreSQL Authentication and Authorization

  1. Least Privilege Principle: Give users only the permissions they absolutely need
  2. Use Strong Authentication: Prefer scram-sha-256 over older methods
  3. Regular Audits: Periodically review who has access to what
  4. Role Hierarchy: Use group roles to simplify management
  5. Schema Organization: Place related objects in the same schema for easier permission control
  6. Connection Encryption: Use SSL to protect connections
  7. Password Policies: Implement password rotation and complexity requirements
  8. Document Everything: Keep detailed records of your authentication and authorization setup

Conclusion

PostgreSQL’s authentication and authorization framework provides robust security when properly configured. By understanding the fundamentals of user creation, authentication methods, and permission management, you can create a secure database environment that protects your data while allowing legitimate users to do their work efficiently.

Remember, good security is like layers of an onion – multiple defenses working together to protect your valuable data. Start with the basics outlined here, and as your needs grow, explore the more advanced features that PostgreSQL offers.

Related Posts

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top