How to Configure Supabase User Roles
You set up supabase user roles by creating Postgres roles and linking them to policies. Then you grant table rights to each role based on what that person or app needs. Many teams skip this step and share the default admin login across people and services. That broad login lets anyone read sensitive tables or drop production data by mistake. How supabase user roles work in Postgres Every project starts with a set of built-in Postgres roles. The default postgres role has admin rights across th
Your coding agents run with your access.
Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.
You set up supabase user roles by creating Postgres roles and linking them to policies. Then you grant table rights to each role based on what that person or app needs. Many teams skip this step and share the default admin login across people and services. That broad login lets anyone read sensitive tables or drop production data by mistake.
How supabase user roles work in Postgres
Every project starts with a set of built-in Postgres roles. The default postgres role has admin rights across the database. The platform does not grant full superuser status to this role. It blocks superuser status to prevent destructive commands on the host system. Still, the postgres role gets extra rights to run most admin tasks.
When you create a project, you set the password for this default postgres role. Any query you run inside the project dashboard also runs as this postgres role. Those dashboard queries carry a comment that marks the dashboard as the source. You should not share this main password with outside apps or whole teams. Instead, you should make a separate database user for each service or person.
Projects also include built-in roles for app traffic, such as anon and authenticated. They also include service_role and authenticator to handle incoming requests. The database uses these built-in roles to sort public quests from logged-in app users. You then write row rules that check which role is active before returning data.
Creating custom roles for engineers and services
For direct database sessions, built-in app roles are not enough. Engineers, support staff, and background jobs often connect right to the Postgres port. If every person logs in with the default admin role, your logs lose value. You cannot tell which engineer ran a bad update or read customer records.
To fix this, admins create custom Postgres roles with narrow permissions. A read-only role can select from public tables without changing any rows. A billing service role can read and write only inside the payments schema. A support role might get select rights on orders but no rights on secret keys. Giving each service its own role keeps blast radius small when a key leaks. If one service key leaks, you revoke that single role and keep others running.
User Provisioning (SCIM) + Lambda Execution Roles: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
What native roles solve and what they leave open
Native database roles do a good job of controlling who can log in. They also define which tables or schemas a user can touch after logging in. Dividing roles by person and service stops people from sharing the main admin password. It also blocks basic mistakes, like a reporting script deleting a production table.
Still, static roles only check permissions at the table or row level. Once a role has read access to a table, it gets every permitted column. If a developer needs to check user accounts, a read role returns real emails. It also returns phone numbers, card numbers, and national IDs in plain text. Native roles must stay setup ahead of time and stay active until someone revokes them. They do not inspect individual queries in flight to block risky commands before they run. They also do not mask sensitive fields in query results for fixing sessions.
Run guardrails and masking on the Postgres connection
Role grants and firewall rules are needed setup. Setup alone cannot check single SQL statements or mask sensitive rows in a response. By design, hoop.dev sits inline in the Postgres protocol beside Postgres, RDS, or Aurora as a sidecar for your Postgres. On every production query from an engineer or AI agent, hoop.dev decides whether to pass, mask, block, or hold it for approval.
First, hoop.dev guardrails refuse disallowed drops or deletes before they run, and risky statements can pause for human review. Next, hoop.dev data masking rewrites sensitive fields in returned rows in memory. Furthermore, hoop.dev holds SOC 2 Type II and logs every statement and verdict per resource. Check out the project on GitHub.
Frequently asked questions
Does the default postgres role have full superuser rights?
No, the default postgres role is an admin role, not a full superuser. The platform withholds superuser status to stop destructive system actions, though it grants extra admin privileges.
Which role runs queries inside the project dashboard?
Queries that you run in the project dashboard execute under the default postgres role. The system tags these queries with a comment showing the dashboard as the source.
Should third-party services use the default database password?
No, you should only give the default password to services you trust completely. Best practice is to create a separate database user for every outside service.
Related guides
In this post