Postgres 一條 `using (true)` 政策會令整張資料表公開

You hit a permission error. The quick fix is a policy with using (true), and the error goes away. So does the protection on that table.

Why one loose policy wins

Postgres policies are permissive by default. When a table has several permissive policies for the same command, a row is visible if any one of them returns true. They are combined with OR.

create policy "Org members read invoices" on invoices for select
  using (org_id in (select org_id from members where user_id = auth.uid()));

create policy "allow read" on invoices for select
  using (true);

The first policy is correct. The second makes it irrelevant: every row passes true, so anyone holding the anon key can read every invoice.

Find them

select schemaname, tablename, policyname, roles, cmd, qual, with_check
from pg_policies
where qual = 'true' or with_check = 'true';

Each row is a policy that lets everything through for its command and roles. Some are on purpose, like a public countries table. The rest are bugs.

Make it a test

Run that query in CI after your migrations and fail when it returns a table that is not on an allow list. One test covers the whole class, including the policy nobody has written yet: the next one an agent adds to make an error go away.

If you need a hard limit

Restrictive policies are combined with AND. A row has to pass at least one permissive policy and every restrictive one, so a restrictive policy caps what any permissive policy can open:

create policy "org boundary" on invoices as restrictive for select
  using (org_id in (select org_id from members where user_id = auth.uid()));

With that in place, a later using (true) cannot widen reads past the org boundary.

Two more to check

• with check (true) on insert or update: anyone allowed to write can write any row.
• A policy with no to clause applies to every role, anon included.

Zelsis flags using (true) policies on pull requests. There is also a 14-second version of this post: https://youtube.com/shorts/hHrIz9LJhD0

Have you found one of these in a real project? I'm curious what the "temporary" reason was.