A Quarterly Access Review Finds a New Table of Bank Accounts That Every Role Can Read

The shop in this case study is made up: it’s the sample shop database that BareSQL’s tests build, and the outputs are what BareSQL printed. It reviews database access once a quarter. The first review with BareSQL Audit covers every login and ends with a file kept for next time, audit.json.

Three months later the review runs again against that file. The reviewer then reads what changed, with no need to compare two long reports by eye.

On 1 July the audit gives this summary:

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

The Quarter’s Changes

During the quarter, three changes go in, each reviewed on its own if at all. A refunds table with customers’ bank accounts is created for the payments team. The new intern joins the support team. And the analyst loses access to the products table, which the analyst’s work no longer needs:

SET ROLE shop_owner;
CREATE TABLE shop.refunds (id integer PRIMARY KEY, order_id integer, iban text, amount numeric(10,2));
INSERT INTO shop.refunds VALUES ... twelve refunds with IBANs, made by the test ...;
RESET ROLE;
GRANT shop_support_team TO shop_intern;
REVOKE SELECT ON shop.products FROM shop_analyst;

The Second Review

On 1 October the audit runs with the July file as its baseline:

baresql audit --baseline q3/audit.json "postgres://postgres@localhost/baresql_fx_shop"

It prints the new summary and the comparison, and exits 1, because access grew. Here is the part of the comparison about the refunds table, with only its IBAN lines:

Compared the audit of 2026-07-01T09:00:00Z with the audit of 2026-10-01T09:00:00Z.
Reach that grew: 49
  + read PUBLIC shop.refunds.iban (bank account (IBAN); granted to PUBLIC)
  + read shop_alice shop.refunds.iban (bank account (IBAN); granted to PUBLIC)
  + read shop_analyst shop.refunds.iban (bank account (IBAN); granted to PUBLIC)
  + read shop_app shop.refunds.iban (bank account (IBAN); granted to PUBLIC)
  + read shop_bob shop.refunds.iban (bank account (IBAN); granted to PUBLIC)
  + read shop_etl shop.refunds.iban (bank account (IBAN); granted to PUBLIC)
  + read shop_intern shop.refunds.iban (bank account (IBAN); granted to PUBLIC)
  + read shop_reporting shop.refunds.iban (bank account (IBAN); granted to PUBLIC | member of pg_read_all_data)
Reach that is gone: 4
Other changes: 1
  ~ finding public-reads-personal shop.refunds (new, high: Every role, including roles created later, can read personal data in shop.refunds.)

Nobody granted the refunds table to anyone. The July report had already said why it would leak, in a medium finding: “New tables that shop_owner creates in schema shop are granted SELECT to PUBLIC.” Default privileges are set once and then forgotten, and they apply to every table the role creates later. The refunds table was the first one with bank accounts in it, so in October the same mechanism produced a high finding, public-reads-personal, and every login could read the IBANs.

The Intern Got More Than a Team Membership

Of the 49 new pieces of reach, 17 came to the intern with one GRANT. Joining the support team brought everything the team reads, including the customers’ card numbers through the support view:

  + read shop_intern shop.customers.card_number (payment card number; through view support.customer_lookup (rights of shop_owner))
  + read shop_intern support.customer_lookup.card_number (payment card number; granted to shop_support_team)

Whether an intern on the support desk should see card numbers is a question for the business, and the support view case study takes it up. The point here is that a membership brings all of a group’s reach at once, and this diff shows it line by line, where the GRANT statement showed nothing.

The Access That Went

The diff also lists 4 pieces of reach that are gone: the analyst’s four columns of the products table, as intended. A review that only looks at growth would miss a removal that went wrong, so the diff lists both.

What the Shop Changed

The refunds table loses its PUBLIC grant, and the default privileges stop handing new tables to PUBLIC:

REVOKE SELECT ON shop.refunds FROM PUBLIC;
ALTER DEFAULT PRIVILEGES FOR ROLE shop_owner IN SCHEMA shop REVOKE SELECT ON TABLES FROM PUBLIC;

The second statement removes the cause, the default privilege that the July finding was about. The next morning the audit runs again, with the October file as its baseline. It finds 28 pieces of reach gone and none grown, and the July finding is gone along with the October one. Without its 28 lines of reach, the comparison reads:

Compared the audit of 2026-10-01T09:00:00Z with the audit of 2026-10-02T09:00:00Z.
Reach that grew: 0
Reach that is gone: 28
Other changes: 2
  ~ finding default-grants-to-public shop_owner (gone)
  ~ finding public-reads-personal shop.refunds (gone)

The report’s other findings were there before the quarter began, and each needs a decision of its own. The same comparison can also run every night from a scheduler. With --baseline the job fails on the day access grows, so the next table like this one is caught the morning after it appears. All three reports are on the sample reports page.