For AI agents: The documentation index is at https://docs.digitalocean.com/llms.txt. Markdown versions of pages use the same URL with index.html.md in place of the HTML page (for example, append index.html.md to the directory path instead of opening the HTML document).

By default, PostgreSQL database clusters include a user, doadmin, with full access to every database you create. Instead of using doadmin to access the database, we recommend creating additional users with only the minimum required privileges, following the principle of least privilege.

Every database cluster is publicly accessible by default. To limit access, you can add trusted sources or manage user permissions by following this guide.

You can create a new user in the Control Panel, but setting a user’s privileges currently requires a command-line PostgreSQL client such as psql.

Modify PostgreSQL User Permissions

First, retrieve your cluster’s connection details using the Control Panel, API, or CLI. Then, connect to your cluster as the admin user, doadmin, by passing the connection URI to psql.

The URI sets the client sslmode parameter to require, which encrypts traffic in transit but does not verify the server identity.

psql "postgresql://doadmin:<your-password>@<your-cluster-hostname>:25060/defaultdb?sslmode=require"

On PostgreSQL Standard Edition clusters, you can use sslmode=verify-full to increase TLS verification with sslmode by also verifying the server identity. To use sslmode=verify-full, set sslrootcert to the path of your downloaded CA certificate.

psql "postgresql://doadmin:<your-password>@<your-cluster-hostname>:25060/defaultdb?sslmode=verify-full&sslrootcert=<path-to-ca-certificate>"

This brings you into the interactive shell for PostgreSQL, which changes your command prompt to defaultdb=>.

Next, connect to the database where you want to modify the user’s privileges.

defaultdb=> \connect example_database

Connecting to the database changes the command prompt to the database’s name and displays output like this:

Output
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "example_database" as user "doadmin".

The commands you need to execute depend on the permissions you want the user to have. See PostgreSQL privileges documentation for available grant and revoke options.

As an example, to make a read-only user, first revoke any database-level privileges the user has on the target database, then grant CONNECT access and USAGE on the public schema. Add SELECT privileges on existing tables and set default SELECT privileges for tables created in the future.

REVOKE ALL ON DATABASE example_database FROM example_user;
GRANT CONNECT ON DATABASE example_database TO example_user;
GRANT USAGE ON SCHEMA public TO example_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO example_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO example_user;

The final command sets default privileges for tables that doadmin creates after you run it. To set defaults for tables created by another role, add FOR ROLE other_role to the ALTER DEFAULT PRIVILEGES command.

Run these commands on each database where you want to grant these privileges.

You can also modify these commands to give the user different permissions. For example, to allow read and write access, add GRANT INSERT ON ALL TABLES IN SCHEMA public TO example_user; and ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT ON TABLES TO example_user; while keeping the existing SELECT grants.

Configure a PMM Monitoring User

Percona Monitoring and Management (PMM) collects PostgreSQL metrics and Query Analytics (QAN) data from a dedicated database user. Managed PostgreSQL does not allow superuser access (see PostgreSQL Limits), but you can configure PMM without superuser privileges by granting predefined PostgreSQL roles to a monitoring user.

This section describes the DigitalOcean-specific user and privilege setup. For PMM server deployment and adding a PostgreSQL service in PMM, see the PMM documentation. You can deploy a PMM server on a Droplet using the PMM 1-Click App.

Prerequisites

Before you grant monitoring privileges, confirm the following:

• You deployed a PMM server and can add PostgreSQL instances from PMM (see the PMM documentation).
• You can connect to the cluster as doadmin using psql.
• You added the PMM server as a trusted source on the database cluster so PMM can reach the database.

Create the Monitoring User

Create a database user named pmm_monitor (or another name you choose) using the Control Panel, doctl, or the API. See Manage PostgreSQL Users and Databases for Control Panel steps and Manage Database Users Using Automation for doctl and API examples.

Grant Monitoring Privileges

Connect to your database cluster as doadmin, then connect to each database you want PMM to monitor for QAN:

\connect example_database

Run the following in each target database:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
GRANT SELECT ON pg_stat_statements TO pmm_monitor;

Grant the predefined pg_read_all_stats role once while connected as doadmin. This role membership applies cluster-wide and provides read access to pg_stat_* views, pg_stat_activity, pg_stat_replication, and other statistics views without superuser access:

GRANT pg_read_all_stats TO pmm_monitor;

The PMM documentation also describes the pg_monitor predefined role for non-superuser monitoring. On Managed PostgreSQL, grant pg_read_all_stats instead. That role provides read access to the pg_stat_* views PMM needs without superuser privileges.

Repeat the extension and GRANT SELECT ON pg_stat_statements steps on every database where you want QAN. You only need to run GRANT pg_read_all_stats once.

After you configure the user, add the PostgreSQL instance in PMM using the monitoring user’s credentials. See the PMM documentation.

Check PostgreSQL User Privileges

After you connect to your database cluster, you can use the \du command to list users that currently exist and see their role attributes. This command shows roles, not table-level grants.

\du

Output
                                       List of roles
 Role name     |                         Attributes                         | Member of
---------------+------------------------------------------------------------+-----------
 \_dodb        | Superuser, Replication                                     | {}
 doadmin       | Create role, Create DB, Replication, Bypass RLS            | {}
 postgres      | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 example_user  |                                                            | {}

You can verify that a privilege change completed successfully by querying the database privileges table for the user:

SELECT table_catalog, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE grantee = 'example_user';

The output displays the new privileges. For example, if you granted only SELECT privileges, the output looks like this:

Output
 table_catalog    | table_schema | table_name | privilege_type
------------------+--------------+------------+----------------
 example_database | public       | account    | SELECT

You can also verify that the user’s permissions changed by logging in to the database cluster as the new user, then connecting to the database and testing commands. For example, if you try to INSERT into a database as a read-only user, you receive an error like ERROR: permission denied for table account.