Supported PostgreSQL role / ownership / recovery model for Supabase Hosted projects

[musashi1002m-cmd]

We are preparing a safe PostgreSQL ownership and privilege lifecycle for a Supabase Hosted STAGING project.

We have only performed read-only investigation so far. No CREATE ROLE, ALTER ROLE, GRANT, REVOKE, OWNER changes, migrations, or other mutations have been executed.

We need guidance specifically for Supabase Hosted Postgres, not general PostgreSQL.

Questions:

1. Custom roles
   Are these officially supported?

• CREATE ROLE from the postgres login role
• SET ROLE to a custom role
• Creating a temporary ownership-transfer role and dropping it afterward

2. Persistent application/lifecycle owner
   Is it supported to create a custom persistent owner role with:

• NOSUPERUSER
• NOBYPASSRLS
• NOCREATEROLE
• NOCREATEDB
• NOINHERIT

and use it as the owner of user-created schemas and objects?

Is changing the database owner or public schema owner to a custom role supported?

3. Ownership operations
   Are the following supported in Supabase Hosted projects?

• ALTER DATABASE ... OWNER TO ...
• ALTER SCHEMA public OWNER TO ...
• ALTER TABLE / SEQUENCE / FUNCTION ... OWNER TO ...
• REASSIGN OWNED
• GRANT role TO role
• REVOKE role FROM role

4. Post-COMMIT recovery
   If ownership, memberships, role attributes, ACLs, or default privileges are changed and a problem is discovered after COMMIT, what is the supported recovery mechanism?

Is there a specific PostgreSQL role that can restore these states?
If so, what role should be used and what operations can it perform?

5. Supabase-managed roles
   Should these roles remain managed by Supabase and never be reused as custom application ownership/lifecycle roles?

• authenticator
• service_role
• supabase_admin
• dashboard_user
• supabase_auth_admin
• supabase_storage_admin

Are there any other managed roles whose attributes, ownership, or memberships should not be modified?

6. Recommended ownership / ACL model
   For user-created schemas, tables, sequences, and functions, what ownership / ACL / default privilege model is recommended for Supabase Hosted projects?

We ultimately need four authority roles/responsibilities:

• bootstrap/provider authority
• temporary ownership-transfer authority
• persistent application lifecycle owner
• post-COMMIT recovery authority

If existing Supabase roles should be used for any of these, please provide the concrete role names.

If custom roles should be created instead, please describe the recommended attributes.

No passwords, API keys, access tokens, connection strings, or other secrets are included.

[k1ng-arthur]

This might not answer all of your questions but you:

1. Can create new roles with the outlines permissions judging by https://supabase.com/dashboard/project/_/database/roles?new=true
2. You can also modify the role in the SQL dashboard to have properties like noinherit https://supabase.com/docs/guides/database/postgres/roles
3. Anything that has _admin at the end is usually a platform role that you can't modify it nor can you modify any of the resources they own/created. Likewise any resources owner by supabase_admin be it schemas/tables can't be transferred. I think things in public might be fine.

As for the rest of your points, i'm not sure. Perhaps someone else might know

[musashi1002m-cmd]

Thanks — this helps confirm custom roles and the managed-role boundary. The remaining point we need to resolve before making any changes is the recovery/ownership side. Could you clarify, if you know: 1. Is changing the database owner or `public` schema owner to a custom role supported on Supabase Hosted? 2. Are `ALTER DATABASE ... OWNER TO ...`, `ALTER SCHEMA ... OWNER TO ...`, and `REASSIGN OWNED` supported? 3. After COMMIT, what role/mechanism can restore ownership, role memberships, ACLs, and default privileges if something goes wrong? 4. Is the `postgres` login role the supported recovery authority for user-created objects, or is there another recommended role/mechanism? 5. For user-created application schemas/objects, is a custom NOINHERIT/NOSUPERUSER/NOBYPASSRLS/NOCREATEROLE/NOCREATEDB owner role a supported pattern? We specifically want to avoid modifying Supabase-managed roles or objects. 2026年9月15日(火) 16:47 Ibrahim ***@***.***>:

…

This might not answer all of your questions but you: 1. Can create new roles with the outlines permissions judging by https://supabase.com/dashboard/project/_/database/roles?new=true 2. You can also modify the role in the SQL dashboard to have properties like noinherit https://supabase.com/docs/guides/database/postgres/roles 3. Anything that has _admin at the end is usually a platform role that you can't modify it nor can you modify any of the resources they own/created. Likewise any resources owner by supabase_admin be it schemas/tables can't be transferred. I think things in public might be fine. As for the rest of your points, i'm not sure. Perhaps someone else might know — Reply to this email directly, view it on GitHub <#50379?email_source=notifications&email_token=COQWLDQH6WX4DF6F46QJ6NL5PDXZVA5CNFSNUABIM5UWIORPF5TWS5BNNB2WEL2ENFZWG5LTONUW63SDN5WW2ZLOOQXTCOBUGQ2TSNZUUZZGKYLTN5XKMYLVORUG64VFMV3GK3TUVRTG633UMVZF6Y3MNFRWW#discussioncomment-18445974>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/COQWLDWBWBHY2WGT7VLWSLT5PDXZVAVCNFSNUABIKJSXA33TNF2G64TZHMZDCNBVHA3TCOJTHNCGS43DOVZXG2LPNY5TCMBYGE3TANJUUF3AE> . Triage notifications, keep track of coding agent tasks and review pull requests on the go with GitHub Mobile for iOS <https://github.com/notifications/mobile/ios/COQWLDUQF2GJJACAC4NE4PT5PDXZVA5CNFSNUABIM5UWIORPF5TWS5BNNB2WEL2ENFZWG5LTONUW63SDN5WW2ZLOOQXTCOBUGQ2TSNZUUZZGKYLTN5XKMYLVORUG64VFMV3GK3TUVJTG633UMVZF62LPOM> and Android <https://github.com/notifications/mobile/android/COQWLDTLVOES7CJN3HLGS2T5PDXZVA5CNFSNUABIM5UWIORPF5TWS5BNNB2WEL2ENFZWG5LTONUW63SDN5WW2ZLOOQXTCOBUGQ2TSNZUUZZGKYLTN5XKMYLVORUG64VFMV3GK3TUVZTG633UMVZF6YLOMRZG62LE>. Download it today! You are receiving this because you authored the thread.Message ID: ***@***.***>

[somuai]

Here are direct, concrete clarifications for your 5 questions regarding ownership, privilege boundaries, and recovery on hosted Supabase:

---

1. Database and public Schema Ownership

• Database owner: ALTER DATABASE postgres OWNER TO <custom_role> is not supported (fails with permission denied because postgres is not a superuser and does not own the database; the database is owned by supabase_admin).
• public schema owner: While postgres owns the public schema on standard projects, changing its owner (ALTER SCHEMA public OWNER TO ...) is strongly discouraged. Internal Supabase migration scripts, platform extensions, and dashboard features rely on postgres possessing ownership and full privileges over public. The standard practice is to leave public owned by postgres and create your own dedicated application schemas (e.g. CREATE SCHEMA app; ALTER SCHEMA app OWNER TO app_owner;).

---

2. Supported Scope of Ownership Operations

• ALTER DATABASE ... OWNER TO ...: Blocked (requires superuser).
• ALTER SCHEMA <user_schema> OWNER TO <role>: Supported for user-created schemas owned by postgres.
• ALTER TABLE / SEQUENCE / FUNCTION ... OWNER TO ...: Supported for all objects created and owned by postgres or roles granted to postgres.
• REASSIGN OWNED BY <role1> TO <role2>: Supported, provided:
  • Both roles are user-created roles or postgres.
  • The executing role (postgres) is a member of both roles (GRANT role1 TO postgres; GRANT role2 TO postgres;).
  • Cannot be run against platform roles (supabase_admin, supabase_auth_admin, supabase_storage_admin).

---

3. Post-COMMIT Recovery Mechanism

In PostgreSQL, transactional DDL allows ROLLBACK during an active transaction, but once COMMIT has executed, Postgres has no native "undo" for DDL, ACLs, or role modifications.
On hosted Supabase, the supported recovery mechanisms are:

1. Idempotent Migration Scripts / Rollback Migrations: Maintain explicit reverse scripts for every GRANT, REVOKE, and OWNER change in your version-controlled migrations repository (e.g. via Supabase CLI migrations).
2. Point-in-Time Recovery (PITR) / Backups: If catalog state or ownership is broken past manual recovery, PITR (available on Pro/Team/Enterprise plans) or database restoration from a daily backup snapshot is the official platform recovery boundary.
3. Database Branching: Always test role and ownership migrations on a Supabase Preview/Branch environment before applying them to Staging or Production.

---

4. Recovery Authority Role

Yes, postgres is the supported recovery authority for all customer-created schemas, tables, roles, and privileges.

• On hosted Supabase, postgres is equipped with CREATEROLE and CREATEDB.
• As long as you maintain the membership bridge (GRANT <custom_role> TO postgres;), postgres can always re-grant, revoke, reassign ownership, or drop customer roles and objects.
• Do not revoke postgres's membership from your custom roles, as that locks postgres out of managing those objects.

---

5. Custom Least-Privilege Role Model

Yes, creating a custom application owner role with those exact attributes is supported and recommended for custom schemas:

CREATE ROLE app_owner WITH
  LOGIN
  NOSUPERUSER
  NOBYPASSRLS
  NOCREATEROLE
  NOCREATEDB
  NOINHERIT
  PASSWORD '...';

-- Essential: allow postgres to manage app_owner objects and migrations
GRANT app_owner TO postgres;

-- Isolate into custom application schema
CREATE SCHEMA app AUTHORIZATION app_owner;

By keeping this pattern scoped to your custom schema (app) and granting membership to postgres, you achieve true least privilege for runtime connections without risking breakages to public or Supabase-managed internal schemas (auth, storage, vault, extensions).

If this clarifies the ownership lifecycle and recovery boundaries, please feel free to mark this as the accepted answer.

[musashi1002m-cmd]

Hello, I have one additional technical question regarding Supabase Hosted projects. When using the Supabase Shared Session Pooler (Supavisor) in Session mode, is the connection between: Supavisor / Shared Pooler → PostgreSQL encrypted with TLS in the managed Supabase environment? We have confirmed that our client connection to the Shared Pooler uses sslmode=verify-full, but PostgreSQL pg_stat_ssl reports ssl = false for the backend connection. Could you please clarify: 1. Whether TLS is normally used between the managed Shared Pooler and PostgreSQL. 2. If TLS is not used, how that internal connection is isolated or otherwise protected. 3. Whether the upstream_ssl setting is controlled by Supabase for hosted projects, or whether customers can view or configure it. We are not asking to change any settings at this time. We only need to understand the supported security model for the managed Shared Pooler. Thank you. On Tue, 15 Sep 2026 20:55:24 -0700, Soumyajit Ghosh ***@***.*** wrote: Here are direct, concrete clarifications for your 5 questions regarding ownership, privilege boundaries, and recovery on hosted Supabase: 1. Database and public Schema Ownership - Database owner: ALTER DATABASE postgres OWNER TO <custom_role> is not supported (fails with permission denied because postgres is not a superuser and does not own the database; the database is owned by supabase_admin). - public schema owner: While postgres owns the public schema on standard projects, changing its owner (ALTER SCHEMA public OWNER TO …) is strongly discouraged. Internal Supabase migration scripts, platform extensions, and dashboard features rely on postgres possessing ownership and full privileges over public. The standard practice is to leave public owned by postgres and create your own dedicated application schemas (e.g. CREATE SCHEMA app; ALTER SCHEMA app OWNER TO app_owner;). 1. Supported Scope of Ownership Operations - ALTER DATABASE … OWNER TO …: Blocked (requires superuser). - ALTER SCHEMA <user_schema> OWNER TO : Supported for user-created schemas owned by postgres. - ALTER TABLE / SEQUENCE / FUNCTION … OWNER TO …: Supported for all objects created and owned by postgres or roles granted to postgres. - REASSIGN OWNED BY TO : Supported, provided: - Both roles are user-created roles or postgres. - The executing role (postgres) is a member of both roles (GRANT role1 TO postgres; GRANT role2 TO postgres;). - Cannot be run against platform roles (supabase_admin, supabase_auth_admin, supabase_storage_admin). 1. Post-COMMIT Recovery Mechanism In PostgreSQL, transactional DDL allows ROLLBACK during an active transaction, but once COMMIT has executed, Postgres has no native “undo” for DDL, ACLs, or role modifications. On hosted Supabase, the supported recovery mechanisms are: - Idempotent Migration Scripts / Rollback Migrations: Maintain explicit reverse scripts for every GRANT, REVOKE, and OWNER change in your version-controlled migrations repository (e.g. via Supabase CLI migrations). - Point-in-Time Recovery (PITR) / Backups: If catalog state or ownership is broken past manual recovery, PITR (available on Pro/Team/Enterprise plans) or database restoration from a daily backup snapshot is the official platform recovery boundary. - Database Branching: Always test role and ownership migrations on a Supabase Preview/Branch environment before applying them to Staging or Production. 1. Recovery Authority Role Yes, postgres is the supported recovery authority for all customer-created schemas, tables, roles, and privileges. - On hosted Supabase, postgres is equipped with CREATEROLE and CREATEDB. - As long as you maintain the membership bridge (GRANT <custom_role> TO postgres;), postgres can always re-grant, revoke, reassign ownership, or drop customer roles and objects. - Do not revoke postgres’s membership from your custom roles, as that locks postgres out of managing those objects. 1. Custom Least-Privilege Role Model Yes, creating a custom application owner role with those exact attributes is supported and recommended for custom schemas: CREATE ROLE app_owner WITH LOGIN NOSUPERUSER NOBYPASSRLS NOCREATEROLE NOCREATEDB NOINHERIT PASSWORD ‘…’; – Essential: allow postgres to manage app_owner objects and migrations GRANT app_owner TO postgres; – Isolate into custom application schema CREATE SCHEMA app AUTHORIZATION app_owner; By keeping this pattern scoped to your custom schema (app) and granting membership to postgres, you achieve true least privilege for runtime connections without risking breakages to public or Supabase-managed internal schemas (auth, storage, vault, extensions). If this clarifies the ownership lifecycle and recovery boundaries, please feel free to mark this as the accepted answer. — Reply to this email directly, view it on GitHub <#50379?email_source=notifications&email_token=COQWLDWZHRVGWBPF52UJNN35PIFKZA5CNFSNUABIM5UWIORPF5TWS5BNNB2WEL2ENFZWG5LTONUW63SDN5WW2ZLOOQXTCOBUGU4DQOJYUZZGKYLTN5XKMYLVORUG64VFMV3GK3TUVRTG633UMVZF6Y3MNFRWW#discussioncomment-18458898>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/COQWLDVU34RERQRTNCWQJPL5PIFKZAVCNFSNUABIKJSXA33TNF2G64TZHMZDCNBVHA3TCOJTHNCGS43DOVZXG2LPNY5TCMBYGE3TANJUUF3AE> . Triage notifications, keep track of coding agent tasks and review pull requests on the go with GitHub Mobile for iOS <https://github.com/notifications/mobile/ios/COQWLDSBT5PLTSPDZ4NM5UT5PIFKZA5CNFSNUABIM5UWIORPF5TWS5BNNB2WEL2ENFZWG5LTONUW63SDN5WW2ZLOOQXTCOBUGU4DQOJYUZZGKYLTN5XKMYLVORUG64VFMV3GK3TUVJTG633UMVZF62LPOM> and Android <https://github.com/notifications/mobile/android/COQWLDQD6VQVVFB3K7HCJZL5PIFKZA5CNFSNUABIM5UWIORPF5TWS5BNNB2WEL2ENFZWG5LTONUW63SDN5WW2ZLOOQXTCOBUGU4DQOJYUZZGKYLTN5XKMYLVORUG64VFMV3GK3TUVZTG633UMVZF6YLOMRZG62LE>. Download it today! You are receiving this because you authored the thread.

[somuai]

Hi @musashi1002m-cmd,

Your observation regarding pg_stat_ssl returning ssl = false for pooler connections is accurate. Below is the technical breakdown of the architecture, isolation mechanisms, and compliance alternatives.

---

Summary Answers

1. Is TLS used between the managed Shared Pooler (Supavisor) and PostgreSQL?
   No. In the managed Supabase environment, connections between Supavisor and the upstream PostgreSQL engine are established with upstream_ssl = false. Therefore, pg_stat_ssl correctly reports ssl = false for these backend pooler worker connections.
2. How is the internal connection isolated and protected if TLS is not used?
   The connection traverses dedicated private virtual interfaces inside an AWS VPC. Traffic is protected by AWS Security Groups, hypervisor network isolation, dedicated single-tenant database compute boundaries, and cryptographic authentication using SCRAM-SHA-256 (passwords are never transmitted in cleartext).
3. Is upstream_ssl customer-configurable or platform-controlled?
   It is strictly platform-managed. Supabase’s internal fleet orchestrator provisions and manages low-level proxy templates. Customers cannot view or toggle upstream_ssl via the Dashboard or Management API.

---

Detailed Architecture & Technical Analysis

1. Supavisor Upstream Architecture

In a managed project, connections follow a two-tier boundary:

• Tier 1: Client to Supavisor (Edge / Ingress)
  Clients connect to Supavisor via port 6543 (or port 5432 on pooler domains). TLS is enforced at this ingress point (sslmode=require or higher). External traffic traversing the public internet is encrypted using TLS 1.3 / TLS 1.2 with modern cipher suites.
• Tier 2: Supavisor to PostgreSQL (Upstream Backend)
  Supavisor maintains persistent backend pools directly to Postgres worker processes over internal private network paths.

Why upstream_ssl is disabled internally:

• Throughput and Latency: Terminating a second TLS layer over an internal loopback or private software-defined network (SDN) incurs CPU overhead and increases packet serialization/deserialization times without adding perimeter defense inside an already isolated VPC boundary.
• Connection Multiplexing: Supavisor pools and multiplexes thousands of incoming client sessions into a smaller, fixed set of upstream Postgres connections. Maintaining plain connections over local private links ensures minimal resource consumption for high-concurrency database workloads.

---

2. Network Isolation & Perimeter Controls

Because TLS is disabled between Supavisor and Postgres, the link relies on multi-layer perimeter and protocol-level controls:

• AWS VPC Private Isolation:
  Postgres instances operate inside an Amazon Web Services (AWS) Virtual Private Cloud (VPC). The upstream Postgres port (5432) is not directly exposed on an unauthenticated public interface. Internal traffic between the pooler tier and the database engine moves across private AWS hypervisor network virtualization layers (AWS Nitro / VPC private subnets).
• Security Groups and Packet Filtering:
  Strict ingress rules enforce that port 5432 on the database instance only accepts incoming TCP packets originating from authorized internal proxy IP ranges and internal control plane agents.
• Tenant Isolation:
  Every hosted Supabase database runs in dedicated virtual machine/container boundaries. Packets on the internal private interfaces cannot be snooped or captured by neighboring tenant projects.
• SCRAM-SHA-256 Authentication:
  Even though the transport layer on this internal link does not apply TLS framing, credentials are never sent in plaintext. Supabase enforces SCRAM-SHA-256 (Salted Challenge Response Authentication Mechanism) by default. The password exchange relies on iterative PBKDF2 hashing, dynamic nonces, and mutual client/server proofs, preventing replay and eavesdropping attacks on credentials.

---

3. Control Boundaries: Customer Settings vs. Platform Orchestration

Supabase delineates between application-facing pooler properties and infrastructure-level proxy parameters:

• Customer-Configurable (Dashboard > Project Settings > Database > Connection Pooling):
  • Pool Mode (Transaction vs. Session)
  • Default Pool Size
  • Max Client Connections
  • IPv4 Add-on allocation
• Platform-Managed (Supabase Internal Control Plane):
  • upstream_ssl
  • Upstream pool distribution and internal target routing
  • Elixir runtime topology and BEAM node cluster configuration
  • Health checking and connection recycling intervals

Exposing upstream_ssl could lead to certificate validation failures, misconfigured upstream state, or degraded database performance across fleet orchestrations.

---

4. Compliance and Direct Connection Alternative

Certain enterprise compliance frameworks (such as FedRAMP High, specific HIPAA interpretations, or zero-trust data-in-transit security policies) require full end-to-end cryptographic transit encryption at every single hop.

If your organization has this mandate, the supported approach is:

1. Use the Direct Connection String:
   Connect directly to PostgreSQL on port 5432 bypassing Supavisor.
2. Enforce Full Certificate Verification:
   Configure your database client or ORM with sslmode=verify-full (or verify-ca) and supply the Supabase Project Certificate Authority (CA), which can be downloaded directly from:
   • Dashboard -> Project Settings -> Database -> Certificate Authority (CA).
3. Verify Encryption Status:
   Executing the following query in your application session:

   SELECT pid, ssl, version, cipher, bits, clientdn 
   FROM pg_stat_ssl 
   WHERE pid = pg_backend_pid();

   will return ssl = true along with the negotiated TLS version (e.g., TLSv1.3) and cipher details.
4. Client-Side / In-VPC Pooling:
   If your application requires connection pooling while adhering to strict hop-by-hop TLS rules, you can deploy a client-side pooler (e.g., PgBouncer or application connection pools like HikariCP / Prisma Accelerate) within your application environment that connects directly to Supabase via port 5432 with TLS enforced.