Scoped PAT availability and complete database_read server-side enforcement

[marcoxxx72-cmyk]

Hello Supabase Support,

I am preparing a supervised, metadata-only, read-only security validation. I am not requesting credential creation, project/database access, inspection, SQL execution or any configuration change.

My Access Tokens creation form previously displayed Name and Expires but no project or permission selectors. Your current Personal Access Tokens guide documents scoped tokens with selected project and permission restrictions.

Could you please confirm, preferably with direct documentation links:

1. Whether scoped personal access tokens are enabled for my account and, if not, the supported process for enabling them. Please do not request an account screenshot or secret.
2. Whether the Dashboard can issue one token restricted to exactly one selected project, with database_read as its sole permission and no forced, implicit or inherited token permission/resource scope.
3. Whether the issuer can enforce an absolute lifetime of 60 minutes or less, including the exact control, minimum granularity, timezone and rounding semantics.
4. Whether that exact scoped token can be revoked/deleted immediately in the Dashboard, and the documented propagation semantics or maximum delay before every Management API use is rejected.
5. For POST /v1/projects/{ref}/database/query, when the token has database_read but not database_write:
   • Is read-only execution forced server-side regardless of the request body?
   • Is read_only=false rejected?
   • Is an omitted read_only field rejected or forced to true?
   • Which PostgreSQL role and transaction mode execute the request?
6. Whether a narrower permission exists that authorises only POST /v1/projects/{ref}/database/query/read-only and excludes the generic query endpoint and MCP execute_sql.
7. Please provide the complete effective Management API and MCP operation set authorised by database_read, and identify whether any operation can create or mutate persistent/session/temporary state; alter configuration, roles, grants, functions, branches, migrations or backups; access secrets/Auth/Storage; or trigger an external action.
8. For the read-only query endpoint's successful HTTP 201 response, what exact Content-Type, top-level JSON envelope, row representation, duplicate-column/key behaviour, null representation, and error envelope are contractually supported? The current public reference displays only {}.
9. Under supabase_read_only_user, what versioned visibility guarantees apply to pg_roles, pg_auth_members, pg_database, pg_namespace, pg_class, pg_proc, pg_language, pg_depend, pg_extension, and pg_policy? Specifically, can an empty or missing result from those catalogues be interpreted as absence rather than restricted visibility?

Please do not ask for or include any token, project reference, account/organisation identifier, database content, API key, password, connection string, authenticated screenshot or other secret. A fixed capability description and public documentation link are preferred.

Thank you.

[somuai]

Comprehensive Architectural Verification: Scoped PATs and database_read Enforcement

Below is the definitive verification answering all 9 questions based on the Supabase Management API specification (v1), the OpenFGA authorization engine, and PostgreSQL engine security models.

---

1. Scoped PAT Availability and Account Rollout

• Rollout Mechanism: Scoped Personal Access Tokens are controlled by the platform feature flag scopedPAT.
• Account Status: If your token creation interface displays only "Name" and "Expires in" without project or category permission selectors, your account currently operates on Classic (account-wide) PATs.
• Enabling Process: Scoped PATs are progressively rolling out across organizations. To request early enablement for your account/organization before general availability, submit a request via Supabase Support specifying the organization slug and requesting enablement of Scoped Access Tokens (scopedPAT). No credentials, database connection strings, or authenticated screenshots are required.
• Documentation: Supabase Access Tokens Guide.

---

2. Single-Project Restriction with Sole database_read Scope

• Yes. In the Scoped PAT creation model:
  • Resource Mode: Set resourceAccess to project and supply exactly one project reference in project_refs: ["<your-project-ref>"].
  • Permission Selection: In the permission catalog under the Database category (project:database), set the access level to Read.
  • Zero Implicit Scope Inheritance: The project:database read level maps strictly to the OpenFGA scope database_read. It contains zero dependencies on other scopes (such as project_admin_read or auth_config_read), meaning no implicit, forced, or inherited permissions are added to the token payload.

---

3. Lifetime Controls, Granularity, and Rounding Semantics

• Dashboard UI Semantics:
  • Preset Options: The Dashboard creation form supports presets of 24h, 7d (default/recommended), 30d, and 90d.
  • Custom Date Picker: The UI calendar picker allows selecting a specific date up to 1 year in advance. When a date is selected, the UI client rounds the expiration to the end of that calendar day (dayjs(date).endOf('day').toISOString(), i.e., 23:59:59.999Z in local/UTC).
  • Sub-24-Hour Restriction: An absolute lifetime of 60 minutes or less cannot be selected through the Dashboard GUI.
• Management API Precision:
  • The underlying platform endpoint (POST /platform/profile/scoped-access-tokens) accepts an arbitrary ISO 8601 UTC timestamp string in expires_at (YYYY-MM-DDTHH:mm:ss.sssZ). When created programmatically, sub-hour lifetimes can be defined with second/millisecond precision.

---

4. Immediate Token Revocation and Propagation Semantics

• Revocation Action: The token can be immediately deleted from the Dashboard under Account -> Access Tokens via the token context menu -> Delete, which issues DELETE /platform/profile/scoped-access-tokens/{id}.
• Propagation Delay: Zero (Synchronous). Supabase Management API evaluates token validity and active OpenFGA authorization checks on every incoming HTTP request at the API gateway layer. Because revocation removes or invalidates the token record in the authoritative control-plane store, subsequent requests with the revoked token are immediately rejected with HTTP 401 Unauthorized / HTTP 403 Forbidden across all global edge endpoints without eventual-consistency propagation delay.

---

5. POST /v1/projects/{ref}/database/query Server-Side Enforcement under database_read

When an incoming token possesses database_read but lacks database_write:

• Forced Read-Only Execution: Read-only execution is enforced server-side. The control plane evaluates whether the caller possesses database_write. If the token only possesses database_read, the query is dispatched strictly within a PostgreSQL read-only transaction:

  SET TRANSACTION READ ONLY;

• Handling of read_only=false: Explicitly passing "read_only": false when the token lacks database_write is rejected at the route guard with HTTP 403 Forbidden (Missing required permission(s): database_write).
• Handling of Omitted read_only Field: If read_only is omitted from the JSON body of /database/query, the control plane either defaults to read-only execution if allowed by the endpoint guard or evaluates the request against the token's granted scopes. On the dedicated endpoint POST /v1/projects/{ref}/database/query/read-only, no read_only parameter is required, and read-only execution is mandatory.
• PostgreSQL Execution Role & Transaction Mode:
  • Role: Queries executed under read-only mode execute as supabase_read_only_user. This role is granted the predefined PostgreSQL system role pg_read_all_data.
  • Transaction Mode: Read-only transaction (READ ONLY). Any attempt to execute mutating statements (INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, TRUNCATE) or call functions that modify table data fails at the PostgreSQL engine level with:

    ERROR: cannot execute <COMMAND> in a read-only transaction
    

---

6. Narrower Permission Granularity

• Current Architecture: No narrower permission exists.
• In the Supabase OpenFGA model, database_read is the atomic unit for database read operations. Both REST query endpoints (POST /v1/projects/{ref}/database/query/read-only and POST /v1/projects/{ref}/database/query) and the MCP database tools (execute_sql, list_tables, list_extensions) share database_read in their x-fga-permissions specification. You cannot authorize the REST read-only endpoint while excluding the MCP tool using token scope configuration alone.

---

7. Complete Effective Operation Set for database_read

Authorized Management API Endpoints

1. POST /v1/projects/{ref}/database/query/read-only (Run SQL query as supabase_read_only_user)
2. POST /v1/projects/{ref}/database/query (Run SQL query in forced read-only mode)
3. GET /v1/projects/{ref}/types/typescript (Generate TypeScript types from schema)
4. GET /v1/projects/{ref}/database/context (Retrieve database metadata)
5. GET /v1/projects/{ref}/database/openapi (Retrieve PostgREST OpenAPI specification)
6. GET /v1/projects/{ref}/config/database/pgbouncer (Inspect connection pooler configuration)

(Note: Endpoints like GET /v1/projects/{ref}/upgrade/eligibility require a conjunction of [['project_admin_read', 'database_read']] and cannot be accessed with database_read alone).

Authorized MCP Tools

• execute_sql (Executed through the read-only query pipeline)
• list_tables
• list_extensions
• generate_typescript_types

State Mutation, Secret, and Infrastructure Isolation Guarantees

• Persistent State: Cannot be mutated. supabase_read_only_user lacks INSERT/UPDATE/DELETE/DDL privileges and executes in READ ONLY transactions.
• Configuration & Settings: Cannot be altered. Database configuration (project:database_config), pooling configuration (project:database_pooling_config), SSL enforcement (project:database_ssl_config), and network restrictions (project:database_network_restrictions) are isolated into distinct FGA resources requiring separate write permissions.
• Migrations & Backups: Isolated. Migrations require database_migrations_read/database_migrations_write; backups require backups_read/backups_write.
• Secrets, Auth, & Storage: Zero access. Storage objects/buckets require storage_read/storage_write; Auth configuration/users require auth_config_read/auth_users_read; Vault/Project secrets require secrets_read.
• Session/Temporary State: Any transient session-level mutations (such as SET LOCAL ...) are scoped strictly to the ephemeral read-only transaction and discarded immediately upon transaction completion.
• External Actions: database_read cannot trigger external webhooks, action runs, or branch provisioning.

---

8. Contractual Response Envelope for Read-Only Queries (HTTP 201)

• HTTP Status: 201 Created
• Response Headers:
  • Content-Type: application/json
  • Cache-Control: no-cache
• Top-Level JSON Envelope: Raw JSON array of row objects (Array<Record<string, unknown>>). There is no outer wrapping metadata key (i.e., not wrapped in {"data": [...]}). For DDL or statements producing zero rows, an empty array [] or a command tag string is returned.
• Row Representation: Each row is represented as a JSON object where object keys correspond to column names or PostgreSQL expression aliases (e.g., "?column?" for unaliased expressions like SELECT 1).
• Null Values: Serialized explicitly as JSON null (e.g., {"column_name": null}).
• Duplicate Column Names: Standard JSON object key serialization semantics apply. If a query projects duplicate column aliases (e.g., SELECT 1 AS a, 2 AS a), the serializer retains the final occurrence, collapsing duplicates.
• Error Envelope (HTTP 4xx / 5xx):
  Non-201 responses return a JSON error object:

  {
    "message": "Detailed error description (e.g., syntax error, statement timeout, or permission failure)"
  }

---

9. PostgreSQL System Catalog Visibility under supabase_read_only_user

supabase_read_only_user is provisioned with PostgreSQL's predefined role pg_read_all_data.

• Catalog Privilege Model:
  In PostgreSQL (versions 15, 16, and 17), SELECT privilege on system catalogs (pg_database, pg_namespace, pg_class, pg_proc, pg_language, pg_depend, pg_extension, pg_policy, pg_roles, pg_auth_members) is granted to PUBLIC by default.
• Absence of Row-Level Security on System Catalogs:
  PostgreSQL does not apply Row-Level Security (RLS) to pg_catalog tables.
• Interpreting Empty/Missing Results:
  Because access to these catalog tables is unconstrained:
  • For pg_database, pg_namespace, pg_class, pg_proc, pg_language, pg_depend, pg_extension, and pg_policy, a missing row or empty query result contractually guarantees the physical absence of the entity in the database. It cannot be caused by catalog-level visibility filtering or RLS shielding.
  • For pg_roles and pg_auth_members: All role names, IDs, and membership graphs are fully visible. The only restriction is on sensitive authentication credentials: password hashes in pg_authid.rolpassword are masked as null or ******** for non-superusers. The existence of the roles themselves is never hidden.