--- title: "Manage users, roles and privileges of Public Cloud Databases for PostgreSQL" description: "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" url: https://docs.ovhcloud.com/pt/guides/public-cloud/databases/postgresql-users-roles-grants lang: en lastUpdated: 2026-10-06 --- > For AI agents: the complete documentation index is available at https://docs.ovhcloud.com/pt/llms.txt, the full documentation bundle is available at https://docs.ovhcloud.com/pt/llms-full.txt. # Manage users, roles and privileges of 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 - An active [Public Cloud project](https://www.ovhcloud.com/pt/public-cloud/) in your OVHcloud account - Access to the OVHcloud Control Panel - A PostgreSQL database running on your OVHcloud Public Cloud Databases (the [Getting started with Public Cloud Databases](https://docs.ovhcloud.com/pt/guides/public-cloud/databases/getting-started.md) guide can help you to meet this requirement) - Command-line access to your service with the **avnadmin** user (the [Connect using the CLI for Public Cloud Databases for PostgreSQL](https://docs.ovhcloud.com/pt/guides/public-cloud/databases/postgresql-connect-cli.md) guide can help you to meet this requirement) *** ### OVHcloud Control Panel Access - **Direct link:** All my Public Cloud projects - **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: ```console 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 `GRANT`s (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: ```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: ```console defaultdb=> \password app Enter new password for user "app": Enter it again: ``` You can also set safeguards specific to this user: ```sql -- 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. ```sql -- 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 `GRANT`s remain the only effective protection. ::: ### Grant read and write access Typical use case: the account used by your application in production. ```sql 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 `GRANT`s: ```sql 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: ```sql 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: ```sql defaultdb=> \du ``` To display the privileges granted on the tables of a schema: ```sql defaultdb=> \dp public.* ``` To display the declared default privileges: ```sql defaultdb=> \ddp ``` To check a specific privilege: ```sql 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: ```sql -- 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](https://docs.ovhcloud.com/pt/guides/public-cloud/databases/postgresql-prepare-for-incoming-connections.md) [Connect using the CLI for Public Cloud Databases for PostgreSQL](https://docs.ovhcloud.com/pt/guides/public-cloud/databases/postgresql-connect-cli.md) [Secure the TLS connection to Public Cloud Databases for PostgreSQL](https://docs.ovhcloud.com/pt/guides/public-cloud/databases/postgresql-secure-connection-tls.md) [Capabilities and Limitations of Public Cloud Databases for PostgreSQL](https://docs.ovhcloud.com/pt/guides/public-cloud/databases/postgresql-capabilities.md) Official PostgreSQL documentation on roles: [https://www.postgresql.org/docs/current/user-manag.html](https://www.postgresql.org/docs/current/user-manag.html) and on privileges: [https://www.postgresql.org/docs/current/ddl-priv.html](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](https://www.ovhcloud.com/pt/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](https://discord.gg/ovhcloud) and interact directly with the team that builds our databases service! Join our [community of users](https://community.ovhcloud.com/).