Roles, Permissions, Security Hardening, and Auditing
Protect PostgreSQL using role design, least privilege, secure access patterns, and careful operational controls.
Inside this chapter
- Security Starts with Role Design
- Granting Limited Access
- Hardening Checklist
- Security as Continuous Discipline
Series navigation
Study the chapters in sequence for the clearest path from beginner PostgreSQL concepts to advanced query design and production operations. Use the navigation at the bottom of every page to move chapter by chapter.
Security Starts with Role Design
PostgreSQL uses roles for authentication and authorization. Good security begins by separating administrator access, application runtime access, migration access, reporting access, and maintenance access. This reduces risk and makes auditing easier.
Granting Limited Access
CREATE ROLE app_user LOGIN PASSWORD 'strong-password';
GRANT CONNECT ON DATABASE appdb 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;Least privilege is one of the most important database security principles. Applications rarely need superuser access, and giving them too much power increases damage if something goes wrong.
Hardening Checklist
- Use distinct roles for distinct responsibilities.
- Restrict network access and review
pg_hba.confcarefully. - Protect credentials and rotate them safely.
- Use TLS where required by environment and policy.
- Audit schema changes, role changes, and privileged access.
Security as Continuous Discipline
Security is not one-time setup. Advanced teams continuously review patch levels, access patterns, backup exposure, role grants, logging, and incident response procedures. A secure PostgreSQL system is the result of ongoing operational attention.