connection methods

14 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

Special symbols in Postgres passwords require percent-encoding

If you use special symbols in your Postgres password, you must percent-encode your password when using the Postgres connection string. For example, a password with 'p=word' becomes 'p%3Dword' in the connection string like postgresql://postgres.projectref:••••••••@aws-0-us-east-1.pooler.supabase.com:6543/postgres.

Identify timed-out queries in Supabase Dashboard

Go to the SQL Editor, set the query source to Logs, and run a query matching event_message for 'statement timeout' to identify timed-out events. Also match 'duration' to find queries that successfully run longer than 10 seconds. Filter by log_attributes['parsed.user_name'] to find events from specific roles.

Dashboard and Client queries max timeout

Dashboard and Client API queries have a maximum configurable timeout of 60 seconds. For longer transactions, use Supavisor or direct connections.

Session level timeout configuration

Set a session-level timeout with the SQL command: set statement_timeout = '10min';. Session level settings persist only for the duration of the connection. Session level timeouts can only be used with connections through Supavisor in session mode (port 5432) or a direct connection. They cannot be used in the Dashboard, with the Supabase Client API, or with Supavisor in Transaction mode (port 6543).

View current session timeout

Execute SHOW statement_timeout; to view the current session timeout setting.

Function level timeout configuration

Set a timeout at the function level using the SET clause in a function definition. This works with the Database REST API when called from Supabase client libraries. Example: create or replace function myfunc() returns void as $$ select pg_sleep(3); $$ language sql set statement_timeout TO '4s'; This is mostly for recurring functions that need a special exemption for runtimes.

Default role timeouts in Supabase

The default statement timeouts for built-in roles are: anon 3s, authenticated 8s, service_role none (defaults to authenticator role's 8s timeout if unset), postgres none (capped by default global timeout to 2min).

Alter role timeout

Change a role's statement timeout using: alter role example_role set statement_timeout = '10min'; The timeout value can use minutes (e.g., '10min') or seconds (e.g., '10s').

Reload PostgREST after timeout changes

After changing the timeout for the Supabase Client API calls (role level), reload PostgREST to reflect the timeout changes by running: NOTIFY pgrst, 'reload config';

Check role-level timeout configuration

To verify role-level timeout settings, run: select rolname, rolconfig from pg_roles where rolname in ('anon', 'authenticated', 'postgres', 'service_role'); Unlike global settings, SHOW statement_timeout will not display role-level timeouts.

Global level timeout configuration

Change the statement timeout for all roles and sessions without an explicit timeout already set using: alter database postgres set statement_timeout TO '4s'; Verify the change with: show statement_timeout;

API server roles and their purposes

Each API server uses a designated role for connecting to the database: supabase_admin (Realtime and project configuration), authenticator (PostgREST), supabase_auth_admin (Auth), supabase_storage_admin (Storage), supabase_replication_admin (Read Replicas synchronization), postgres (Supabase Dashboard and External Tools like Prisma, SQLAlchemy, PSQL), and custom roles (External Tools).

Filter logs by user role

Filter timeout and performance logs by the parsed.user_name field to retrieve events made by specific roles. Example: where log_attributes['parsed.user_name'] = '<ROLE>'

Three ways to access data in Supabase with security models

Supabase provides three ways to access data: (1) Data API - use Supabase client libraries, REST, or GraphQL with a publishable key, protect exposed tables with Row Level Security (RLS) and grant only the privileges each role needs; (2) Edge Functions - put custom server-side logic between your client and database, can use secrets, API keys, or database connection strings inside the function, and can disable the Data API if your app only accesses data this way; (3) Direct database connections - connect to Postgres with a connection string from trusted servers, workers, or tools, keep database credentials secret and use the right connection method for your environment, can disable the Data API if your app only uses direct connections.

Give your agent this brain