Sample Reports

These reports are exactly what BareSQL Audit wrote for the databases its tests build. Each database is made up, with invented people in it, and planted with mistakes that turn up in real ones, plus near misses that look like mistakes and aren’t. For the four databases below, the tests check every line of the audit on PostgreSQL 13 to 18, so a change to BareSQL that alters one of them fails before it ships.

Open a report to read it the way a reviewer would. The audit.json beside it holds the same facts for scripts and for baresql diff.

An Online Shop

An app login, a support team that reads customers through a view, an analyst with column grants, a reporting login in pg_read_all_data, a newsletter table that every role can read and an export function that runs with its owner’s rights.

BareSQL Audit of baresql_fx_shop (PostgreSQL 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)) as postgres
7 logins, 7 of them can read personal data; 10 columns hold personal data.
Findings: 2 high, 4 medium, 0 low
  high   public-reads-personal        shop.newsletter
  high   secdef-no-search-path        shop.export_customers()
  medium default-grants-to-public     shop_owner
  medium read-all-data                shop_reporting
  medium secdef-executable            shop.count_orders()
  medium secdef-executable            shop.export_customers()
Wrote baresql-report/report.html and baresql-report/audit.json

report.html · audit.json

A Multi-Tenant App

Row security keeps each tenant’s rows apart for the app login. An admin console bypasses row security. An AI support agent gets a view that was meant to show tickets and also hands out every tenant’s accounts, since the view runs with the rights of a table owner who isn’t held to the policies.

BareSQL Audit of baresql_fx_saas (PostgreSQL 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)) as postgres
4 logins, 4 of them can read personal data; 5 columns hold personal data.
Findings: 1 high, 1 medium, 1 low
  high   view-bypasses-row-security   support.ticket_context
  medium rls-owner-login              app.audit_log
  low    rls-no-policies              app.api_keys
Wrote baresql-report/report.html and baresql-report/audit.json

report.html · audit.json

A Clinic

Doctors inherit their rights through a chain of groups, and the front desk reads a few patient columns. Research gets one partition and a materialized view. Billing may pass its grant on. Further out, IT can read files on the database server while HR can add doctors without being one. The lab still logs in with an old MD5 password, and it reaches a foreign server.

BareSQL Audit of baresql_fx_clinic (PostgreSQL 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)) as postgres
9 logins, 7 of them can read personal data; 13 columns hold personal data.
Findings: 1 high, 3 medium, 1 low
  high   server-file-access           clinic_it
  medium admin-option                 clinic_hr
  medium grant-option-personal        clinic_billing
  medium md5-passwords                logins
  low    foreign-server-mapping       clinic_lab
Wrote baresql-report/report.html and baresql-report/audit.json

report.html · audit.json

A Database Set Up With Care

The app reads and writes what it needs and nothing more. Reports read daily totals through a view. The one security-definer function sets its own search_path, and only the reports login may run it. Its owner is a role that reads no personal data. An audit of a database like this should find nothing to flag, and BareSQL doesn’t.

BareSQL Audit of baresql_fx_clean (PostgreSQL 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)) as postgres
2 logins, 1 of them can read personal data; 2 columns hold personal data.
Findings: 0 high, 0 medium, 0 low
Wrote baresql-report/report.html and baresql-report/audit.json

report.html · audit.json

Before and After

Each of the three BareSQL Audit case studies runs an audit, changes the database and runs it again. The reports from before and after are here. The quarterly review has a third, from the morning after the fix. In these stories the tests check only the facts each one turns on.

To make reports like these of your own database, start with the quick start.