For AI agents: the complete documentation index is available at https://docs.ovhcloud.com/en/llms.txt, the full documentation bundle is available at https://docs.ovhcloud.com/en/llms-full.txt, and this page is available as Markdown at https://docs.ovhcloud.com/en/guides/public-cloud/databases/postgresql-users-roles-grants.md.

Manage users, roles and privileges of Public Cloud Databases for PostgreSQL

View as Markdown

Find out how to create users via the OVHcloud Control Panel or SQL and manage their roles and privileges in SQL on Public Cloud Databases for PostgreSQL

Objective

Public Cloud Databases allow you to focus on building and deploying cloud applications while OVHcloud takes care of the database infrastructure and maintenance in operational conditions.

The OVHcloud Control Panel and the OVHcloud API let you create users, but not grant them privileges on your data: rights on databases, schemas and tables are declared in SQL, with standard PostgreSQL statements.

This guide explains how responsibilities are split between the OVHcloud Control Panel and PostgreSQL, and provides the usual commands to create application users and grant them privileges.

Requirements

OVHcloud Control Panel Access

  • Direct link:
  • Navigation path: Public Cloud > Select your project > Databases

Concept

User management in the OVHcloud Control Panel

From the Users tab of your service (or via the OVHcloud API), you can:

  • create a user, whose password is generated by the service;
  • assign it the replication role, the only role offered;
  • regenerate its password;
  • delete it.
Warning

When you regenerate a password via the OVHcloud API, the new password is returned in clear text in the response. Treat this response as a secret: do not log it, and store the password directly in your secrets manager.

Role and privilege management in SQL

Everything else falls under standard PostgreSQL roles and privileges, which you administer yourself via the CLI or from your code: creating users with specific privileges, role membership, rights on databases, schemas, tables, sequences and functions, default privileges, and per-role settings (statement_timeout, connection limit, etc.).

Management of user rights and privileges is not supported via the OVHcloud Control Panel or the OVHcloud API. We rely on the official PostgreSQL roles and privileges, without modifying them.

Warning

Users and roles created in SQL do not appear in the user list of the Control Panel or of the OVHcloud API. You therefore cannot regenerate their password or delete them from the Control Panel: these operations are done in SQL (ALTER ROLE, DROP ROLE). To list all roles, use the \du command in psql.

The avnadmin user

The first user, avnadmin, is created with the service. It has the following attributes:

  LOGIN
  NOSUPERUSER
  INHERIT
  CREATEDB
  CREATEROLE
  REPLICATION

This is your administration user: it can create databases, create roles and grant privileges on the objects it owns. It is not a superuser: the SUPERUSER attribute is reserved for OVHcloud's operation of the service.

Warning

We recommend not using avnadmin as an application account. Create a dedicated user for each application, with only the privileges it needs. This limits the impact of a credential leak and prevents an application from dropping objects it did not create.

The replication role

The replication role offered in the Control Panel corresponds to the PostgreSQL REPLICATION attribute. It allows the user to open a replication connection to the service and to create or consume replication slots: this is what logical replication and Change Data Capture (CDC) tools need.

It grants no read or write rights on your tables. A user intended for CDC therefore needs both this attribute and the corresponding GRANTs (at least SELECT) on the objects to replicate.

Default behaviour of a new user

A newly created user can connect to databases, because the CONNECT privilege is granted to PUBLIC by default. It also inherits the privileges granted to the PUBLIC pseudo-role and to the roles it is a member of. In practice, it can connect and read the system catalogues, but cannot access tables it does not own until a privilege is granted to it.

Info

Since PostgreSQL 15, the PUBLIC pseudo-role no longer has the CREATE privilege on the public schema. A user who needs to create tables in this schema therefore needs an explicit GRANT CREATE, or to own the schema. This is the most frequent cause of permission denied for schema public errors when migrating from an earlier version.

Instructions

Run the SQL commands below as avnadmin, connected to the relevant database: privileges on schemas and tables are specific to each database.

Create an application user in SQL

For an application user with specific privileges, create it directly in SQL:

CREATE ROLE "app" LOGIN;

Then set its password with psql's \password meta-command, which hashes it client-side and keeps it out of the history and logs:

defaultdb=> \password app
Enter new password for user "app":
Enter it again:

You can also set safeguards specific to this user:

-- Automatically cancel queries running longer than 30 seconds
ALTER ROLE "app" SET statement_timeout = '30s';

-- Limit the number of concurrent connections for this user
ALTER ROLE "app" CONNECTION LIMIT 20;
Info

For a user that only needs the replication role, you can also use the Users tab of the Control Panel: click Add user, enter a username, add the replication role, then confirm with Create User. Note the displayed password and wait for the user's status to change to READY. Privileges on tables are then granted in SQL, as below.

Grant read-only access

Typical use case: a reporting tool or dashboard that queries your data without ever modifying it.

-- Allow access to the schema
GRANT USAGE ON SCHEMA public TO "reporting";

-- Read access to existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "reporting";

-- Read access to tables created later by avnadmin
ALTER DEFAULT PRIVILEGES FOR ROLE "avnadmin" IN SCHEMA public
  GRANT SELECT ON TABLES TO "reporting";
Warning

GRANT ... ON ALL TABLES only applies to tables that exist when it is run. Without the ALTER DEFAULT PRIVILEGES statement, any table created afterwards will be inaccessible to the user, and you will have to replay the GRANT. Default privileges only apply to objects created by the role named after FOR ROLE: if your tables are created by another user (for example app), also declare ALTER DEFAULT PRIVILEGES FOR ROLE "app" ....

Danger

Do not rely on the default_transaction_read_only setting to make a user "read-only": it only sets a default value, which the user can turn off in their session with SET default_transaction_read_only = off. Only the absence of write privileges (INSERT, UPDATE, DELETE, TRUNCATE, CREATE) actually prevents writes. You can combine both, but GRANTs remain the only effective protection.

Grant read and write access

Typical use case: the account used by your application in production.

GRANT USAGE ON SCHEMA public TO "app";

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO "app";
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO "app";

ALTER DEFAULT PRIVILEGES FOR ROLE "avnadmin" IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO "app";
ALTER DEFAULT PRIVILEGES FOR ROLE "avnadmin" IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO "app";

Do not forget sequences: without privileges on them, inserts into a table with a serial column will fail.

Give a schema to an application

If your application manages its own schema, for example with a migration tool, it is simpler to make it the owner of a dedicated schema rather than multiplying GRANTs:

CREATE SCHEMA "myapp" AUTHORIZATION "app";

The app user can then freely create, alter and drop objects in this schema, without touching the rest of the database.

Share privileges with a group role

When several users need the same privileges, define them once on a role without login rights, then add your users to it:

CREATE ROLE "readers" NOLOGIN;
GRANT USAGE ON SCHEMA public TO "readers";
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "readers";
ALTER DEFAULT PRIVILEGES FOR ROLE "avnadmin" IN SCHEMA public
  GRANT SELECT ON TABLES TO "readers";

GRANT "readers" TO "reporting";
GRANT "readers" TO "analytics";

Since roles have the INHERIT attribute by default, members automatically benefit from the group role's privileges.

Check granted privileges

To list all roles and their attributes, including those created in SQL:

defaultdb=> \du

To display the privileges granted on the tables of a schema:

defaultdb=> \dp public.*

To display the declared default privileges:

defaultdb=> \ddp

To check a specific privilege:

defaultdb=> SELECT has_table_privilege('reporting', 'public.orders', 'SELECT');

Delete a user

A user cannot be deleted while it owns objects or holds privileges. First transfer its objects and revoke its privileges, in each database where it has any:

-- avnadmin automatically holds the privileges of users created from the Control Panel
-- or by avnadmin itself. If the role was created by another role, run first:
-- GRANT "app" TO "avnadmin";

REASSIGN OWNED BY "app" TO "avnadmin";
DROP OWNED BY "app";

Then delete the user:

  • if it was created in SQL, with DROP ROLE "app"; (it does not appear in the Control Panel);
  • if it was created from the Control Panel, in the Users tab, by clicking ... to the right of the row, then Delete.

Go further

Configure incoming connections of a Public Cloud Databases for PostgreSQL service

Connect using the CLI for Public Cloud Databases for PostgreSQL

Secure the TLS connection to Public Cloud Databases for PostgreSQL

Capabilities and Limitations of Public Cloud Databases for PostgreSQL

Official PostgreSQL documentation on roles: https://www.postgresql.org/docs/current/user-manag.html and on privileges: https://www.postgresql.org/docs/current/ddl-priv.html

For training or technical assistance implementing our solutions, contact your sales representative or visit our Professional Services page to request a quote and have your project analysed by our experts.

We want your feedback!

We would love to help answer questions and appreciate any feedback you may have.

Are you on Discord? Connect to our channel at https://discord.gg/ovhcloud and interact directly with the team that builds our databases service!

Join our community of users.

Was this page helpful?