# Per-member psql access (ident auth)

> Status: canonical. Updated 2026-09-04.

Every approved `engine.member` gets a PostgreSQL `LOGIN` role so they
can connect with `psql` directly. Authentication is by `ident` -- the
member connects as their own OS account, and `pg_ident.conf` on the DB
host maps that OS user to the member's `l_<loginid>` PG role. No
password is ever set, displayed, or stored.

The `l_<loginid>` pattern is the canonical ident-auth role naming. A
parallel Python provisioning helper (`bbsengine6.pgrole`) uses the
`m_<moniker>` pattern for the connection-pool path; both are
LOGIN-capable and both inherit from the `member` group role. They are
distinct namespaces: `l_<loginid>` is the legacy ident-auth name,
`m_<moniker>` is the Python-managed provisioning name. New code should
call `bbsengine6.pgrole.ensure_login_role()` rather than constructing
role names directly.

## Architecture

| Layer | Detail |
| --- | --- |
| PG role | `l_<loginid>`, derived from the member's `loginid` (lowercased, non-alnum -> `_`, numeric suffix on collision with any existing `pg_roles.rolname`) |
| Group role | `member` (NOLOGIN, NOINHERIT). Every `l_<loginid>` is GRANTed membership. Baseline schema usage and SELECT privileges are granted to `member`, not to each `l_<loginid>` |
| Group sync | `engine.syncpgrolegroups(memberid)` brings a member's `l_<loginid>` role's `sysop` / `term` / `web` group memberships in line with the member's current flags. Called from the sysop console (`console/member.py:edit()`, `add()`) and from the approvals flow (`console/memberapproval.py`) |
| Tracking | `engine.pgrole(memberid, rolname, osuser, created_at, last_ack_at)`. `osuser` is the OS username the member connects from; `last_ack_at` records that the member has seen the welcome screen |
| Provisioning (legacy SQL) | `engine.createpgrole(loginid, osuser)` is the single SQL entry point. It derives the rolename, collision-suffixes, runs `CREATE ROLE ... LOGIN INHERIT` (no password), `GRANT member TO`, and INSERTs the tracking row |
| Provisioning (Python) | `bbsengine6.pgrole.ensure_login_role(args, moniker)` -- idempotent Python wrapper around the same flow, using `m_<moniker>` naming |
| Removal | `engine.deletepgrole(rolname)` runs `DROP ROLE IF EXISTS` and removes the `engine.pgrole` row |

## Configuration

### `pg_hba.conf`

The DB host needs an `ident` line that uses the `bbbsmap` map for local
connections:

```
host    all    all    127.0.0.1/32    ident    map=bbbsmap
```

If members also need to connect from a specific subnet (e.g. a
terminal-server LAN), add additional `host` lines scoped to that
subnet. Do **not** use `0.0.0.0/0` for ident -- ident is meaningful
only for trusted, controlled networks.

### `pg_ident.conf`

One line per member, mapping the OS user to the PG role:

```
bbbsmap    l_jonez    jonez
```

The OS user is what the member `su`s to (or is logged in as) on the
client host. It does not have to be unique globally -- it just has to
be unique per client host. The PG role name on the left side must
match the `l_<loginid>` value stored in `engine.pgrole.rolname`.

After editing, reload PostgreSQL:

```
sudo systemctl reload postgresql
```

(or `SELECT pg_reload_conf();` from a SQL session as a superuser.)

## First-time setup checklist

1. Add the `ident` line to `pg_hba.conf`.
2. Run `\i bbsengine6.sql` so the `engine.pgrole` table, the `member`
   group role, and the `engine.createpgrole` /
   `engine.syncpgrolegroups` / `engine.deletepgrole` functions are
   created.
3. Run `py/src/bbsengine6/sql/backfill_pgrole.sql` to provision
   `l_<loginid>` roles for all currently-approved members. Their
   `osuser` is left NULL -- to be filled in by the member on first
   `[P] psql credentials` visit, or by the sysop.
4. For each backfilled member (and each new member going forward), add
   a `bbbsmap` line to `pg_ident.conf` and reload PG.
5. Members run `[P]` from the `member:` console menu. The first visit
   prompts for `osuser` and acknowledges the welcome. The `psql`
   connect command is printed.

## Member flow (the `[P]` menu entry)

`py/src/bbsengine6/console/showpgrole.py:main()`:

- If no `engine.pgrole` row exists for the member: tell them to ask a
  sysop to approve them.
- If a row exists: print the rolname, osuser, and the `psql` connect
  command. If `last_ack_at` is NULL, require an ENTER to acknowledge,
  then capture `osuser` if blank.
- No password, no regenerate, no re-show. Auth is by ident; the only
  secret the member needs is their own OS account.

## What the `member` group grants (and doesn't)

Granted to `member`:

- `USAGE` on `engine` and `bank` schemas.
- `SELECT` on all existing tables in `engine` and `bank`.
- `ALTER DEFAULT PRIVILEGES` so future tables inherit the same
  `SELECT` grant.
- `SELECT` on `engine.pgrole` (so a future `SET LOCAL ROLE` flow can
  read the tracking row).

**Not** granted (intentionally):

- `INSERT` / `UPDATE` / `DELETE` on `engine.__session` or
  `engine.__invite`. Members don't write to these via psql. If that
  becomes a need, add it explicitly.
- `CREATEROLE`, `CREATEDB`, `SUPERUSER`. Members connect read-only.
- `USAGE` on any schema other than `engine` and `bank`. New schemas
  (e.g. a future `games` schema) need their own grants.

## Operations

### Adding a new member

1. The new member submits `join.php` (no PG role is created here).
2. A sysop runs `[A] Approvals` from the `member:` menu and approves
   the application. The console hook calls
   `engine.createpgrole(loginid, NULL)`. The console prints the new
   rolname and reminds the sysop to add the `bbbsmap` line to
   `pg_ident.conf`.
3. The sysop edits `pg_ident.conf` and reloads PG.
4. The member logs in and runs `[P] psql credentials`. The welcome
   flow records `osuser` and sets `last_ack_at = now()`.

### Removing a member

- `engine.deletepgrole(rolname)` drops the PG role and the
  `engine.pgrole` row.
- The sysop should also remove the corresponding `bbbsmap` line from
  `pg_ident.conf` and reload PG.

### Flag changes

Whenever a member's flags change (e.g. they become a sysop), the
`engine.syncpgrolegroups(memberid)` SQL function is called from
`console/member.py:edit()` to bring the `l_<loginid>` role's `sysop` /
`term` / `web` group memberships in line. This is idempotent and cheap;
safe to call on every edit.

## Python-managed provisioning (current)

`py/src/bbsengine6/pgrole.py` is the current Python implementation of
per-member role provisioning. It exposes two public functions:

```python
ensure_login_role(args, moniker, **kwargs) -> str
sync_groups(args, loginid) -> bool
```

`ensure_login_role` is idempotent: if the role already exists or a
`pgrole` row exists, it returns the existing rolename without making
changes. On a fresh provisioning it:

1. Derives the rolename `m_<moniker>` (regex-sanitized to
   `[A-Za-z0-9_]+`).
2. Checks for an existing `engine.pgrole` row.
3. Checks whether the PostgreSQL role exists; if not, calls
   `database.createrol(..., login=True, superuser=False, createdb=False,
   createrole=False)`.
4. `GRANT member TO <rolename>`.
5. `manage_schema_priv(args, "grant", "usage", "engine", rolname)` for
   schema access.
6. `INSERT INTO engine.pgrole (membermoniker, rolname, created_at)`.

`sync_groups` calls `engine.syncpgrolegroups(<membermoniker>)` to
update the role's `sysop` / `term` / `web` memberships after a flag
change.

## Deferred work

- **Password fallback**: add a password to `engine.createpgrole` for
  deployments without stable OS accounts per member. Would also require
  `engine.rotatepgrole`, a regenerate UI in `showpgrole.py`, and a
  redaction list in the logging helpers.
- **PHP web surface**: `engine/psql_credentials.php` plus helpers in
  `php/libmember.php`. Blocked on the web machine being on the same
  host as the DB.
- **`SET LOCAL ROLE member` per request**: implemented in
  `database.connect()` / `database.async_connect()` via the
  `set_role=` keyword argument. The `www-data` DSN user has been
  granted membership in `member` (`checkwebserverrole.py`). Call
  `database.connect(args, pool=pool, set_role="member")` to run a
  transaction as the `member` group role. Per-member data isolation
  still requires RLS -- see `bbsengine6/TODO_RLS.md`.
- **`SET LOCAL ROLE l_<loginid>` per request**: would switch to the
  specific member's role. Requires RLS for meaningful privacy
  enforcement, plus `GRANT l_<loginid> TO "www-data"` per member (or a
  superuser DSN user).
- **Email notification** on psql access provisioning.
- **Audit table**: `engine.pgrole_event` for tighter tracking of every
  `CREATE ROLE` / `ALTER ROLE` / `DROP ROLE` / `GRANT` / `REVOKE`.
- **Re-show the current osuser** would require no extra work (it is
  already shown). Re-show a *password* is impossible without
  persisting it, which contradicts the design.