Section 1 · Core concept

One primitive: ROLE

In Postgres, user and group are not separate SQL primitives. They are both roles with different attributes. That is the foundation for everything else in the video.

Section 1 · Group role picture

Grant once, many inherit

Group-style roles exist so you do not repeat the same GRANT statements for every service account. Attach permissions to the parent role, then attach members to that parent.

The visual emphasis is now the direction of permission flow: grants live on the left, member accounts consume them on the right.

Section 2 · Creating roles

Bare CREATE ROLE

The simplest role has no login and no password. That is ideal for group roles or roles you intend PostgREST to switch into later through JWT claims.

CREATE ROLE role_name;

Section 2 · Login roles

LOGIN + PASSWORD

When something connects directly to Postgres with a URI, the role needs LOGIN. Backends, scripts, BI tools, and admin clients all fit that pattern.

CREATE ROLE app_backend WITH LOGIN PASSWORD 'your-strong-password';

The connection string is not connecting to "a user type". It is connecting to a role that happens to be allowed to log in.

Section 2 · Rotation

Changing a role password

Provisioning and rotation are different moments in the same lifecycle. A role can stay the same while the secret changes underneath it.

ALTER ROLE app_backend WITH PASSWORD 'new-strong-password';

Section 2 · URI encoding

Postgres accepts it, URLs may not

Special characters are allowed in the password itself, but connection strings are URLs. Reserved characters need percent-encoding or the parser may split the URI incorrectly.

A good rule of thumb for demos is to avoid ending the password with @, since that often confuses people reading the string aloud.

Section 2 · Supabase studio

The postgres password is separate

The default postgres role is your setup and migration account. Supabase lets you reset that password in Studio, and it is independent from the custom roles you create afterward.

Section 3 · Fresh role behavior

A new role is limited, not blind

A newly created role can still inspect system catalogs, so commands like \d+ can list tables. But that does not mean the role can read or write the table rows themselves.

The narration makes an important distinction here: schema and catalog visibility are not the same thing as row privileges.

Section 3 · Explicit vs inherited grant

Inherited USAGE vs explicit USAGE

If you explicitly grant schema USAGE to a role that already inherits it via PUBLIC, the effective access does not change much, but the ACL becomes easier to read because the role now gets its own visible entry.

Section 3 · Why public resolves

The PUBLIC role is doing quiet work

Supabase grants USAGE on the public schema to the special PUBLIC role by default. Every role inherits that baseline, so custom roles can resolve object names there even when they do not appear explicitly in the ACL list.

SELECT nspname, nspacl
FROM pg_namespace
WHERE nspname = 'public';

The empty string before =U is Postgres notation for the PUBLIC role. That line applies to every role automatically.

Section 3 · Important exception

Built-in API roles are not blank slates

Supabase's built-in API roles such as anon and authenticated already come with grants configured by the platform. The "fresh role with almost no data access" story applies to roles you create yourself, not to these pre-wired roles.

Section 3 · GRANT grammar

GRANT has a simple shape

Once a role exists, object-level permissions are attached with a sentence you can almost say out loud: grant a permission on an object to a role.

GRANT permission ON object TO role;

Section 3 · Object examples

Tables, views, functions, sequences

Grants are not only for tables. In a real app, the role may need read access to a table, execute on a function, and maybe access to a sequence if inserts generate IDs automatically.

GRANT SELECT ON public.products TO app_backend;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.orders TO app_backend;
GRANT SELECT ON public.order_summary TO app_backend;
GRANT EXECUTE ON FUNCTION public.calculate_total(uuid) TO app_backend;

Section 3 · Sequence foot-gun

INSERT may still fail on auto IDs

A common surprise is granting INSERT on the table but forgetting the sequence behind the auto-generated ID. The table permission is not always enough by itself.

Section 3 · Schema access

USAGE on a schema is the hallway

A role can have table-level grants and still fail if it does not have USAGE on the schema that contains the table. In Supabase this is easy to miss because the default public schema already has a permissive baseline.

GRANT USAGE ON SCHEMA public TO app_backend;

The narration emphasizes custom schemas here because the public defaults can hide the pattern until your design grows beyond public.

Section 3 · Future objects

Grants do not automatically follow new tables

GRANT statements affect objects that exist now. If a migration creates a new table next week, the role does not magically inherit the same privileges unless you define default privileges for the role that creates the future objects.

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public
  GRANT SELECT ON TABLES TO app_reader;

Section 4 · Membership

Role inheritance

Membership lets you express hierarchy. One role can inherit the privileges of another so you do not repeat the full permission set everywhere.

GRANT parent_role TO child_role;

CREATE ROLE child_role_name IN ROLE parent_role_name;

Section 4 · PostgreSQL 16+

INHERIT vs SET ROLE

PostgreSQL 16 and later let you control inheritance at the membership level. That gives you a choice between automatic privilege flow and explicit activation with SET ROLE.

GRANT parent_role TO child_role WITH INHERIT TRUE;

GRANT parent_role TO child_role WITH INHERIT FALSE, SET TRUE;

Section 4 · Why teams use this

One shared baseline for many services

If multiple services share the same database, inheritance keeps the base permission model centralized. Update the parent role once and every child role receives the new behavior.

That is the operational win the narration is pointing at: one place to update, multiple downstream roles benefit.

Section 5 · Supabase roles

Built-in platform roles

Supabase starts a project with a ready-made role system. You do not create these from scratch each time; the platform provisions them because the API, auth, storage, and admin services rely on them.

Section 5 · Internal service roles

Other built-ins you will see in a project

Some Supabase roles exist mainly so platform services can own schemas or run internal jobs. You may not grant against them often, but it helps to recognize their purpose when inspecting permissions.

Section 5 · Day-to-day RLS targets

Most policies target anon and authenticated

When you are writing row-level security for an application, these are usually the two roles you care about most. anon represents unauthenticated requests, and authenticated represents signed-in users.

Section 6 · Example app need

Design a role for the job, not for maximum power

The example app only needs to read from products and create or update rows in orders. That is a perfect least-privilege case: define a role that can do exactly that and nothing more.

Section 6 · Approach 1

Direct database connection role

If the backend or tool connects straight to Postgres, the role needs LOGIN because the connection string authenticates directly as that role.

CREATE ROLE app_service LOGIN PASSWORD 'your-strong-password';

GRANT USAGE ON SCHEMA public TO app_service;
GRANT SELECT ON public.products TO app_service;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.orders TO app_service;

Section 6 · Least privilege payoff

Smaller role, smaller damage

This is the real value of creating a narrower role. The application still works, but the credential cannot act like an admin account if it leaks or gets misused.

Section 5 · API request path

How the Data API chooses a role

With the Data API, clients do not connect as your custom direct-login role. PostgREST connects as authenticator, then switches into the role named by the JWT, typically anon or authenticated.

Section 6 · Approach 2

Custom role for the Supabase Data API

If requests go through the Supabase client libraries or REST endpoints, the custom role does not need LOGIN. PostgREST will switch into that role instead.

CREATE ROLE custom_user NOLOGIN;

GRANT USAGE ON SCHEMA public TO custom_user;
GRANT SELECT ON public.products TO custom_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.orders TO custom_user;

GRANT custom_user TO authenticator;

The last GRANT is the permission for authenticator to switch into custom_user. Without it, the role exists but cannot be assumed by the API layer.

Section 6 · JWT role claim

The token must name the custom role

Creating the role is not enough. The incoming JWT has to include role: "custom_user" or PostgREST will keep using the default role like anon or authenticated.

Section 6 · Direction matters

GRANT anon TO custom_user

If you want the custom role to inherit whatever anon already has, the grant direction is important. The child goes on the right.

GRANT anon TO custom_user;

Section 7 · Mental model

GRANT decides tables, RLS decides rows

Object grants answer "can this role touch this table at all?" Row-level security answers "which subset of rows should be visible or writable once table access exists?"

Section 7 · Enable the policy layer

Turn RLS on

Once RLS is enabled, row access is denied by default until a policy allows it. That is why viewers should think of RLS as a second authorization layer after grants.

ALTER TABLE public.orders ENABLE ROW LEVEL SECURITY;

Section 7 · Supabase Auth policy

auth.uid() is the usual multi-tenant pattern

With Supabase Auth, the common pattern is to compare a row's owner column to the current user's UUID from the JWT. That is what auth.uid() gives you.

CREATE POLICY "users can read their own orders"
  ON public.orders
  FOR SELECT
  TO authenticated
  USING (user_id = auth.uid());

Section 7 · Direct Postgres policy

No JWT? Use a request-local session setting

If the caller connects directly to Postgres instead of through Supabase Auth, you can still do row-based filtering. The backend sets a request-specific value, and the policy reads it with current_setting(..., true).

CREATE POLICY "users can read their own orders"
  ON public.orders
  FOR SELECT
  USING (user_id = current_setting('app.current_user_id', true)::uuid);

Section 7 · Transaction-local state

Set the value safely

The backend can populate the setting just before the query. Using the transaction-local form avoids user identity leaking into later requests when pooled connections are reused.

SELECT set_config(
  'app.current_user_id',
  '550e8400-e29b-41d4-a716-446655440000',
  true
);

Section 8 · Separate audience

Schemas can isolate a slice of the database

Reporting tools often need summaries, not raw operational tables. A separate schema is a clean way to expose the transformed data while keeping the original tables and sensitive columns elsewhere.

The trust boundary becomes the ETL or transformation job that decides what crosses from operational data into reporting data.

Section 8 · Reporting schema SQL

Create the schema and its tables

Once you decide the reporting area exists, create its schema and the tables or views that the analytics audience should actually use.

CREATE SCHEMA reporting;

CREATE TABLE reporting.monthly_summary (
  month date,
  revenue numeric,
  order_count int
);

Section 8 · BI login role

A Power BI reader only needs the reporting schema

If Power BI or another BI tool connects directly to Postgres, give it its own login role and point it only at the reporting schema. That keeps raw application tables out of scope.

CREATE ROLE powerbi_reader LOGIN PASSWORD 'strong-password';

GRANT USAGE ON SCHEMA reporting TO powerbi_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO powerbi_reader;

As with the earlier default-privileges slide, future reporting tables may need default grants too if you do not want refresh jobs to break later.

Section 8 · Direct DB vs Data API

Expose the schema only if the API really needs it

Supabase can expose extra schemas through the Data API, but that is optional. BI tools commonly work better over a direct connection or the session pooler, especially for long-lived reporting sessions.

Outro · Final model

The permissions model to repeat in every project

This is the full story from the video in one picture: use postgres for setup and migrations, use narrow roles for production tools and services, use RLS when access depends on the current user, and carve out schemas when one audience should only see one slice of data.