Metadata-Version: 2.5
Name: pgsesame
Version: 0.4.0
Summary: Permissions as code for PostgreSQL and Amazon Redshift.
Project-URL: Repository, https://github.com/almostly/pgsesame
Project-URL: Changelog, https://github.com/almostly/pgsesame/blob/main/CHANGELOG.md
Project-URL: Design, https://github.com/almostly/pgsesame/blob/main/DESIGN.md
Author: almostly
License-Expression: Apache-2.0
License-File: LICENSE
Keywords: access-control,grants,iac,permissions,postgresql,redshift
Classifier: Development Status :: 3 - Alpha
Classifier: Environment :: Console
Classifier: Intended Audience :: Developers
Classifier: Programming Language :: Python :: 3
Classifier: Topic :: Database
Classifier: Topic :: Security
Requires-Python: >=3.10
Requires-Dist: keyring>=25
Requires-Dist: psycopg[binary]>=3.1
Requires-Dist: pydantic>=2.6
Requires-Dist: pyyaml>=6
Requires-Dist: tomli-w>=1
Requires-Dist: tomli>=2; python_version < '3.11'
Requires-Dist: typer>=0.12
Provides-Extra: aurora
Requires-Dist: boto3>=1.34; extra == 'aurora'
Provides-Extra: dsql
Requires-Dist: boto3>=1.34; extra == 'dsql'
Provides-Extra: rds
Requires-Dist: boto3>=1.34; extra == 'rds'
Provides-Extra: redshift
Requires-Dist: boto3>=1.34; extra == 'redshift'
Description-Content-Type: text/markdown

<p align="center">
  <img src="https://oblako-public.s3.amazonaws.com/pgsesame_icon.png" width="128" alt="pgsesame">
</p>

# pgsesame

Declarative permission management for PostgreSQL and Amazon Redshift. Define
roles, users, groups and grants in YAML, then plan and apply changes like
Terraform.

> **Status: 0.2, alpha.** `validate`, `plan`, `apply`, change sets and `import`
> work for roles, users, groups, memberships, privileges, ownership and default
> privileges on PostgreSQL and Redshift, along with row-level security and Redshift
> masking. Tested against PostgreSQL 14 to 18, Supabase, oblako's redshift-local and
> Redshift Serverless, on Python 3.10 to 3.13. See the [roadmap](DESIGN.md#roadmap).

## Why

Granting access by hand leaves a trail of `GRANT` statements nobody can review.
pgsesame keeps the intended state in one file: changes go through pull requests,
`sesame plan` shows exactly which statements a change needs, and `sesame apply`
runs them. A second plan after apply is empty.

It is built on what went wrong with earlier tools (redtape for Redshift, pgbedrock
for PostgreSQL): it reads the real catalog without crashing on what it doesn't
model, issues only the difference instead of every grant on every run, and plans
revokes and drops but applies them only when you ask.

## Where it runs

PostgreSQL 14 to 18, Amazon Redshift (provisioned or Serverless) and Amazon
Aurora DSQL. PostgreSQL
services work through the same connection, as the platform's admin role: Supabase
(tested, with its built-in roles and row-level security), Google's AlloyDB, Amazon
RDS and Aurora, Cloud SQL, Neon.

On Amazon RDS and Aurora PostgreSQL, `--rds` names the instance or cluster and
pgsesame finds the rest:

```bash
sesame plan permissions.yaml --iam --rds my-cluster        # a signed IAM token, over TLS
sesame plan permissions.yaml --data-api --rds my-cluster --secret-arn arn:aws:secretsmanager:...
```

`--iam` signs an authentication token for the admin user (or `--db-user`), which
is the only way into an Aurora cluster made with express configuration; the
caller needs `rds-db:connect` on that database user, which `AmazonRDSFullAccess`
doesn't include. A user the spec creates signs in the same way once it is a
member of `rds_iam` (`member_of: [rds_iam]`, with `rds_iam: {type: builtin}`). The RDS
Data API runs apply in one transaction. The admin user isn't a superuser: from
PostgreSQL 16 on it changes only the roles it has ADMIN OPTION on, so a login or
membership change it can't make is noted in the plan, not attempted.

Amazon Aurora DSQL is `engine: dsql`: PostgreSQL 16's roles, memberships, grants
and default privileges, plus the IAM identities that sign in as a role:

```yaml
version: 1
engine: dsql
principals:
  app:
    type: user                       # a role that logs in
    iam: [arn:aws:iam::123456789012:role/app-task]   # AWS IAM GRANT app TO '...'
    privileges:
      schemas: {usage: [orders]}
      tables: {select: [orders.*]}
```

```bash
sesame plan permissions.yaml --dsql abc123xyz          # as admin, with an IAM token
sesame login dsql --dsql abc123xyz                     # or save it as a target
```

DSQL signs in with IAM only, so a spec gives it no passwords, and it has no
database privileges, row-level security or ownership changes: a spec naming them
is refused with the reason. It also allows one DDL statement per transaction,
and a GRANT is one, so apply runs each statement in its own transaction; if one
fails, apply says how many before it were applied, and the next plan shows what
remains.

## Install

```bash
uv tool install pgsesame               # installs the `sesame` command
uv tool install "pgsesame[redshift]"   # Redshift through IAM or the Data API
uv tool install "pgsesame[rds]"        # RDS and Aurora through IAM or the Data API (or [aurora])
uv tool install "pgsesame[dsql]"       # Aurora DSQL
uvx pgsesame --help                    # or try it without installing
pip install pgsesame                   # or into an environment
```

To upgrade, `uv tool install --force --refresh 'pgsesame[redshift]'`: without
`--refresh` uv can reuse the version it has cached.

In CI without Python, use the image (amd64 and arm64):

```bash
docker run --rm -v "$PWD:/work" -e SESAME_DSN ghcr.io/almostly/pgsesame plan permissions.yaml
```

pgsesame connects the way you already do: a DSN or the standard `PG*` variables
with a password (PostgreSQL and Redshift), temporary credentials from IAM for a
Redshift cluster or Serverless workgroup, or the Redshift Data API when the
database isn't reachable over the network.

## A spec

```yaml
version: 1
engine: redshift            # or postgres

principals:
  reader:
    type: role
    privileges:
      schemas:
        usage: [analytics]
      tables:
        select: [analytics.*]

  alice:
    type: user
    password_env: ALICE_PASSWORD
    member_of: [reader]

  support:
    type: role
    privileges:
      columns:                # some columns of a table, not all of it
        select: [crm.customers.id, crm.customers.email]
        update: [crm.customers.email]
```

Connect once with `sesame login`, then plan and apply by name. The password goes
to the operating system's keychain, never to a file; a Redshift target with IAM or
the Data API keeps no secret at all:

```bash
sesame login prod --host db.example.com --user admin --database app   # asks for the password
sesame login analytics --engine redshift --iam --workgroup analytics --database dev
sesame login aurora --iam --rds my-cluster            # RDS or Aurora: an IAM token
sesame login dsql --dsql abc123xyz                    # Aurora DSQL: an IAM token
sesame login app --supabase abcdefghijklmnopqrst  # Supabase: its session pooler
sesame targets                     # the saved targets; * marks the default
sesame use prod                    # the default for plan and apply
sesame plan permissions.yaml --target analytics
sesame logout prod                 # forget it and its password
```

`--supabase` takes a project ref and connects as `postgres` with TLS. With a
personal access token in `SUPABASE_ACCESS_TOKEN`, as the Supabase CLI uses, it
asks Supabase for the project's session pooler, which takes IPv4; without one it
uses the direct connection, `db.<ref>.supabase.co`, which is IPv6 only.

A target can instead take its password from an environment variable when it
connects, so a `.env` file or a CI secret supplies it and nothing is stored:

```bash
sesame login staging --host db.staging.example.com --user admin --password-env STAGING_DB_PASSWORD
uv run --env-file .env sesame plan permissions.yaml -t staging
```

Targets to share go in the project, committed next to the spec: `sesame login
--project` writes `sesame.toml` (or put the same tables under `[tool.sesame]` in
`pyproject.toml`). sesame looks for it from the current directory up to the
repository root; a project target wins over your own of the same name, and your
`sesame use` wins over the project's `default`. A project target takes no typed
password, only `--password-env`, so the file stays free of secrets:

```toml
# sesame.toml
default = "staging"

[targets.staging]
host = "db.staging.example.com"
user = "admin"
database = "app"
sslmode = "require"
password_env = "STAGING_DB_PASSWORD"
```

The same file then serves CI, with `STAGING_DB_PASSWORD` as a repository secret.
`SESAME_DSN` and the standard `PG*` variables keep working too.

```bash
sesame validate permissions.yaml
sesame plan permissions.yaml       # exit code 2 when there are changes
sesame apply permissions.yaml      # revokes, drops and owner changes need --allow-revoke / --allow-drop / --allow-owner

sesame plan permissions.yaml -o changes.json   # save the plan as a change set
sesame show changes.json                       # review it, no database needed
sesame apply changes.json                      # run exactly that, or refuse if it's stale
```

Where migrations change the database, as with Supabase's CLI and preview branches,
a plan can be one: `--migration` writes it to a directory as
`<UTC time>_pgsesame.sql`, for review and `supabase db push`. What apply runs only
with a flag (revokes, drops, owner changes) is in it commented out, with the flag,
and a plan that sets a password is refused, as the file is committed:

```bash
sesame plan permissions.yaml -t app --migration supabase/migrations
```

A plan's statements are green, yellow or red for what they add, change or remove.
On a dark terminal, `SESAME_THEME=workbench` highlights their SQL in SQL
Workbench/J's colors instead (keywords, types, functions, names and strings), and
only the `+`, `~` or `-` marker keeps the plan's color. Output that isn't a
terminal, or with `NO_COLOR` set, has no color either way.

Passwords never go in the spec: name an environment variable with `password_env`,
use IAM, or set `password: disabled`.

On PostgreSQL a role's attributes are declared too: `createdb`, `createrole`,
`replication` and `bypassrls`. Once a principal lists them, it has those and no
others; taking one away needs `--allow-revoke`. A principal that doesn't list
them keeps whatever it has. An admin that isn't a superuser gives only the
attributes it holds itself (PostgreSQL 16+; before, CREATEDB and CREATEROLE), and
the plan notes the rest rather than failing:

```yaml
principals:
  migrator:
    type: user
    attributes: [createdb, createrole]
```

On Redshift a role's system permissions are declared the same way, with `system`:
Redshift grants them to roles only, and checks the names, so any of its permissions
can be listed as GRANT writes it. Its `sys:` roles are built-in, joined with
`member_of`:

```yaml
principals:
  "sys:monitor":
    type: builtin
  auditor:
    type: role
    system: [access system table, create user]
    member_of: ["sys:monitor"]
```

A platform's own roles (Supabase's `authenticated`, RDS's `rds_iam`) are `type:
builtin`: granted to and joined, never created or altered, and their privileges are
managed only in the schemas the spec names for them. PUBLIC, every user, is one too,
named `public`: its grants are planned `TO PUBLIC` and revoked as drift only in
the schemas its privileges name, so its defaults elsewhere (CONNECT on the
database, USAGE on `public`) are left alone:

```yaml
principals:
  public:
    type: builtin
    privileges:
      tables: {select: [reference.countries]}
```

A built-in role's memberships are the platform's too, but for the spec's own
roles it joins (`member_of`, and only those). Supabase's Data API signs in as
`authenticator` and switches to the role a request's JWT names, so a custom role
needs granting to it:

```yaml
principals:
  editor:  # a JWT with "role": "editor"
    type: role
  authenticator:
    type: builtin
    member_of: [editor]
```

Superusers bypass every grant, so pgsesame leaves them alone, unless the spec
says who may be one. Then any other is warned about on every plan, and apply
makes the plan's changes (`NOSUPERUSER` for the unlisted, `SUPERUSER` for the
listed; Redshift's `NOCREATEUSER` and `CREATEUSER`) only with `--allow-superuser`,
which no other flag implies. The connected user stays one (the apply would cut
itself off), and so do the platform's own (`rdsadmin`, `rdsdb`, `supabase_admin`)
and PostgreSQL's bootstrap superuser. Only a superuser changes superusers: on RDS,
Aurora or Supabase, where the admin isn't one, the plan is an audit:

```yaml
manage:
  superusers: [dba]
```

Row-level security is declared per table:

```yaml
principals:
  authenticated:
    type: builtin
    privileges:
      schemas: {usage: [app]}
      tables: {select: [app.notes]}

row_level_security:
  app.notes:
    policies:
      own_notes:
        command: select          # all, select, insert, update, delete
        to: [authenticated]
        using: "auth.uid() = owner"
```

Policy expressions are compared in the form PostgreSQL stores them, so a re-plan
after apply is empty; dropping a policy or disabling row-level security needs
`--allow-drop`. [examples/supabase](examples/supabase) puts it together for a
Supabase app: its built-in roles, column grants, policies on `auth.uid()` and a
dashboard's own login.

Only a table's owner switches its row-level security. On a table the connected
user can't act as the owner of, a change to `enabled` or `force` is warned about
and skipped, and its policies are still planned. That is how Supabase's file
storage works: `storage.objects` belongs to `supabase_storage_admin` with
row-level security on, and `postgres` may add policies to it:

```yaml
row_level_security:
  storage.objects:
    policies:
      avatars_own_folder:  # users/<their id>/... in the avatars bucket
        command: select
        to: [authenticated]
        using: "bucket_id = 'avatars' and (select auth.uid())::text = (storage.foldername(name))[1]"
```

Realtime works the same way: policies on `realtime.messages` authorize Broadcast
and Presence. Which tables send their changes to Postgres Changes subscribers is
the `supabase_realtime` publication's list, which a spec can manage too
(`publications`, on PostgreSQL). pgsesame never creates or drops a publication;
taking a table out needs `--allow-revoke`:

```yaml
publications:
  supabase_realtime:
    tables: [app.posts]  # or app.* for every table in app
```

Where Supabase's Data API reaches rows past row-level security, the plan warns,
whatever the spec says. The API signs in as `authenticator` and switches to one of
its roles (anon, authenticated, a custom JWT role), so anyone with the project's
anon key acts as one. Once the plan has run, a table with row-level security off,
or a view that runs with its owner's rights (no `security_invoker`), that such a
role can read or write is said; so are PUBLIC's grants, which every role holds. A
role that bypasses row-level security (`service_role`) and the platform's own
tables are left out:

```
! public.todos: row-level security is off, and anon (DELETE, INSERT, SELECT, UPDATE) can reach it: every row is open to them
```

Default privileges that give such a role the tables an owner creates are said as
well, since PostgreSQL makes a table with row-level security off. Supabase's own
defaults in `public` did this until October 30, 2026: a new table there now needs
a grant, in the migration that creates it. That is what a spec does, and
`sesame plan --migration` writes the grants into a migration. A spec imported
before then still lists those defaults, and applying it would put them back:
drop them from its `default_privileges`, or import it again.

On PostgreSQL, functions and procedures take EXECUTE. `schema.name` names every
overload, `schema.name(types)` one of them, with the types as PostgreSQL writes
them (`integer`, not `int`), and `schema.*` every function in the schema; an
extension's functions are left out:

```yaml
principals:
  supabase_auth_admin:  # Supabase's Auth hook
    type: builtin
    privileges:
      schemas: {usage: [public]}
      functions: {execute: [public.custom_access_token_hook]}
```

A function the spec names is declared in full: whoever may execute it, declared
or not, is what the spec says. PostgreSQL lets PUBLIC execute every new function,
and Supabase grants anon and authenticated the functions made in `public`; on a
function the spec names, both are drift, revoked with `--allow-revoke`. Functions
it doesn't name keep their grants.

On Redshift, dynamic data masking is declared per column: what everyone sees,
which roles see the raw value, and which see their own mask:

```yaml
masking:
  policies:
    redact: {type: varchar(256), using: "'***'::varchar(256)"}
    email_domain: {type: varchar(256), using: "regexp_replace(value, '^[^@]+', '***')"}
  columns:
    crm.customers.email:
      mask: redact               # everyone else
      unmasked: [pii_reader]     # the raw value
      roles:
        support: email_domain    # their own mask; later entries win
```

pgsesame works out Redshift's mechanics: the mask is attached to PUBLIC at
priority 10, each role's policy at 20, 30 ... in the order written, and the
unmasked roles get a pass-through policy of pgsesame's own
(`sesame_unmasked_varchar_256`) at 1000. A constant needs its type (`'***'::varchar(256)`): Redshift
refuses an expression of ambiguous type. Expressions are compared in the form
Redshift stores them; moving a role's priority is a change, taking a role off a
column needs `--allow-revoke`, and a policy whose type changes is replaced only
with `--allow-drop`. Planning masking needs a superuser or the `sys:secadmin`
role, as Redshift shows policies to no one else.

Priorities are compared by order, not number: where a column's attachments
already rank everyone as the spec does (the mask below the roles, the roles in
the order written, the unmasked above), the plan keeps the database's numbers.
Expressions are compared in the form Redshift stores them, read back from probe
policies: rolled back over a direct connection; over the Data API created, read
and dropped in the same plan (never attached), which needs
`redshift-data:BatchExecuteStatement`.

Ownership is declared on the owner, and planned as `ALTER ... OWNER TO` for the
objects it lists (`schema.*` for every table in a schema). Objects the spec
doesn't list keep their owner; an owner's privileges on its own objects are
implied, so they're neither granted nor revoked. A change of owner takes from the
old owner everything owning gave it, so apply runs it only with `--allow-owner`;
without it, the plan shows it and apply skips it. A superuser's memberships and
privileges are left alone (it bypasses them), but what it owns is planned like
any other owner's. pgsesame never drops a schema,
table, view, user, group or role. On Redshift the owner is a user:

```yaml
principals:
  etl:
    type: user
    owns:
      schemas: [analytics]
      tables: [analytics.*]
```

Default privileges give a role what an owner creates from now on, so a table
made overnight is readable in the morning. The owner needn't be in the spec (it's
often the ETL or admin user); pgsesame manages the entries whose grantee it does:

```yaml
default_privileges:
  - owner: etl               # objects etl creates ...
    schema: analytics        # ... in this schema (leave out for every schema) ...
    grantee: analyst         # ... are readable by analyst
    tables: [select]
```

On Redshift an entry for one schema takes only if its owner may create there
(directly, through a group or role, or as the schema's owner): Redshift refuses
it otherwise, even to a superuser. The plan warns about such an entry and skips
it, so the rest still applies.

### Tables that are rebuilt

A grant belongs to one table, so a tool that rebuilds tables (dbt's table
materialization, Glue, a stored procedure) drops its grants with the old one.
`sesame grants` lists what the spec grants, from the spec alone, for such a tool
to put back right after a rebuild, without parsing the YAML itself:

```bash
sesame grants permissions.yaml --object bianalytics.loans          # JSON
sesame grants permissions.yaml --format csv > declared_grants.csv  # every grant
```

Each row is `object_type`, `schema`, `object` (`*` for `schema.*`, as the spec
writes it), `column`, `privilege`, `grantee` and `grantee_type` (on Redshift user,
group or role, which decides the `GRANT` syntax). `--object schema.table` keeps
the rows that reach that table: its own and its schema's `*`.

[examples/dbt](examples/dbt) is the pattern for dbt: after each apply,
`publish_grants.py` loads `sesame grants --format csv` into
`monitoring.declared_grants`, and one shared post-hook, `pgsesame_regrant()`,
grants a model's rows right after it builds, in the same transaction, skipping
grantees that don't exist. dbt's own `grants:` config would revoke everything
else on each build; this only adds back what the spec declares.
`tests/test_dbt.py` runs it with dbt against redshift-local.

### Adopting an existing database

`sesame import` writes the spec that reproduces what the database grants today,
so a first plan has nothing to do, and the spec is edited from there. On Redshift
it needs to see every grant: Redshift shows a user only its own, unless it is a
superuser or holds the `ACCESS SYSTEM TABLE` system permission, and a spec missing
the rest would have a plan revoke them, so import refuses rather than write it.
The permission is the least-privileged way, through a role:

```sql
CREATE ROLE sesame_reader;
GRANT ACCESS SYSTEM TABLE TO ROLE sesame_reader;
GRANT ROLE sesame_reader TO "IAM:deployer";
```

Plan and apply without it say so before anything else:

```bash
sesame import --target dwh --schema collections --schema risk_engine -o permissions.yaml
sesame plan permissions.yaml --target dwh        # ✓ nothing to do
```

It writes every role but superusers and the platform's own (or only those named
with `--prefix`), their memberships, grants, column grants, default privileges,
ownership and, on Redshift, masking; never passwords. Masking is written in
pgsesame's model (the PUBLIC mask, roles in priority order, unmasked roles), so
where policies were attached another way, the first plan shows the correction;
the import's notes say what changes. Without superuser or `sys:secadmin` it says
it couldn't see the policies, rather than writing none. A role they refer to but that wasn't selected is
written as `type: builtin`: referred to, never managed. So is every other role
that may execute a function the import names, PUBLIC included, with its grants in
the same schemas, since a plan takes every grant on a named function as declared.

On Supabase, the platform stays the platform's. Its roles are `type: builtin`:
anon, authenticated and service_role with their grants in the app's schemas, and
authenticator with the app's roles it joins (custom JWT roles). Its schemas (auth,
storage, realtime, extensions ...) and the default privileges of roles postgres
can't act as are left out, unless `--schema` names one. The flags become the
spec's `manage:` section, which also works on its own:

```yaml
manage:
  schemas: [collections, risk_engine]   # grants elsewhere aren't compared (no drift)
  prefixes: [svc_]                       # undeclared svc_* roles are managed too:
                                         # what they hold is revoked with --allow-revoke
```

A first plan creates the roles and grants the spec declares:

![sesame plan: the roles and grants a spec needs](https://raw.githubusercontent.com/almostly/pgsesame/main/docs/images/plan.png)

Later, two grants someone made by hand show up as drift; they are revoked only
with `--allow-revoke`:

![sesame plan: drift, planned as revokes](https://raw.githubusercontent.com/almostly/pgsesame/main/docs/images/drift.png)

A saved change set can be reviewed without a database, then applied exactly:

![sesame show: a saved change set](https://raw.githubusercontent.com/almostly/pgsesame/main/docs/images/change-set.png)

## In GitHub Actions

Review the plan on the pull request; apply the reviewed change set on merge. The
connection comes from a secret (`SESAME_DSN`), and the `production` environment
can require a reviewer's approval before apply runs.

```yaml
on:
  pull_request:
  push:
    branches: [main]

jobs:
  plan:
    runs-on: ubuntu-latest
    permissions: {contents: read, pull-requests: write}
    env: {SESAME_DSN: "${{ secrets.SESAME_DSN }}"}
    steps:
      - uses: actions/checkout@v4
      - uses: almostly/pgsesame@v0.4.0
        with: {command: plan, spec: permissions.yaml}

  apply:
    if: github.event_name == 'push'
    needs: plan
    runs-on: ubuntu-latest
    environment: production
    env: {SESAME_DSN: "${{ secrets.SESAME_DSN }}"}
    steps:
      - uses: almostly/pgsesame@v0.4.0
        with: {command: apply}
```

On a pull request the plan is posted as one comment, updated on each push. On
merge, `apply` runs exactly the change set the plan job saved, or refuses if the
database changed since. Revokes need `allow-revoke: true`, owner changes
`allow-owner: true`. The step's outputs
(`has-changes`, `to-add`, `to-change`, `to-remove`) can drive other steps. For
Redshift with IAM, sign in with `aws-actions/configure-aws-credentials` and pass
`args: --iam --workgroup analytics`.

Outside GitHub, run `uvx pgsesame` or the image
(`docker run --rm -v "$PWD:/work" -e SESAME_DSN ghcr.io/almostly/pgsesame plan
permissions.yaml`); `plan` exits 2 when it has changes.

## Testing locally

The integration tests run against PostgreSQL in Docker and against
[oblako](https://github.com/almostly/oblako)'s local Redshift, so a spec can be
planned and applied in CI before it touches a real cluster.

## License

Apache-2.0
