Skip to content
← Guides

Guide · Audit

What a PostgreSQL security audit covers, and how to read the report

A database audit is not a network scan or a list of "best practices". It is looking at PostgreSQL from the inside, with credentials, and answering one question: who can do what today, and what would happen if they made a mistake or meant harm. This is what it should cover, and how to tell whether the report you received is worth anything.

Published on 8 min read By Manuel Reyes Bravo

What it is, and what it is not

It is not a pentest of the network or the server: that checks whether someone can reach the database. The database audit assumes someone already did, with a valid user (an application, an analyst, an AI agent, an employee), and measures how far they can get from there.

It is not a generic checklist either. Recommendations that do not come with a query proving the problem on your database are opinions.

1. Roles and privileges

Who the superusers are and why. Which roles can create other roles or databases. Which roles have LOGIN without needing it. What each application can do on each table, and what everyone inherits through PUBLIC. Whether default privileges (ALTER DEFAULT PRIVILEGES) hand out grants on everything created in the future.

sql
-- who can do what, per role, outside the catalog
SELECT grantee, table_schema, table_name, string_agg(privilege_type, ', ' ORDER BY privilege_type) AS privileges
FROM information_schema.role_table_grants
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3;

2. Authentication and access

From where each role may connect and with which method (pg_hba.conf): trust or cleartext password are findings; SCRAM-SHA-256 is what is expected. Whether traffic is encrypted (ssl) and whether it is required. Whether listen_addresses exposes the server to networks that should not see it. On managed services, the equivalent in their network and user configuration.

3. Data separation (RLS) and multi-tenancy

If several customers share tables: which tables have row-level policies, whether they are forced for the owner, what the policies read (a session parameter that can be moved with SET is a finding), and whether any role has BYPASSRLS. And the practical test: with the application’s role, try to read another customer’s data.

4. Extensions, functions and powerful objects

Which extensions are installed and what they allow (dblink, file_fdw, untrusted languages). SECURITY DEFINER functions and who can execute them. Roles in pg_read_server_files, pg_write_server_files or pg_execute_server_program. Triggers, rules and cascades that amplify a write beyond what the role could do directly.

sql
SELECT extname, extversion FROM pg_extension ORDER BY 1;

-- roles with server-level powers
SELECT r.rolname, m.rolname AS member_of
FROM pg_auth_members am
JOIN pg_roles r ON r.oid = am.member
JOIN pg_roles m ON m.oid = am.roleid
WHERE m.rolname IN ('pg_read_server_files', 'pg_write_server_files', 'pg_execute_server_program');

5. Configuration and logging

What gets logged and what does not: log_statement set to all can write sensitive data and passwords in plain text; log_connections and log_disconnections help reconstruct an incident. The PostgreSQL version and whether it is community-supported; pending security patches.

6. Backups and recovery

That the backup exists is the minimum. The audit asks when someone last restored it somewhere else, how long it took, and whether that time is compatible with what the business tolerates. A backup that was never restored is a hypothesis.

7. AI agents and automations

If there are AI agents, integrations or scripts with database access: which role they connect with, what they can write, whether there is a limit on the effect of each statement, whether they can run DDL, and whether there is a record of what they proposed and what ran. This part has its own guide.

How to read the report

A report is useful if you can verify every finding yourself and know what to do with it. What each point should bring:

  • The evidence: the query or test that proves it, to run on your database.
  • The real risk in your context, not the theoretical severity from a catalog: an extra superuser weighs differently if it is the backup account than if it is the chatbot’s.
  • The concrete change to close it, and what it can break when applied.
  • The order: what to close first and what can wait.
  • A way to repeat the review afterwards (a script, a query) to confirm it stayed closed.

What a good report does not do

Signs you were handed a template rather than an audit:

  • Findings without a query or test that proves them.
  • Everything at "high" severity, or nothing prioritized.
  • Recommendations that cannot be applied on your version or your managed service.
  • No negative finding about the very tool or service they want to sell you afterwards.
  • Saying the database is "secure": an audit bounds risks, it does not remove them.

Frequently asked questions

How often should you audit?

After big changes (a new AI agent, a migration, a customer demanding evidence) and, if nothing changes, at least once a year. The most useful thing is having the verification script and running it whenever you want.

Does it disrupt production?

It should not. The review works on the catalog and the configuration with a read-only role; practical tests run on a copy or in an agreed window.

Who should do it?

Someone who knows PostgreSQL from the inside and does not depend on selling what they find. And if the same person applies the changes afterwards, the report should be verifiable by a third party.

Other guides

See all services →

Try it Talk