Database

Postgres Roles

Managing access to your Postgres database and configuring permissions.

Postgres manages database access permissions using the concept of roles. Generally you wouldn't use these roles for your own application - they are mostly for configuring system access to your database. If you want to configure application access, then you should use Row Level Security (RLS). You can also implement Role-based Access Control on top of RLS.

Users vs roles

In PostgreSQL, roles can function as users or groups of users. Users are roles with login privileges, while groups (also known as role groups) are roles that don't have login privileges but can be used to manage permissions for multiple users.

Creating roles

You can create a role using the create role command:


_10
create role "role_name";

Creating users

Roles and users are essentially the same in Postgres, however if you want to use password-logins for a specific role, then you can use WITH LOGIN PASSWORD:


_10
create role "role_name" with login password 'extremely_secure_password';

Passwords

Your Postgres database is the core of your Supabase project, so it's important that every role has a strong, secure password at all times. Here are some tips for creating a secure password:

  • Use a password manager to generate it.
  • Make a long password (12 characters at least).
  • Don't use any common dictionary words.
  • Use both upper and lower case characters, numbers, and special symbols.

Special symbols in passwords

If you use special symbols in your postgres password, you must remember to percent-encode your password later if using the postgres connection string, for example, postgresql://postgres.projectref:p%[email protected]:6543/postgres

Changing your project password

When you created your project you were also asked to enter a password. This is actually the password for the postgres role in your database. You can update this from the Dashboard under the database settings page. You should never give this to third-party service unless you absolutely trust them. Instead, we recommend that you create a new user for every service that you want to give access too. This will also help you with debugging - you can see every query that each role is executing in your database within pg_stat_statements.

Changing the password does not result in any downtime. All connected services, such as postgrest, pgbouncer, and other Supabase managed services, are automatically updated to use the latest password to ensure availability. However, if you have any external services connecting to the Supabase database using hardcoded username/password credentials, a manual update will be required.

Granting permissions

Roles can be granted various permissions on database objects using the GRANT command. Permissions include SELECT, INSERT, UPDATE, and DELETE. You can configure access to almost any object inside your database - including tables, views, functions, and triggers.

Revoking permissions

Permissions can be revoked using the REVOKE command:


_10
REVOKE permission_type ON object_name FROM role_name;

Role hierarchy

Roles can be organized in a hierarchy, where one role can inherit permissions from another. This simplifies permission management, as you can define permissions at a higher level and have them automatically apply to all child roles.

Role inheritance

To create a role hierarchy, you first need to create the parent and child roles. The child role will inherit permissions from its parent. Child roles can be added using the INHERIT option when creating the role:


_10
create role "child_role_name" inherit "parent_role_name";

Preventing inheritance

In some cases, you might want to prevent a role from having a child relationship (typically superuser roles). You can prevent inheritance relations using NOINHERIT:


_10
alter role "child_role_name" noinherit;

Supabase roles

Postgres comes with a set of predefined roles. Supabase extends this with a default set of roles which are configured on your database when you start a new project:

postgres

The default Postgres role. This has admin privileges.

anon

For unauthenticated, public access. This is the role which the API (PostgREST) will use when a user is not logged in.

authenticator

A special role for the API (PostgREST). It has very limited access, and is used to validate a JWT and then "change into" another role determined by the JWT verification.

authenticated

For "authenticated access." This is the role which the API (PostgREST) will use when a user is logged in.

service_role

For elevated access. This role is used by the API (PostgREST) to bypass Row Level Security.

supabase_auth_admin

Used by the Auth middleware to connect to the database and run migration. Access is scoped to the auth schema.

supabase_storage_admin

Used by the Auth middleware to connect to the database and run migration. Access is scoped to the storage schema.

dashboard_user

For running commands via the Supabase UI.

supabase_admin

Supabase Administrative role for maintaining your database.

Resources