Why does update fail when I granted UPDATE?

Because the statement has a filter. Finding the rows to change reads the table, and reading needs SELECT. Grant SELECT and UPDATE together.

Does insert need a grant on the sequence?

Only when the id column takes its value from a sequence, as serial and bigserial columns do. Then the role also needs USAGE on that sequence. A uuid default needs nothing extra.

Should I just grant all four?

It works, and it gives the role more than it uses. Supabase recommends granting each role only what it needs. If signed-out visitors only read, grant anon SELECT and nothing else.

Which GRANT does each supabase-js call need? Checker and table

What does your code call on this table? .select()Reading rows needs SELECT. .insert()Inserting needs INSERT. Rows are not returned unless you chain .select(). .insert().select()Returning the new row needs SELECT on the returned columns as well. .update().eq()A filter reads columns to find the rows, so UPDATE alone is not enough. .upsert()The conflict path is an update, and the conflict column is read. .delete().eq()A filter reads columns to find the rows, so DELETE alone is not enough.

Who calls it? Signed-out visitors (anon) Signed-in users (authenticated) Your server with the secret key (service_role)

Table name Sequence, only if the id is serial

grant select, insert on public.your_table to authenticated;

Needs SELECT, INSERT on the table, plus USAGE on the sequence if the id column is serial.

The rule behind each row

Call	SQL it becomes	Table privileges	Why
`.select()`	`select`	SELECT	Reading rows needs SELECT.
`.insert()`	`insert`	INSERT	Inserting needs INSERT. Rows are not returned unless you chain .select().
`.insert().select()`	`insert ... returning`	INSERT, SELECT	Returning the new row needs SELECT on the returned columns as well.
`.update().eq()`	`update ... where`	SELECT, UPDATE	A filter reads columns to find the rows, so UPDATE alone is not enough.
`.upsert()`	`insert ... on conflict do update`	INSERT, SELECT, UPDATE	The conflict path is an update, and the conflict column is read.
`.delete().eq()`	`delete ... where`	DELETE, SELECT	A filter reads columns to find the rows, so DELETE alone is not enough.

Three of these surprise people:

Update and delete need SELECT. The PostgreSQL manual: any nontrivial UPDATE command will require SELECT privilege as well. The filter in .eq() reads a column.
Upsert needs UPDATE. The manual: If ON CONFLICT DO UPDATE is present, UPDATE privilege on the table is also required.
Chaining .select() needs SELECT. The client reference says: By default, inserted rows are not returned. To return it, chain the call with .select(). Returning rows is a read.

For each call we granted exactly the privileges in the table, ran the statement, then removed one privilege at a time and confirmed Postgres answered permission denied for table.

Tested on the SQL statements in Postgres (PGlite), not through a hosted project. The Data API may send a slightly different statement for the same call; if a call still fails, the hint in the error names the missing privilege.

A grant is half of it

A grant lets the role reach the table. With row level security on, a policy must also allow the rows. See which policy each command needs, or paste your table into the tool below to get grants and policies together.

Which grant does each call need?

The rule behind each row

A grant is half of it

Write the fix for your tables

Who can reach each table

Questions

Sources

Related

Did it fix your error?