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 confgure application access, then you should use Row Level Security.

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.

Superuser roles#

The superuser role has unrestricted access to the database system. Typically these roles will be able to bypass all security measures (like Row Level Security). Be cautious when granting superuser privileges as it can potentially lead to security risks.

Creating roles#

You can create a role using the create role command:

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:

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 e.g. postgresql://postgres:p%3Dword@db.xxx.supabase.co:5432/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 3rd-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.

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:

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:

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:

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 "anonymous 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#

• Official Postgres docs: Database Roles
• Official Postgres docs: Role Membership
• Official Postgres docs: Function Permissions