PostgreSQL permissions

Create the role that Scops uses on a PostgreSQL instance, give it what it needs, and add the optional parts for statements and the host.

What the role needs

Scops signs in as a PostgreSQL role with a password. Test Connection checks what the role holds, also through a group role. A superuser holds all of it.

Permission or settingUsed forRequired
Member of pg_monitorThe sessions, statistics and settings of every database.Yes
CONNECT on the Maintenance DB, postgres by defaultThe sessions that Scops reads the instance through.Yes
pg_stat_statements in shared_preload_librariesStatement statistics. A change needs a restart.No
pg_stat_statements created in the Maintenance DB, and SELECT on itStatement statistics.No
compute_query_id not offStatement statistics. With it off, PostgreSQL gives statements no id.No
track_io_timing onThe time of reads and writes, for example Block read time on Metrics.No

Without a required permission, you cannot add the instance. Without the optional part, Scops adds the instance. A line under the test result then says what Scops does not read yet.

Create the role

  1. Connect to the instance as a superuser.

  2. Create the role and give it pg_monitor:

    CREATE ROLE scops_monitor LOGIN PASSWORD '<password>';
    GRANT pg_monitor TO scops_monitor;
    GRANT CONNECT ON DATABASE postgres TO scops_monitor;
  3. Add a line to pg_hba.conf that lets the role in from the Scops server, then reload the configuration:

    host all scops_monitor 10.0.0.7/32 scram-sha-256
    SELECT pg_reload_conf();
  4. In Add Database, save the role as a DB credential and click Test Connection.

The line says all because Scops opens a session in each database that it reads. Use the address of the Scops server. Use md5 only when password_encryption is md5.

Run the script from Test Connection

  1. Click Test Connection. If something is missing, click Show Script. The Grant Script window opens.

  2. If the window has a Monitoring tab, select Existing Role to grant to the role that signed in. The window opens on it. New Role creates a role and grants to it.

  3. If the tab has Steps, do them by hand.

  4. Copy the script and ask a superuser to run it.

  5. Click Test Connection again.

TabWhat it holdsRequired
MonitoringThe missing pg_monitor and CONNECT grants. With New Role, also the role and the line for pg_hba.conf.Yes
Statement StatisticsThe steps and statements for pg_stat_statements, compute_query_id and track_io_timing. The name of the tab says what is missing, for example I/O Timing.No

A tab shows only when it has something to do. For example, the optional tab can hold this step and script:

  • In postgresql.conf set shared_preload_libraries = 'pg_stat_statements', then restart PostgreSQL.
  • Run the statements in the database postgres.
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
ALTER SYSTEM SET compute_query_id = auto;
ALTER SYSTEM SET track_io_timing = on;
SELECT pg_reload_conf();

On a managed service, there is no postgresql.conf or pg_hba.conf to edit. The steps then tell you which parameters to set in the settings of the service.

Fix a missing permission

  1. On the Databases page, right-click the instance and select Edit Database.

  2. Click Test Connection, then Show Script.

  3. Ask a superuser to run the script.

Statement statistics

pg_stat_statements gives the text of each statement and what it cost. Scops reads it every ten minutes in the Maintenance DB. Without it:

  • Insights shows one sentence that says what is missing, in place of its lists.
  • The statement details have no text and no history.
  • The Statements list of the Dashboard has no text.

Scops reads version 1.10 of the extension or later. After pg_upgrade, run ALTER EXTENSION pg_stat_statements UPDATE in the Maintenance DB. The role must also be a member of pg_read_all_stats, which pg_monitor includes. Without it, PostgreSQL hides the statements of other roles.

Other optional permissions

PermissionUsed for
CONNECT on each databaseThe checks inside each database on Health Check, the tables of each database on Sizing, and the names of locked tables on Live Activity. By default, PUBLIC has CONNECT. If you revoked it, grant it to the role.
SELECT on the sequences, in each database: GRANT SELECT ON ALL SEQUENCES IN SCHEMA <schema> TO scops_monitor;ID columns nearest their limit on Health Check. pg_monitor does not give the last value of a sequence.
The pgstattuple extension in a databaseThe Bloat measure of a table on Sizing. The role needs pg_stat_scan_tables, which pg_monitor includes.

The OS credential

The OS credential is optional. Scops then signs in to the host of the instance over SSH, with the user and password of the credential, at its port, usually 22. The host must allow password sign-in over SSH. Scops runs no command with sudo. To add it, select an OS credential in Add Database or Edit Database.

With the OS credential, Scops readsShows on
The host name, the operating system, the CPU count and the memoryThe test result, and the CPU line of the Dashboard.
CPU, I/O wait, load and free memory, every minute. Linux only.The Host preset of Metrics.
The free space of the file systemsSizing, and Disks on Health Check.
The server logThe Server log check, the details on the Deadlocks tab of Live Activity, and Log Watch.

Without it, the pages say that these values are not read. On a managed service, Scops does not read the host.

Let the SSH user read the server log

  • Turn on logging_collector, and send the log to stderr, csvlog or jsonlog. Scops does not read syslog. On Debian and Ubuntu, Scops also finds the log file of the package.
  • Set log_file_mode = 0640, and put the SSH user in the group of the PostgreSQL files, usually postgres. The mode applies from the next log file.
  • If the log is in the data directory and only postgres can open it, set log_directory to a directory outside it, for example /var/log/postgresql.
  • Scops searches the log for the English messages of PostgreSQL. If lc_messages is a different language, set lc_messages = 'C'.

The test result says which log file Scops reads, or what to change. A problem with the log or the disk does not stop the registration. If SSH cannot sign in, the test fails: correct the OS credential, or clear it.

Updated November 22, 2026