Neon is now SOC 2 Type 2 compliant 🚀Read more
Manage/Access

Manage database access

Learn how to manage user access to databases in your Neon project

Each Neon project is created with a default Postgres role that takes its name from your Neon account (the account you registered with). For example, if a user named "Alex" signs up for Neon, the project is created with a default role named alex.

The default Postgres role is automatically assigned the neon_superuser role, which allows creating databases, roles, and reading and writing data in all tables, views, and sequences. Any user created with the Neon console, Neon API, or Neon CLI is also assigned the neon_superuser role.

It is good practice to reserve neon_superuser roles for database administration tasks like creating roles and databases. For other users, we recommend creating roles with specific sets of permissions based on application and access requirements. Then, assign the appropriate roles to your users. The roles you create should adhere to a least privilege model, granting only the permissions required to accomplish their tasks.

But how do you create roles with limited access? The following sections describe how to create read-only and read-write roles and assign those roles to users. We'll also look at how to create a "developer" role and grant that role full access to a database on a development branch in a Neon project.

A word about users, groups, and roles in Postgres

In Postgres, users, groups, and roles are the same thing. From the PostgreSQL Database Roles documentation:

PostgreSQL manages database access permissions using the concept of roles. A role can be thought of as either a database user, or a group of database users, depending on how the role is set up.

Neon recommends granting privileges to roles, and then assigning those roles to your database users.

Creating roles with limited access

You can create roles with limited access via SQL. Roles created with SQL are created with the same basic public schema privileges granted to newly created roles in a standalone Postgres installation. These users are not assigned the neon_superuser role. They must be selectively granted permissions for each database object.

The recommended approach to creating roles with limited access is as follows:

  1. Use your default Neon role or another role with neon_superuser privileges to create roles for each application or use case via SQL. For example, create readonly and readwrite roles.
  2. Grant privileges to those roles to allow access to database objects. For example, grant the SELECT privilege to a readonly role, or grant SELECT, INSERT, UPDATE, and DELETE privileges to a readwrite role.
  3. Create your database users. For example, create users named readonly_user1 and readwrite_user1.
  4. Assign the readonly or readwrite role to those users to grant them the privileges associated with those roles. For example, assign the readonly role to readonly_user1, and the readwrite role to readwrite_user1.

note

You can remove a role from a user at any time to revoke privileges. See Revoke privileges.

Create a read-only role

This section describes how to create a read-only role with access to a specific database and schema. An SQL statement summary is provided at the end.

info

In Postgres, access must be granted at the database, schema, and object level. For example, to grant access to a table, you must also grant access to the database and schema in which the table resides. If these access permissions are not defined, the role will not be able access the table.

To create a read-only role:

  1. Connect to your database from an SQL client such as psql, pgAdmin, or the Neon SQL Editor. If you need help connecting, see Connect from any client.

  2. Create a readonly role using the following statement. A password is required.

    The password should have at least 12 characters with a mix of lowercase, uppercase, number, and symbol characters. For detailed password guidelines, see Manage roles with SQL.

  3. Grant the readonly role read-only privileges on the schema. Replace <database> and <schema> with actual database and schema names, respectively.

  4. Create a database user. The password requirements mentioned above apply here as well.

  5. Assign the readonly role to readonly_user1:

    The readonly_user1 user now has read-only access to tables in the specified schema and database and should be able to connect and run SELECT queries.

    If the user attempts to perform an INSERT, UPDATE, or DELETE operation, a permission denied error is returned.

SQL statement summary

To create the read-only role and user described above, run the following statements from an SQL client:

Create a read-write role

This section describes how to create a read-write role with access to a specific database and schema. An SQL statement summary is provided at the end.

To create a read-write role:

  1. Connect to your database from an SQL client such as psql, pgAdmin, or the Neon SQL Editor. If you need help connecting, see Connect from any client.

  2. Create a readwrite role using the following statement. A password is required.

    The password should have at least 12 characters with a mix of lowercase, uppercase, number, and symbol characters. For detailed password guidelines, see Manage roles with SQL.

  3. Grant the readwrite role read-write privileges on the schema. Replace <database> and <schema> with actual database and schema names, respectively.

  4. Create a database user. The password requirements mentioned above apply here as well.

  5. Assign the readwrite role to readwrite_user1:

    The readwrite_user1 user now has read-write access to tables in the specified schema and database and should able to connect and run SELECT, INSERT, UPDATE, DELETE queries.

SQL statement summary

To create the read-write role and user described above, run the following statements from an SQL client:

Create a developer role

This section describes how to create a "development branch" and grant developers full access to a database on the development branch. To accomplish this, we create a developer role on the "parent" branch, create a development branch, and then assign users to the developer role on the development branch.

As you work through the steps in this scenario, remember that when you create a branch in Neon, you are creating a clone of the parent branch, which includes the roles and databases on the parent branch.

To get started:

  1. Connect to the database on the parent branch from an SQL client such as psql, pgAdmin, or the Neon SQL Editor. If you need help connecting, see Connect from any client.

  2. Use your default Neon role or another role with neon_superuser privileges to create a developer role on the parent branch. For example, create a role named dev_users.

    The password should have at least 12 characters with a mix of lowercase, uppercase, number, and symbol characters. For detailed password guidelines, see Manage roles with SQL.

  3. Grant the dev_users role privileges on the database:

    You now have a dev_users role on your parent branch, and the role is not assigned to any users. This role will now be included in all future branches created from this branch.

    note

    The GRANT statement above does not grant privileges on existing schemas, tables, sequences, etc., within the database. If you want the dev_users role to access specific schemas, tables, etc., you need to grant those permissions explicitly.

    For example, to grant all privileges on all tables in a schema:

    Similarly, you'd grant privileges for sequences and other objects as needed.

    That said, the GRANT command above allows users with the dev_users role to create new schemas within the database. But for pre-existing schemas and their objects, you need to grant permissions explicitly.

  4. Create a development branch. Name it something like dev1. See Create a branch for instructions.

  5. Connect to the database on the development branch with an SQL client. Be mindful that a child branch connection string differs from a parent branch connection string. The branches reside on different hosts. If you need help connecting to your branch, see Connect from any client.

  6. After connecting the database on your new branch, create a developer user (e.g., dev_user1). The password requirements described above apply here as well.

  7. Assign the dev_users role to the dev_user1 user:

    The dev_user1 user can now connect to the database on your development branch and start using the database with full privileges.

SQL statement summary

Revoke privileges

If you set up privilege-holding roles as describe above, you can revoke privileges by removing assigned roles. For example, to remove the readwrite role from readwrite_user1, run the following SQL statement:

Public schema privileges

When creating a new database, Postgres creates a schema named public in the database and permits access to the schema to a predefined Postgres role named public. Newly created roles in Postgres are automatically assigned the public role. In Postgres 14, the public role has CREATE and USAGE privileges on the public schema. In Postgres 15 and higher, the public role has only USAGE privileges on the public schema.

Why does this matter? If you create a new role and want to limit access for that role, you should be aware of the default public schema access automatically assigned to newly created roles.

If you want to limit access to the public schema for your users, you have to revoke privileges on the public schema explicitly.

For users of Postgres 14, the SQL statement to revoke the default CREATE permission on the public schema from the public role is as follows:

You must be the owner of the public schema or a member of a role that authorizes you to execute this SQL statement.

To restrict the public role’s capability to connect to a database, use this statement:

This ensures users are unable to connect to a database by default unless this permission is explicitly granted.

More information

For more information about granting privileges in Postgres, please see the GRANT command in the PostgreSQL documentation.

Last updated on

Edit this page
Was this page helpful?