Managing Roles for your Postgres database
(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."
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.
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
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.
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.
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.
- From the PlanetScale organization dashboard, select the desired database
- Select the desired branch from the dropdown
- Click "Connect"
- Copy the branch id
- 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.