Navigation

Managing Roles for your Postgres database

Postgres

(Updated )

Managing roles

It is not recommended that you connect to the database from your application servers using the default role. To create additional roles for this purpose, there are several options:

  • Using the CREATE ROLE command as the default role (which has elevated privileges).
  • Using the Postgres Roles API
  • Using "Roles" section in your database settings
  • Using the PlanetScale CLI pscale role commands

Creating roles via settings

To create a new role via your database settings, first select the database you want to create a role for, then navigate to "Settings" and then "Roles."

Roles section in sidebar

On this page, you will see all roles created via this settings page and the API. It will not show roles created manually via CREATE ROLE commands.

List of roles

To create a new role, click the "New role" button. This will bring up a modal where you can configure:

  • Which branch you want this role to be created for
  • The name of the role
  • The permissions for this role

Configure the new role

When ready, click "Create role." You'll be presented with the credentials for this new role. You should save the password to a secure location, as it will not be presented to you again after you close the modal.

Credentials for the new role

You can now use this new credential to connect to your database. You can also rename and delete roles by clicking the "..." for the role on the Roles page.

Rename or delete a role

Creating roles via the CLI

You can also manage roles directly from the command line using the PlanetScale CLI. This provides a convenient way to create, list, and manage roles as part of your development workflow or automation scripts.

Prerequisites

Make sure you have the PlanetScale CLI installed and that you're authenticated with pscale auth login.

Available commands

Create a new role:

pscale role create <database> <branch> <name> [flags]

Example:

pscale role create my-database main api-user --inherited-roles pg_read_all_data --ttl 24h

List all roles for a branch:

pscale role list <database> <branch>

Get details for a specific role:

pscale role get <database> <branch> <role-id>

Delete a role:

pscale role delete <database> <branch> <role-id>

Update a role's name:

pscale role update <database> <branch> <role-id> --name <new-name>

Renew a role's expiration:

pscale role renew <database> <branch> <role-id>

Reset the default postgres role credentials:

pscale role reset-default <database> <branch>

Roles created via the CLI will appear in your database settings and can be managed through the dashboard as well.

Creating roles via CREATE ROLE

When you create a role via the Postgres CREATE ROLE command, these will not display on your database settings. It is up to you to manage these via the psql CLI.

PlanetScale's routing layer uses the user to identify which database or branch we are sending queries to. For example, the user matt.nk35mx55qq routes to the PlanetScale database with branch id nk35mx55qq. When you create a new role, you do not need to specify the branch id on the user. You can simply set the user to matt.

However, when you connect, you must append the branch id to the user so we know which branch to route to.

  1. From the PlanetScale organization dashboard, select the desired database
  2. Select the desired branch from the dropdown
  3. Click "Connect"
  4. Copy the branch id
  5. Append it to your user with .branch_id

Need help?

Get help from the PlanetScale Support team, or join our GitHub discussion board to see how others are using PlanetScale.