33 不要信任任何租户

create role wg_migrator login;
grant wg_owner to wg_migrator with inherit false, set true;

select r.rolname as role, m.rolname as member,
       a.inherit_option, a.set_option, a.admin_option
from pg_auth_members a
join pg_roles r on r.oid = a.roleid
join pg_roles m on m.oid = a.member
where m.rolname like 'wg_%';

    role     |   member    | inherit_option | set_option | admin_option
-------------+-------------+----------------+------------+--------------
 wg_owner    | wg_migrator | f              | t          | f
 wg_readonly | wg_analyst  | t              | t          | f

=> \dp app.events
 Schema |  Name  | Type  |     Access privileges      | ...
--------+--------+-------+----------------------------+
 app    | events | table | wg_owner=arwdDxtm/wg_owner+|
        |        |       | wg_app=arwd/wg_owner      +|
        |        |       | wg_readonly=r/wg_owner     |

=> select account_id from app.projects where id = 1;
(0 rows)

begin;
insert into app.events (account_id, project_id, kind)
  values (42, 1, 'deploy') returning id;
rollback;

   id
---------
 2000002

alter table app.projects
  add constraint projects_account_id_id_key unique (account_id, id);
alter table app.events
  add constraint events_project_fk
  foreign key (account_id, project_id) references app.projects (account_id, id);

ERROR:  insert or update on table "events" violates foreign key constraint
        "events_project_fk"
DETAIL:  Key is not present in table "projects".

select current_setting('app.account_id', true) is null as is_null,
       current_setting('app.account_id', true) as val;

 is_null | val
---------+-----
 f       |

import { Pool, PoolClient } from "pg";

const pool = new Pool(); // direct, or through PgBouncer in transaction mode

export async function withTenant<T>(
  accountId: string,
  fn: (db: PoolClient) => Promise<T>,
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query("begin");
    // true = transaction-local: gone at commit, never seen by the next request
    await client.query(
      "select set_config('app.account_id', $1, true)",
      [accountId],
    );
    const result = await fn(client);
    await client.query("commit");
    return result;
  } catch (err) {
    await client.query("rollback");
    throw err;
  } finally {
    client.release();
  }
}

// every query for the request runs inside the tenant's transaction
const { rows } = await withTenant(session.accountId, (db) =>
  db.query("select id, name from app.projects order by name"),
);

 Finalize Aggregate (actual rows=1.00 loops=1)
   Buffers: shared hit=10258 read=23051 written=5
   ...
               ->  Parallel Seq Scan on events (actual rows=160.67 loops=3)
                     Filter: ((kind = 'deploy'::text) AND (account_id =
                       (NULLIF(current_setting('app.account_id'::text, true),
                               ...))::bigint))
                     Rows Removed by Filter: 666506

 Aggregate (actual rows=1.00 loops=1)
   Buffers: shared hit=685 read=1351
   ->  Bitmap Heap Scan on events (actual rows=482.00 loops=1)
         ...
         ->  Bitmap Index Scan on events_account_id_created_at_idx
                 (actual rows=2084.00 ...)
               Index Cond: (account_id =
                 (NULLIF(current_setting('app.account_id'::text, true),
                         ''::text))::bigint)

select oprname, oprcode, p.proleakproof
from pg_operator o join pg_proc p on p.oid = o.oprcode
where (oprleft, oprright) in (('text'::regtype, 'text'::regtype),
                              ('bigint'::regtype, 'bigint'::regtype),
                              ('jsonb'::regtype, 'jsonb'::regtype),
                              ('timestamptz'::regtype, 'timestamptz'::regtype))
  and oprname in ('=', '<', '@>', '~~', '~~*')
order by 1, 2;

 oprname |    oprcode     | proleakproof
---------+----------------+--------------
 <       | int8lt         | t
 <       | text_lt        | t
 <       | timestamptz_lt | t
 <       | jsonb_lt       | f
 =       | texteq         | t
 =       | int8eq         | t
 =       | timestamptz_eq | t
 =       | jsonb_eq       | f
 @>      | jsonb_contains | f
 ~~      | textlike       | f
 ~~*     | texticlike     | f

 Aggregate (actual rows=1.00 loops=1)
   Buffers: shared hit=434
   ->  Bitmap Heap Scan on events (actual rows=1.00 loops=1)
         ->  BitmapAnd (actual rows=0.00 loops=1)
               ->  Bitmap Index Scan on events_payload_idx (actual rows=403.00 ...)
               ->  Bitmap Index Scan on events_account_id_created_at_idx
                       (actual rows=2084.00 ...)

 Aggregate (actual rows=1.00 loops=1)
   Buffers: shared hit=2036
   ->  Bitmap Heap Scan on events (actual rows=1.00 loops=1)
         Filter: (payload @> '{"duration_ms": 1234}'::jsonb)
         Rows Removed by Filter: 2083
         ->  Bitmap Index Scan on events_account_id_created_at_idx
                 (actual rows=2084.00 ...)

alter table app.events
  add column duration_ms int
  generated always as ((payload->>'duration_ms')::int) stored;
create index on app.events (account_id, duration_ms);

 Aggregate (actual rows=1.00 loops=1)
   Buffers: shared hit=1 read=3
   ->  Index Only Scan using events_account_id_duration_ms_idx on events
           (actual rows=1.00 ...)
         Index Cond: ((account_id =
                       (NULLIF(current_setting(...), ''::text))::bigint)
                      AND (duration_ms = 1234))
         Heap Fetches: 0

create function wg_app.steal(bigint) returns bigint language plpgsql as $$
begin
  perform set_config('lab.ran_as', current_user, false);
  return $1 + 1;
end $$;
create aggregate wg_app.count(*) (
  sfunc = wg_app.steal, stype = bigint, initcond = '0'
);
set search_path = wg_app, pg_catalog;
select app.unpinned_event_count(42);

log_connections = 'authentication,authorization'   # PostgreSQL 18 takes a list
log_disconnections = on
log_statement = 'ddl'          # every create/alter/drop, with the user
log_line_prefix = '%m [%p] %q%u@%d %a '

select a.usename, a.client_addr, s.ssl, s.version
from pg_stat_ssl s join pg_stat_activity a using (pid)
where a.backend_type = 'client backend';