The Postgres development platform. Supabase gives you a dedicated Postgres database to build your web, mobile, and AI applications.
TypeScript aialternativeauthdatabasedenoembeddingsexamplefirebasenextjsoauth2pgvectorpostgispostgrespostgresqlpostgrestrealtimesupabasevectorswebsockets
import { ident, joinSqlFragments, literal, safeSql, type SafeSqlFragment } from '../../../pg-format'
/**
* Builds the shared `table_privileges` and `table_grants` CTEs used by
* both the exposed-tables list query and the counts-only query.
*
* Returns SQL text meant to follow `WITH` (no leading `WITH` keyword).
* Callers that append additional CTEs should add a comma after interpolation.
*/
function getTableGrantsCTEs({
search,
ignoredSchemas = [],
}: { search?: string; ignoredSchemas?: string[] } = {}): SafeSqlFragment {
const IGNORED_SCHEMAS_LIST = joinSqlFragments(
ignoredSchemas.map((s) => literal(s)),
', '
)
return safeSql`
table_privileges as (
select
c.oid::int as id,
n.nspname as schema_name,
c.relname as name,
c.relkind as kind,
-- Anon Privileges
bool_or(pr.rolname = 'anon' and acl.privilege_type = 'SELECT') as anon_select,
bool_or(pr.rolname = 'anon' and acl.privilege_type = 'INSERT') as anon_insert,
bool_or(pr.rolname = 'anon' and acl.privilege_type = 'UPDATE') as anon_update,
bool_or(pr.rolname = 'anon' and acl.privilege_type = 'DELETE') as anon_delete,
-- Authenticated Privileges
bool_or(pr.rolname = 'authenticated' and acl.privilege_type = 'SELECT') as auth_select,
bool_or(pr.rolname = 'authenticated' and acl.privilege_type = 'INSERT') as auth_insert,
bool_or(pr.rolname = 'authenticated' and acl.privilege_type = 'UPDATE') as auth_update,
bool_or(pr.rolname = 'authenticated' and acl.privilege_type = 'DELETE') as auth_delete,
-- Service Role Privileges
bool_or(pr.rolname = 'service_role' and acl.privilege_type = 'SELECT') as srv_select,
bool_or(pr.rolname = 'service_role' and acl.privilege_type = 'INSERT') as srv_insert,
bool_or(pr.rolname = 'service_role' and acl.privilege_type = 'UPDATE') as srv_update,
bool_or(pr.rolname = 'service_role' and acl.privilege_type = 'DELETE') as srv_delete
from pg_class c
join pg_namespace n
on n.oid = c.relnamespace
left join lateral aclexplode(coalesce(c.relacl, acldefault('r', c.relowner))) as acl
on true
left join pg_roles pr
on pr.oid = acl.grantee
where c.relkind in ('r', 'p', 'v', 'm', 'f')
${IGNORED_SCHEMAS_LIST ? safeSql`and n.nspname not in (${IGNORED_SCHEMAS_LIST})` : safeSql``}
${search ? safeSql`and (n.nspname || '.' || c.relname) ilike ${literal(`%${search}%`)}` : safeSql``}
group by c.oid, n.nspname, c.relname, c.relkind
),
table_grants as (
select
id,
schema_name,
name,
kind,
case
-- 1. Strict Granted: All 3 roles possess ALL 4 privileges
when (
anon_select and anon_insert and anon_update and anon_delete and
auth_select and auth_insert and auth_update and auth_delete and
srv_select and srv_insert and srv_update and srv_delete
) then 'granted'
-- 2. Strict Revoked: NO role possesses ANY privilege
when not (
anon_select or anon_insert or anon_update or anon_delete or
auth_select or auth_insert or auth_update or auth_delete or
srv_select or srv_insert or srv_update or srv_delete
) then 'revoked'
-- 3. Custom: Anything in between
else 'custom'
end as status
from table_privileges
)
`
}
export function getExposedTablesSql({
search,
offset,
limit,
ignoredSchemas = [],
}: {
search?: string
offset: number
limit: number
ignoredSchemas?: string[]
}): SafeSqlFragment {
return safeSql`
with ${getTableGrantsCTEs({ search, ignoredSchemas })}
select
(select count(*)::int from table_grants) as total_count,
coalesce(
(
select jsonb_agg(
jsonb_build_object(
'id', tg.id,
'schema', tg.schema_name,
'name', tg.name,
'status', tg.status
)
)
from (
select *
from table_grants
order by schema_name, name
offset ${literal(offset)}
limit ${literal(limit)}
) tg
),
'[]'::jsonb
) as tables;
`
}
export function getExposedTableCountsSql({
selectedSchemas,
ignoredSchemas = [],
}: {
selectedSchemas: string[]
ignoredSchemas?: string[]
}) {
const schemasList =
selectedSchemas.length > 0
? joinSqlFragments(
selectedSchemas.map((s) => literal(s)),
', '
)
: safeSql`''`
return safeSql`
with ${getTableGrantsCTEs({ ignoredSchemas })}
select
count(*)::int as total_count,
(count(*) filter (where status = 'granted' and schema_name in (${schemasList})))::int as grants_count
from table_grants
`
}
/**
* Builds the shared `function_privileges` and `function_grants` CTEs used by
* both the exposed-functions list query and the counts-only query.
*
* Returns SQL text meant to follow `WITH` (no leading `WITH` keyword).
* Callers that append additional CTEs should add a comma after interpolation.
*/
function getFunctionGrantsCTEs({
search,
ignoredSchemas = [],
}: { search?: string; ignoredSchemas?: string[] } = {}): SafeSqlFragment {
const IGNORED_SCHEMAS_LIST = joinSqlFragments(
ignoredSchemas.map((s) => literal(s)),
', '
)
return safeSql`
function_privileges as (
select
n.nspname as schema_name,
p.proname as name,
-- Aggregate EXECUTE across all overloads + all 3 roles
bool_or(pr.rolname = 'anon' and acl.privilege_type = 'EXECUTE') as anon_execute,
bool_or(pr.rolname = 'authenticated' and acl.privilege_type = 'EXECUTE') as auth_execute,
bool_or(pr.rolname = 'service_role' and acl.privilege_type = 'EXECUTE') as srv_execute
from pg_proc p
join pg_namespace n
on n.oid = p.pronamespace
left join lateral aclexplode(coalesce(p.proacl, acldefault('f', p.proowner))) as acl
on true
left join pg_roles pr
on pr.oid = acl.grantee
where p.prokind in ('f', 'w')
${IGNORED_SCHEMAS_LIST ? safeSql`and n.nspname not in (${IGNORED_SCHEMAS_LIST})` : safeSql``}
${search ? safeSql`and (n.nspname || '.' || p.proname) ilike ${literal(`%${search}%`)}` : safeSql``}
group by n.nspname, p.proname
),
function_grants as (
select
schema_name,
name,
case
when anon_execute and auth_execute and srv_execute then 'granted'
when not (anon_execute or auth_execute or srv_execute) then 'revoked'
else 'custom'
end as status
from function_privileges
)
`
}
export function getExposedFunctionsSql({
search,
offset,
limit,
ignoredSchemas = [],
}: {
search?: string
offset: number
limit: number
ignoredSchemas?: string[]
}): SafeSqlFragment {
return safeSql`
with ${getFunctionGrantsCTEs({ search, ignoredSchemas })}
select
(select count(*)::int from function_grants) as total_count,
coalesce(
(
select jsonb_agg(
jsonb_build_object(
'schema', fg.schema_name,
'name', fg.name,
'status', fg.status
)
)
from (
select *
from function_grants
order by schema_name, name
offset ${literal(offset)}
limit ${literal(limit)}
) fg
),
'[]'::jsonb
) as functions;
`
}
export function getExposedFunctionCountsSql({
selectedSchemas,
ignoredSchemas = [],
}: {
selectedSchemas: string[]
ignoredSchemas?: string[]
}): SafeSqlFragment {
const schemasList =
selectedSchemas.length > 0
? joinSqlFragments(
selectedSchemas.map((s) => literal(s)),
', '
)
: safeSql`''`
return safeSql`
with ${getFunctionGrantsCTEs({ ignoredSchemas })}
select
count(*)::int as total_count,
(count(*) filter (where status = 'granted' and schema_name in (${schemasList})))::int as grants_count
from function_grants
`
}
export function getDefaultPrivilegesStateSql({
schema = 'public',
}: { schema?: string } = {}): SafeSqlFragment {
return safeSql`
select
count(*)::int as grant_count
from pg_default_acl d
join pg_namespace n on n.oid = d.defaclnamespace
join pg_roles r on r.oid = d.defaclrole
where n.nspname = ${literal(schema)}
and r.rolname = 'postgres'
and d.defaclobjtype in ('r', 'f', 'S')
and exists (
select 1
from aclexplode(d.defaclacl) acl
join pg_roles gr on gr.oid = acl.grantee
where gr.rolname in ('anon', 'authenticated', 'service_role')
)
`
}
export function buildDefaultPrivilegesSql(action: 'grant' | 'revoke'): SafeSqlFragment {
const roles = ['anon', 'authenticated', 'service_role']
const statements: SafeSqlFragment[] = []
for (const role of roles) {
if (action === 'grant') {
statements.push(
safeSql`alter default privileges for role postgres in schema public grant select, insert, update, delete on tables to ${ident(role)}`,
safeSql`alter default privileges for role postgres in schema public grant execute on functions to ${ident(role)}`,
safeSql`alter default privileges for role postgres in schema public grant usage, select on sequences to ${ident(role)}`
)
} else {
statements.push(
safeSql`alter default privileges for role postgres in schema public revoke select, insert, update, delete on tables from ${ident(role)}`,
safeSql`alter default privileges for role postgres in schema public revoke execute on functions from ${ident(role)}`,
safeSql`alter default privileges for role postgres in schema public revoke usage, select on sequences from ${ident(role)}`
)
}
}
if (action === 'revoke') {
statements.push(
safeSql`alter default privileges for role postgres in schema public revoke execute on functions from public`
)
} else {
statements.push(
safeSql`alter default privileges for role postgres in schema public grant execute on functions to public`
)
}
return safeSql`${joinSqlFragments(statements, ';\n')};`
}
export const buildTablePrivilegesSql = (
oids: number[],
action: 'grant' | 'revoke'
): SafeSqlFragment => {
if (oids.length === 0) return safeSql``
const privilegeClause =
action === 'grant'
? safeSql`grant select, insert, update, delete on table %I.%I to anon, authenticated, service_role`
: safeSql`revoke all on table %I.%I from anon, authenticated, service_role`
return safeSql`
do $$
declare
nspname name;
relname name;
begin
for nspname, relname in
select n.nspname, c.relname
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where c.oid in (${joinSqlFragments(
oids.map((oid) => literal(oid)),
', '
)})
loop
execute format('${privilegeClause}', nspname, relname);
end loop;
end $$;
`
}
export const buildFunctionPrivilegesSql = (
schemaNames: string[],
action: 'grant' | 'revoke'
): SafeSqlFragment => {
if (schemaNames.length === 0) return safeSql``
const tuples = joinSqlFragments(
schemaNames.map((sn) => {
const dotIdx = sn.indexOf('.')
const schema = sn.slice(0, dotIdx)
const name = sn.slice(dotIdx + 1)
return safeSql`(${literal(schema)},${literal(name)})`
}),
', '
)
const privilegeClause =
action === 'grant'
? safeSql`grant execute on function %I.%I(%s) to anon, authenticated, service_role`
: safeSql`revoke all on function %I.%I(%s) from anon, authenticated, service_role`
return safeSql`
do $$
declare
nspname name;
proname name;
arg_types text;
begin
for nspname, proname, arg_types in
select n.nspname, p.proname, pg_get_function_identity_arguments(p.oid)
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where (n.nspname, p.proname) in (${tuples})
loop
execute format('${privilegeClause}', nspname, proname, arg_types);
end loop;
end $$;
`
}