User and Permission Management in PostgreSQL: Complete Security Guide

Introduction

PostgreSQL's robust and sophisticated privilege system is one of its standout features, providing database administrators with fine-grained control over user access and permissions. Proper user and permission management in PostgreSQL is essential for maintaining data security, ensuring compliance with regulations, and protecting sensitive information from unauthorized access or modification. In an environment where data breaches can result in severe financial and reputational damage, implementing comprehensive access control is not optional—it's mandatory.

Unlike simpler database systems, PostgreSQL implements a multi-layered privilege system that operates at the cluster, database, schema, table, column, and even row level. This granular approach allows administrators to implement the principle of least privilege effectively, ensuring users can only perform operations necessary for their specific roles. Understanding this system's nuances, from basic user creation to advanced row-level security policies, is crucial for anyone responsible for PostgreSQL database administration.

This comprehensive guide explores every aspect of PostgreSQL user and permission management, covering role creation, privilege assignment, schema-based access control, row-level security implementation, and advanced features like security labels and column-level permissions. Whether you're setting up a new PostgreSQL installation or auditing an existing one, this guide provides the knowledge and practical examples needed to build and maintain a secure, well-managed database environment.

Prerequisites

Before diving into PostgreSQL user and permission management, ensure you have:

System Requirements

PostgreSQL 12+ installed (PostgreSQL 14+ recommended)
Access to a Linux server (Ubuntu 20.04+, CentOS 8+, or Debian 11+)
Sudo or root access to the operating system
PostgreSQL service running and accessible

PostgreSQL Access

Ability to connect as the postgres superuser
PostgreSQL client tools (psql) installed
Knowledge of the postgres user credentials

Knowledge Requirements

Basic SQL query syntax
Understanding of database concepts (databases, schemas, tables)
Linux command line fundamentals
Basic understanding of role-based access control

Recommended Tools

psql command-line interface
pgAdmin 4 or similar GUI tool (optional)
Text editor for configuration files

Understanding PostgreSQL Roles and Privileges

Role Concept

In PostgreSQL, the terms "user" and "role" are often used interchangeably. A role can be:

Login Role: Can connect to the database (traditional "user")
Group Role: Contains other roles (like a group)
Both: A role can have login privileges and contain other roles

Role Attributes

Roles can have various attributes:

LOGIN: Ability to connect to database
SUPERUSER: Bypass all permission checks
CREATEDB: Create databases
CREATEROLE: Create other roles
REPLICATION: Used for replication connections
BYPASSRLS: Bypass row-level security
CONNECTION LIMIT: Maximum concurrent connections

Privilege Types

Database-Level Privileges:

CONNECT: Connect to the database
CREATE: Create schemas within the database
TEMP/TEMPORARY: Create temporary tables

Schema-Level Privileges:

CREATE: Create objects within the schema
USAGE: Access objects within the schema

Table-Level Privileges:

SELECT: Read data
INSERT: Add new rows
UPDATE: Modify existing rows
DELETE: Remove rows
TRUNCATE: Empty table
REFERENCES: Create foreign key constraints
TRIGGER: Create triggers

Column-Level Privileges:

SELECT: Read specific columns
INSERT: Insert into specific columns
UPDATE: Update specific columns
REFERENCES: Reference in foreign keys

Sequence Privileges:

USAGE: Use sequence with nextval, currval
SELECT: View sequence value
UPDATE: Modify sequence value

Function/Procedure Privileges:

EXECUTE: Execute function or procedure

Step-by-Step User Management

Step 1: Connect to PostgreSQL

Connect as the postgres superuser:

sudo -u postgres psql

Or with password authentication:

psql -U postgres -h localhost

Check your current role and database:

SELECT current_user, current_database();

Step 2: View Existing Roles

List all roles in the PostgreSQL cluster:

-- List all roles
\du

-- Or using SQL
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin
FROM pg_roles
ORDER BY rolname;

-- View detailed role information
SELECT rolname,
       rolsuper AS is_superuser,
       rolcreaterole AS can_create_role,
       rolcreatedb AS can_create_db,
       rolcanlogin AS can_login,
       rolconnlimit AS connection_limit,
       rolvaliduntil AS valid_until
FROM pg_roles;

-- View role membership
SELECT r.rolname AS role,
       m.rolname AS member
FROM pg_roles r
JOIN pg_auth_members am ON r.oid = am.roleid
JOIN pg_roles m ON am.member = m.oid
ORDER BY r.rolname, m.rolname;

Step 3: Create Basic Roles

Create roles with different attributes:

-- Create basic login role (user)
CREATE ROLE app_user WITH LOGIN PASSWORD 'SecurePassword123!';

-- Create role with multiple attributes
CREATE ROLE developer WITH
    LOGIN
    PASSWORD 'SecurePassword123!'
    CREATEDB
    VALID UNTIL '2025-12-31';

-- Create role with connection limit
CREATE ROLE limited_user WITH
    LOGIN
    PASSWORD 'SecurePassword123!'
    CONNECTION LIMIT 5;

-- Create group role (no login)
CREATE ROLE developers;

-- Create read-only role
CREATE ROLE readonly WITH LOGIN PASSWORD 'SecurePassword123!';

-- Create superuser (use cautiously)
CREATE ROLE admin_user WITH
    LOGIN
    PASSWORD 'SecurePassword123!'
    SUPERUSER
    CREATEDB
    CREATEROLE;

Step 4: Modify Existing Roles

Alter role attributes:

-- Change password
ALTER ROLE app_user WITH PASSWORD 'NewSecurePassword456!';

-- Add login capability
ALTER ROLE developers WITH LOGIN;

-- Set password expiration
ALTER ROLE app_user VALID UNTIL '2025-12-31';

-- Remove password expiration
ALTER ROLE app_user VALID UNTIL 'infinity';

-- Change connection limit
ALTER ROLE app_user CONNECTION LIMIT 10;

-- Grant CREATEDB privilege
ALTER ROLE developer WITH CREATEDB;

-- Revoke CREATEDB privilege
ALTER ROLE developer WITH NOCREATEDB;

-- Rename role
ALTER ROLE old_name RENAME TO new_name;

Step 5: Grant Database-Level Privileges

Grant database access and privileges:

-- Grant connection to database
GRANT CONNECT ON DATABASE myapp_db TO app_user;

-- Grant ability to create schemas
GRANT CREATE ON DATABASE myapp_db TO developer;

-- Grant temporary table creation
GRANT TEMP ON DATABASE myapp_db TO app_user;

-- Grant all database privileges
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO admin_user;

-- Revoke connection
REVOKE CONNECT ON DATABASE myapp_db FROM app_user;

Step 6: Grant Schema-Level Privileges

Control access to schemas:

-- Connect to the database
\c myapp_db

-- Grant schema usage (required to access objects)
GRANT USAGE ON SCHEMA public TO app_user;

-- Grant ability to create objects in schema
GRANT CREATE ON SCHEMA public TO developer;

-- Grant both usage and create
GRANT USAGE, CREATE ON SCHEMA public TO developer;

-- Revoke schema access
REVOKE ALL ON SCHEMA public FROM app_user;

-- Grant access to all schemas
GRANT USAGE ON ALL SCHEMAS IN DATABASE myapp_db TO app_user;

Step 7: Grant Table-Level Privileges

Grant privileges on tables:

-- Grant SELECT on specific table
GRANT SELECT ON users TO readonly;

-- Grant multiple privileges on table
GRANT SELECT, INSERT, UPDATE ON users TO app_user;

-- Grant all privileges on table
GRANT ALL PRIVILEGES ON users TO admin_user;

-- Grant privileges on all tables in schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- Grant privileges on all tables and set as default for future tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO readonly;

-- Grant with grant option (can grant to others)
GRANT SELECT ON users TO app_user WITH GRANT OPTION;

-- Revoke privileges
REVOKE INSERT, UPDATE, DELETE ON users FROM app_user;

Step 8: Grant Column-Level Privileges

Implement column-level security:

-- Create table with sensitive columns
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    ssn VARCHAR(11)
);

-- Grant SELECT on specific columns only
GRANT SELECT (id, name, email, department) ON employees TO app_user;

-- Grant UPDATE on specific columns
GRANT UPDATE (email, department) ON employees TO app_user;

-- No access to salary and ssn columns for app_user

-- Verify column privileges
SELECT grantee, privilege_type, column_name
FROM information_schema.column_privileges
WHERE table_name = 'employees';

Step 9: Implement Role Hierarchy

Create role hierarchies for easier management:

-- Create group roles
CREATE ROLE app_readers;
CREATE ROLE app_writers;
CREATE ROLE app_admins;

-- Grant privileges to group roles
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readers;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_writers;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_admins;

-- Create user roles and add to groups
CREATE ROLE john WITH LOGIN PASSWORD 'SecurePassword123!';
CREATE ROLE jane WITH LOGIN PASSWORD 'SecurePassword123!';
CREATE ROLE admin WITH LOGIN PASSWORD 'SecurePassword123!';

-- Grant group membership
GRANT app_readers TO john;
GRANT app_writers TO jane;
GRANT app_admins TO admin;

-- View role memberships
\du john

Step 10: Set Default Privileges

Configure default privileges for future objects:

-- Set default privileges for tables created by developer role
ALTER DEFAULT PRIVILEGES FOR ROLE developer IN SCHEMA public
    GRANT SELECT ON TABLES TO readonly;

-- Set default privileges for sequences
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT USAGE, SELECT ON SEQUENCES TO app_user;

-- Set default privileges for functions
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT EXECUTE ON FUNCTIONS TO app_user;

-- View default privileges
\ddp

Advanced Permission Scenarios

Implementing Row-Level Security (RLS)

PostgreSQL's row-level security allows filtering of rows based on current user:

-- Create table with multi-tenant data
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    owner VARCHAR(50),
    department VARCHAR(50),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Enable row-level security
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Create policy: users can only see their own documents
CREATE POLICY user_documents ON documents
    FOR SELECT
    USING (owner = current_user);

-- Create policy: users can insert only with their username
CREATE POLICY user_insert ON documents
    FOR INSERT
    WITH CHECK (owner = current_user);

-- Create policy: users can update only their own documents
CREATE POLICY user_update ON documents
    FOR UPDATE
    USING (owner = current_user);

-- Create policy: department managers see all department documents
CREATE POLICY department_managers ON documents
    FOR SELECT
    USING (
        department = (
            SELECT department
            FROM users
            WHERE username = current_user
            AND role = 'manager'
        )
    );

-- Create permissive policy (OR logic with other policies)
CREATE POLICY admin_all ON documents
    FOR ALL
    TO admin_role
    USING (true);

-- View policies
\d+ documents

Implementing Schema-Based Access Control

Organize objects in schemas for better access control:

-- Create schemas for different purposes
CREATE SCHEMA app_data;
CREATE SCHEMA app_staging;
CREATE SCHEMA app_reporting;

-- Create roles for each schema
CREATE ROLE data_user WITH LOGIN PASSWORD 'SecurePassword123!';
CREATE ROLE staging_user WITH LOGIN PASSWORD 'SecurePassword123!';
CREATE ROLE report_user WITH LOGIN PASSWORD 'SecurePassword123!';

-- Grant schema access
GRANT USAGE ON SCHEMA app_data TO data_user;
GRANT ALL ON SCHEMA app_staging TO staging_user;
GRANT USAGE ON SCHEMA app_reporting TO report_user;

-- Grant table privileges within schemas
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app_data TO data_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA app_staging TO staging_user;
GRANT SELECT ON ALL TABLES IN SCHEMA app_reporting TO report_user;

-- Set default privileges for new objects
ALTER DEFAULT PRIVILEGES IN SCHEMA app_data
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO data_user;

-- Revoke public schema access
REVOKE ALL ON SCHEMA public FROM PUBLIC;

Implementing Function-Based Security

Control access through security definer functions:

-- Create function with SECURITY DEFINER
CREATE OR REPLACE FUNCTION update_user_email(
    p_user_id INTEGER,
    p_new_email VARCHAR
)
RETURNS VOID
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
    -- Validate email format
    IF p_new_email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN
        RAISE EXCEPTION 'Invalid email format';
    END IF;

    -- Update only if user owns the record
    UPDATE users
    SET email = p_new_email,
        updated_at = NOW()
    WHERE id = p_user_id
      AND username = current_user;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Permission denied or user not found';
    END IF;
END;
$$;

-- Grant execute privilege to app users
GRANT EXECUTE ON FUNCTION update_user_email(INTEGER, VARCHAR) TO app_user;

-- Users can call function without direct table access
-- Revoke direct table access
REVOKE UPDATE ON users FROM app_user;

Creating Views for Data Security

Use views to hide sensitive columns:

-- Create base table with sensitive data
CREATE TABLE customer_data (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(20),
    credit_card VARCHAR(16),
    ssn VARCHAR(11),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Create view without sensitive columns
CREATE VIEW customer_basic AS
SELECT id, name, email, phone, created_at
FROM customer_data;

-- Create view with masked sensitive data
CREATE VIEW customer_masked AS
SELECT
    id,
    name,
    email,
    phone,
    CONCAT('****-****-****-', RIGHT(credit_card, 4)) AS credit_card_masked,
    CONCAT('***-**-', RIGHT(ssn, 4)) AS ssn_masked,
    created_at
FROM customer_data;

-- Grant access to views only
GRANT SELECT ON customer_basic TO app_user;
GRANT SELECT ON customer_masked TO support_user;

-- Revoke access to base table
REVOKE ALL ON customer_data FROM app_user, support_user;

Security Best Practices Implementation

Implement Connection Security

Configure pg_hba.conf for secure connections:

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

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Local connections use peer authentication
local   all             postgres                                peer

# Require SSL for network connections
hostssl all             all             0.0.0.0/0              scram-sha-256
hostssl replication     replication     192.168.1.0/24         scram-sha-256

# Reject non-SSL network connections
hostnossl all           all             0.0.0.0/0              reject

# Allow local network without SSL (development only)
host    all             all             127.0.0.1/32           scram-sha-256
host    all             all             ::1/128                scram-sha-256

Enable SSL in postgresql.conf:

sudo nano /etc/postgresql/14/main/postgresql.conf

ssl = on
ssl_cert_file = '/etc/postgresql/14/main/server.crt'
ssl_key_file = '/etc/postgresql/14/main/server.key'
ssl_ca_file = '/etc/postgresql/14/main/root.crt'

Restart PostgreSQL:

sudo systemctl restart postgresql

Implement Password Policies

Configure password requirements:

-- Install passwordcheck extension
CREATE EXTENSION IF NOT EXISTS passwordcheck;

-- The extension automatically enforces:
-- - Minimum length (default 8 characters)
-- - Must contain numbers
-- - Must contain special characters
-- - Password not same as username

-- Test password strength
CREATE ROLE test_user WITH PASSWORD 'weak';  -- Will fail
CREATE ROLE test_user WITH PASSWORD 'StrongP@ss123!';  -- Will succeed

Configure in postgresql.conf:

passwordcheck.minimum_length = 12

Implement Audit Logging

Enable comprehensive audit logging:

-- Install pgaudit extension
CREATE EXTENSION IF NOT EXISTS pgaudit;

-- Configure audit settings
ALTER SYSTEM SET pgaudit.log = 'write, ddl, role';
ALTER SYSTEM SET pgaudit.log_catalog = off;
ALTER SYSTEM SET pgaudit.log_parameter = on;
ALTER SYSTEM SET pgaudit.log_relation = on;

-- Reload configuration
SELECT pg_reload_conf();

-- Verify settings
SHOW pgaudit.log;

In postgresql.conf:

# Audit logging
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'write, ddl, role'
pgaudit.log_catalog = off
pgaudit.log_level = 'log'

Implement Connection Limits

Control connection usage:

-- Set database-wide connection limit
ALTER DATABASE myapp_db CONNECTION LIMIT 100;

-- Set per-role connection limits
ALTER ROLE app_user CONNECTION LIMIT 10;
ALTER ROLE readonly CONNECTION LIMIT 5;

-- View connection limits
SELECT rolname, rolconnlimit
FROM pg_roles
WHERE rolconnlimit <> -1;

-- View current connections
SELECT usename, COUNT(*)
FROM pg_stat_activity
GROUP BY usename;

-- Kill idle connections
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND state_change < NOW() - INTERVAL '1 hour';

Monitoring and Auditing

Monitor Active Sessions

View current database connections:

-- View all active connections
SELECT pid, usename, application_name, client_addr, backend_start, state, query
FROM pg_stat_activity
WHERE state = 'active';

-- Count connections per user
SELECT usename, COUNT(*)
FROM pg_stat_activity
GROUP BY usename
ORDER BY count DESC;

-- View long-running queries
SELECT pid, usename, client_addr, query_start, state, query
FROM pg_stat_activity
WHERE state != 'idle'
  AND query_start < NOW() - INTERVAL '5 minutes'
ORDER BY query_start;

-- Terminate specific connection
SELECT pg_terminate_backend(12345);  -- Replace with actual PID

-- Terminate all connections for a user
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'app_user';

Audit Role Privileges

Create privilege audit queries:

-- View all role privileges
SELECT grantee, privilege_type, table_schema, table_name
FROM information_schema.table_privileges
WHERE grantee NOT IN ('postgres', 'PUBLIC')
ORDER BY grantee, table_schema, table_name;

-- View column-level privileges
SELECT grantee, privilege_type, table_name, column_name
FROM information_schema.column_privileges
WHERE grantee NOT IN ('postgres')
ORDER BY grantee, table_name, column_name;

-- View roles with superuser privileges
SELECT rolname FROM pg_roles WHERE rolsuper = true;

-- View role memberships
SELECT r.rolname AS role,
       ARRAY_AGG(m.rolname) AS members
FROM pg_roles r
JOIN pg_auth_members am ON r.oid = am.roleid
JOIN pg_roles m ON am.member = m.oid
GROUP BY r.rolname
ORDER BY r.rolname;

-- View database privileges
SELECT datname, datacl
FROM pg_database
WHERE datname NOT IN ('template0', 'template1');

Create Security Audit Script

#!/bin/bash
# PostgreSQL Security Audit Script

echo "=== PostgreSQL Security Audit Report ==="
echo "Generated: $(date)"
echo ""

sudo -u postgres psql -d postgres << 'EOF'

\echo '=== Superuser Accounts ==='
SELECT rolname, rolcanlogin
FROM pg_roles
WHERE rolsuper = true
ORDER BY rolname;

\echo ''
\echo '=== Roles Without Password ==='
SELECT rolname
FROM pg_authid
WHERE rolcanlogin = true
  AND rolpassword IS NULL
ORDER BY rolname;

\echo ''
\echo '=== Roles with No Connection Limit ==='
SELECT rolname, rolconnlimit
FROM pg_roles
WHERE rolcanlogin = true
  AND rolconnlimit = -1
ORDER BY rolname;

\echo ''
\echo '=== Public Schema Privileges ==='
SELECT grantee, privilege_type
FROM information_schema.table_privileges
WHERE table_schema = 'public'
  AND grantee = 'PUBLIC';

\echo ''
\echo '=== Active Connections by User ==='
SELECT usename, COUNT(*) as connections
FROM pg_stat_activity
GROUP BY usename
ORDER BY connections DESC;

\echo ''
\echo '=== Recent Failed Connection Attempts ==='
-- Check log file for failed authentication
\! grep "authentication failed" /var/log/postgresql/postgresql-14-main.log | tail -10

EOF

Make executable and run:

chmod +x pg_security_audit.sh
./pg_security_audit.sh

Verification and Testing

Test Role Permissions

# Test connection as specific role
psql -U app_user -d myapp_db -h localhost

# Test specific permissions
psql -U app_user -d myapp_db -c "SELECT * FROM users LIMIT 1;"
psql -U app_user -d myapp_db -c "INSERT INTO test VALUES ('data');"
psql -U readonly -d myapp_db -c "DELETE FROM test;"  # Should fail

Verify Privilege Configuration

-- Check if role can connect to database
SELECT has_database_privilege('app_user', 'myapp_db', 'CONNECT');

-- Check if role can select from table
SELECT has_table_privilege('app_user', 'users', 'SELECT');

-- Check if role can update table
SELECT has_table_privilege('app_user', 'users', 'UPDATE');

-- Check column privileges
SELECT has_column_privilege('app_user', 'employees', 'salary', 'SELECT');

-- Check schema usage
SELECT has_schema_privilege('app_user', 'public', 'USAGE');

-- Check function execute privilege
SELECT has_function_privilege('app_user', 'update_user_email(integer,varchar)', 'EXECUTE');

Test Row-Level Security

-- Create test users
CREATE ROLE alice WITH LOGIN PASSWORD 'SecurePassword123!';
CREATE ROLE bob WITH LOGIN PASSWORD 'SecurePassword123!';

-- Grant basic privileges
GRANT CONNECT ON DATABASE myapp_db TO alice, bob;
GRANT USAGE ON SCHEMA public TO alice, bob;
GRANT SELECT, INSERT ON documents TO alice, bob;

-- Insert test data as different users
SET ROLE alice;
INSERT INTO documents (title, content, owner) VALUES ('Alice Doc', 'Content', 'alice');

SET ROLE bob;
INSERT INTO documents (title, content, owner) VALUES ('Bob Doc', 'Content', 'bob');

-- Verify RLS: Alice should only see her documents
SET ROLE alice;
SELECT * FROM documents;  -- Shows only Alice's documents

-- Verify RLS: Bob should only see his documents
SET ROLE bob;
SELECT * FROM documents;  -- Shows only Bob's documents

-- Reset to superuser
RESET ROLE;

Troubleshooting

Permission Denied Errors

Issue: ERROR: permission denied for table users

Solution: Grant necessary privileges:

-- Check current privileges
\dp users

-- Check if user has schema access
SELECT has_schema_privilege('app_user', 'public', 'USAGE');

-- Grant schema usage first
GRANT USAGE ON SCHEMA public TO app_user;

-- Then grant table privileges
GRANT SELECT ON users TO app_user;

-- Verify privileges were granted
\dp users

Role Does Not Exist Errors

Issue: ERROR: role "app_user" does not exist

Solution: Create the role:

-- Check if role exists
SELECT rolname FROM pg_roles WHERE rolname = 'app_user';

-- Create the role if missing
CREATE ROLE app_user WITH LOGIN PASSWORD 'SecurePassword123!';

-- Verify creation
\du app_user

Authentication Failed Errors

Issue: FATAL: password authentication failed for user "app_user"

Solution: Reset password and check pg_hba.conf:

-- Reset password
ALTER ROLE app_user WITH PASSWORD 'NewSecurePassword456!';

Check pg_hba.conf:

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

Ensure correct authentication method:

host    all             app_user        127.0.0.1/32            scram-sha-256

Reload configuration:

sudo systemctl reload postgresql

Connection Limit Exceeded

Issue: FATAL: too many connections for role "app_user"

Solution: Increase connection limit or close idle connections:

-- Check current connections
SELECT COUNT(*) FROM pg_stat_activity WHERE usename = 'app_user';

-- Increase connection limit
ALTER ROLE app_user CONNECTION LIMIT 20;

-- Or terminate idle connections
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'app_user'
  AND state = 'idle'
  AND state_change < NOW() - INTERVAL '10 minutes';

Row-Level Security Issues

Issue: Row-level security policies not working as expected

Solution: Verify RLS configuration:

-- Check if RLS is enabled
SELECT tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public'
  AND tablename = 'documents';

-- View all policies
SELECT * FROM pg_policies WHERE tablename = 'documents';

-- Disable RLS temporarily for testing (as superuser)
ALTER TABLE documents DISABLE ROW LEVEL SECURITY;

-- Re-enable RLS
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Force RLS even for table owner
ALTER TABLE documents FORCE ROW LEVEL SECURITY;

Best Practices Summary

1. Principle of Least Privilege

Grant only the minimum privileges required for each role to perform their duties. Start with minimal access and add privileges as needed.

2. Use Role Hierarchies

Create group roles and grant membership to simplify management:

-- Create groups
CREATE ROLE readers;
CREATE ROLE writers;

-- Grant privileges to groups
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readers;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO writers;

-- Add users to groups
GRANT readers TO user1;
GRANT writers TO user2;

3. Never Use Superuser for Applications

Always create dedicated roles with specific privileges for application connections. Reserve superuser access for administrative tasks only.

4. Implement Schema Separation

Use schemas to organize objects and simplify permission management:

-- Create separate schemas
CREATE SCHEMA application;
CREATE SCHEMA reporting;
CREATE SCHEMA staging;

-- Grant appropriate access
GRANT USAGE ON SCHEMA application TO app_role;
GRANT USAGE ON SCHEMA reporting TO report_role;

5. Use Row-Level Security for Multi-Tenant Applications

Implement RLS policies to automatically filter data based on current user, eliminating the need for application-level filtering.

6. Enable Connection Encryption

Always use SSL/TLS for network connections. Configure pg_hba.conf to require SSL:

hostssl all all 0.0.0.0/0 scram-sha-256

7. Regular Security Audits

Schedule monthly security audits to:

Review all role privileges
Identify unused roles
Check for excessive privileges
Verify RLS policies
Review audit logs

8. Document Role Purposes

Maintain comprehensive documentation:

Role purpose and owner
Privilege requirements
Review schedule
Contact information
Change history

9. Implement Audit Logging

Enable pgaudit for comprehensive activity logging:

CREATE EXTENSION pgaudit;
ALTER SYSTEM SET pgaudit.log = 'write, ddl, role';

10. Test Privilege Changes

Always test privilege changes in a development environment before applying to production:

-- Begin transaction for testing
BEGIN;

-- Make privilege changes
GRANT SELECT ON sensitive_table TO test_role;

-- Test as the role
SET ROLE test_role;
SELECT * FROM sensitive_table LIMIT 1;

-- Rollback if not correct
ROLLBACK;

-- Or commit if correct
COMMIT;

Conclusion

Effective user and permission management in PostgreSQL is essential for maintaining a secure, compliant, and well-organized database environment. By following this comprehensive guide, you've learned how to create and manage roles with appropriate privileges, implement row-level security for fine-grained access control, use schemas for organizational security, secure connections with SSL/TLS and proper authentication, audit user activities and privileges, and troubleshoot common permission issues.

PostgreSQL's sophisticated privilege system provides the tools needed to implement robust security policies that protect your data while allowing legitimate users to perform their work efficiently. The key is understanding the different privilege levels, using role hierarchies effectively, implementing row-level security where appropriate, regularly auditing and reviewing permissions, and maintaining comprehensive documentation.

As your PostgreSQL environment evolves, continue to review and refine your permission structure to ensure it meets both security requirements and operational needs. Implement automated monitoring and alerting for suspicious activities, conduct regular security training for team members, stay updated with PostgreSQL security features and best practices, maintain a clear process for privilege requests and approvals, and regularly test your security configurations through simulated attack scenarios.

The foundation you've built with this guide will serve as a solid basis for managing PostgreSQL security in environments of any size and complexity. Remember that security is an ongoing process requiring continuous attention, regular updates, and adaptation to new threats and requirements. By maintaining vigilance and following the best practices outlined in this guide, you'll ensure your PostgreSQL databases remain secure and compliant while supporting your organization's data management needs.

User and Permission Management in PostgreSQL: Complete Security Guide

User and Permission Management in PostgreSQL: Complete Security Guide

Introduction

Prerequisites

System Requirements

PostgreSQL Access

Knowledge Requirements

Recommended Tools

Understanding PostgreSQL Roles and Privileges

Role Concept

Role Attributes

Privilege Types

Step-by-Step User Management

Step 1: Connect to PostgreSQL

Step 2: View Existing Roles

Step 3: Create Basic Roles

Step 4: Modify Existing Roles

Step 5: Grant Database-Level Privileges

Step 6: Grant Schema-Level Privileges

Step 7: Grant Table-Level Privileges

Step 8: Grant Column-Level Privileges

Step 9: Implement Role Hierarchy

Step 10: Set Default Privileges

Advanced Permission Scenarios

Implementing Row-Level Security (RLS)

Implementing Schema-Based Access Control

Implementing Function-Based Security

Creating Views for Data Security

Security Best Practices Implementation

Implement Connection Security

Implement Password Policies

Implement Audit Logging

Implement Connection Limits

Monitoring and Auditing

Monitor Active Sessions

Audit Role Privileges

Create Security Audit Script

Verification and Testing

Test Role Permissions

Verify Privilege Configuration

Test Row-Level Security

Troubleshooting

Permission Denied Errors

Role Does Not Exist Errors

Authentication Failed Errors

Connection Limit Exceeded

Row-Level Security Issues

Best Practices Summary

1. Principle of Least Privilege

2. Use Role Hierarchies

3. Never Use Superuser for Applications

4. Implement Schema Separation

5. Use Row-Level Security for Multi-Tenant Applications

6. Enable Connection Encryption

7. Regular Security Audits

8. Document Role Purposes

9. Implement Audit Logging

10. Test Privilege Changes

Conclusion

Latest Video

Get $20 Free Credit