Four tables open for eight weeks

1. How the hole opened

A directory product with about nine thousand pages runs on a hosted Postgres where every table is created by a migration file that also enables row security and writes the policies. In June a de-duplication of the main listings table was done by hand, and it left behind four working tables, two backups and two mappings, created with create table as select from the dashboard.

A table created that way gets the schema defaults: no row security, and the public grants the platform gives the anonymous role. From that day the four tables were readable and writable by anyone holding the public key that ships inside the web app.

2. How it was found, and what the alert got wrong

The platform advisor sent an email in August naming one table, detected on the ninth. The scan was right about what it saw and wrong about the shape of the incident: there were four tables, and the exposure began on the fourteenth of June, eight weeks earlier. An advisor reports its own scan, and the scan date is never the incident date.

The tables held de-duplication working data, so the exposure was of listing data already public on the site. The write access was the real problem: any visitor could have altered the mapping the product used.

3. Closing it, and proving it closed

The fix was a migration that enables row security on the four tables and adds no policies, which denies everything to the anonymous role. Proof came from outside: a request to each table with the public key returned 206 before the migration and 401 after. A fix confirmed only from the dashboard is a fix you believe; a 401 from the public internet is one you know.

The audit query below now runs after any migration-adjacent change. Only the PostGIS reference table is expected to show without row security, because the extension owns it.

select c.relname,
       c.relrowsecurity as rls_enabled,
       (select count(*) from pg_policies p
         where p.tablename = c.relname) as policies,
       has_table_privilege('anon', c.oid, 'SELECT') as anon_select
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public' and c.relkind = 'r'
order by c.relrowsecurity, c.relname;

4. What to take away

• 1

  Migration discipline is the only thing enforcing row security. A table created any other way is open by default.

• 2

  Verify from the outside with the public key. The dashboard shows intent; the 401 shows the state.

• 3

  Treat an advisor alert as a scan result. Establish the count and the start date yourself before you write the incident down.