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 setting | Used for | Required |
|---|---|---|
Member of pg_monitor | The sessions, statistics and settings of every database. | Yes |
CONNECT on the Maintenance DB, postgres by default | The sessions that Scops reads the instance through. | Yes |
pg_stat_statements in shared_preload_libraries | Statement statistics. A change needs a restart. | No |
pg_stat_statements created in the Maintenance DB, and SELECT on it | Statement statistics. | No |
compute_query_id not off | Statement statistics. With it off, PostgreSQL gives statements no id. | No |
track_io_timing on | The 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
Connect to the instance as a superuser.
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;Add a line to
pg_hba.confthat lets the role in from the Scops server, then reload the configuration:host all scops_monitor 10.0.0.7/32 scram-sha-256SELECT pg_reload_conf();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
Click Test Connection. If something is missing, click Show Script. The Grant Script window opens.
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.
If the tab has Steps, do them by hand.
Copy the script and ask a superuser to run it.
Click Test Connection again.
| Tab | What it holds | Required |
|---|---|---|
| Monitoring | The missing pg_monitor and CONNECT grants. With New Role, also the role and the line for pg_hba.conf. | Yes |
| Statement Statistics | The 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.confsetshared_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
On the Databases page, right-click the instance and select Edit Database.
Click Test Connection, then Show Script.
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
| Permission | Used for |
|---|---|
| CONNECT on each database | The 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 database | The 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 reads | Shows on |
|---|---|
| The host name, the operating system, the CPU count and the memory | The 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 systems | Sizing, and Disks on Health Check. |
| The server log | The 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 tostderr,csvlogorjsonlog. Scops does not readsyslog. 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, usuallypostgres. The mode applies from the next log file. - If the log is in the data directory and only
postgrescan open it, setlog_directoryto a directory outside it, for example/var/log/postgresql. - Scops searches the log for the English messages of PostgreSQL. If
lc_messagesis a different language, setlc_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.
Related
Updated November 22, 2026