Set up the database monitoring account

octoja monitors Microsoft SQL Server, MySQL / MariaDB and PostgreSQL with six checks — a Health check per engine, a Query check for MySQL / MariaDB and PostgreSQL, and an Activity check for Microsoft SQL Server. Every one of them signs in to the database as an ordinary client, so the work that decides whether they succeed happens on the database server, not in octoja: a login with just enough rights to read the statistics, and a server that accepts that login from the address the agent connects from.

That is what this article covers. The individual check fields — thresholds, intervals, which databases to watch — are explained in the check editor while you fill them in.

What applies to all three engines

• Create a dedicated account. Never point a check at an administrative account or at the application’s own database user. A separate login can be rotated or revoked without touching anything else, and it is unmistakable in the process list when you are hunting a connection.
• Read-only is enough. The Health checks read server-level statistics; the Query checks read only what your own statement touches. Nothing needs write access.
• One check per database server, not one per device — for MySQL / MariaDB and PostgreSQL. Their host is part of the check configuration, so rolling them out to a device group through a config package makes every matched agent open its own connection to the same database. The two SQL Server checks have no host field at all: they discover the local instances of the device they run on, so assign them to the SQL Server machine itself.
• Verify from the machine the agent runs on before you configure anything in octoja. A login that works on the database server itself proves nothing about a connection one hop away.

Microsoft SQL Server

Both SQL Server checks read server-level state. Decide how they authenticate, then grant that identity the handful of read permissions they need.

Pick an authentication mode

Both checks support the same two modes. You choose by filling in — or leaving empty — the SQL username and SQL password fields, which appear once you turn on Show advanced options in the check editor.

Windows authentication is the simpler choice on a single-purpose database server: nothing to rotate, nothing to store. A dedicated SQL login is the better choice when you want the monitoring identity to be visible and revocable on its own, when NT AUTHORITY\SYSTEM is deliberately restricted on the instance, or when the same credentials have to work across several instances that do not share a Windows trust.

Grant the login only what the checks read

Both checks are read-only against your databases. They never read table data out of them — they read server-level state, the database list, the backup and job history in msdb, and volume statistics. That maps onto a small set of server-level permissions:

Missing VIEW SERVER STATE is the trap. The Health check turns Critical, which is what you want. The Activity check, however, treats an unreachable instance as somebody else's problem and reports OK — so it goes permanently, quietly green. If Activity never leaves OK while Health complains about the connection, this permission is the first thing to check.

A dedicated SQL login

Run this once on each instance you want monitored, replacing the placeholder password:

CREATE LOGIN [octoja_monitor]
    WITH PASSWORD = N'<strong-password>', CHECK_POLICY = ON;
GO

GRANT VIEW SERVER STATE   TO [octoja_monitor];
GRANT VIEW ANY DATABASE   TO [octoja_monitor];
GRANT VIEW ANY DEFINITION TO [octoja_monitor];
GO

USE msdb;
GO
CREATE USER [octoja_monitor] FOR LOGIN [octoja_monitor];
GRANT SELECT ON dbo.backupset TO [octoja_monitor];
ALTER ROLE SQLAgentReaderRole ADD MEMBER [octoja_monitor];
GO

On a server with several instances, create the login with the same name and the same password on each of them — the check sends one set of credentials to all instances it finds.

Windows authentication instead

To keep the check password-free, grant the same set to the agent's identity:

-- The octoja agent service runs as LocalSystem, so it arrives as NT AUTHORITY\SYSTEM.
-- Skip CREATE LOGIN if this login already exists on the instance.
CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS;
GO

GRANT VIEW SERVER STATE   TO [NT AUTHORITY\SYSTEM];
GRANT VIEW ANY DATABASE   TO [NT AUTHORITY\SYSTEM];
GRANT VIEW ANY DEFINITION TO [NT AUTHORITY\SYSTEM];
GO

USE msdb;
GO
CREATE USER [NT AUTHORITY\SYSTEM] FOR LOGIN [NT AUTHORITY\SYSTEM];
GRANT SELECT ON dbo.backupset TO [NT AUTHORITY\SYSTEM];
ALTER ROLE SQLAgentReaderRole ADD MEMBER [NT AUTHORITY\SYSTEM];
GO

On many instances this login already exists and already has more than enough rights, in which case the grants are a no-op. Add what is missing, but leave the account's existing memberships alone — Windows and SQL Server servicing use it.

What the login does not need

• Not sysadmin, and no other fixed server role.
• No database roles in your user databases — not db_owner, not even db_datareader. The checks never read your data.
• No write permission in your databases — both checks only read them. The single write either check performs is a temporary table in tempdb, which the Activity check uses while reading the error log, and every login may create one. There is nothing to grant for it.
• The one exception is the Activity check's Monitor SQL Server error log option: reading the error log goes through xp_readerrorlog, which requires the securityadmin or sysadmin role. That is far more than monitoring should hold, so on a least-privilege login either turn the option off, or leave it on and accept that the section stays empty — a denied error log is reported as unavailable and never raises an alert.

MySQL and MariaDB

Two decisions decide whether this works on the first try. Both of them are made in the database, not in octoja.

The account must allow the agent's address, not the database server's. MySQL accounts are user@host pairs, and the host half is matched against where the connection comes from. If the agent runs on the database server itself, that is localhost / 127.0.0.1. If the agent runs anywhere else — a jump host, a hypervisor, any other managed device — it is that machine's address. A grant written for 'octoja_monitor'@'localhost' rejects an agent one hop away, and the check then reports Authentication failed with a password that is perfectly correct.

The Query check counts rows, not values. It executes your statement and counts the rows in the result set. SELECT count(*) FROM orders WHERE status = 'stuck' always returns exactly one row, so it always evaluates as 1 — no matter how many stuck orders there are. Write the query so it returns one row per problem instead. See What the query can and cannot do below. Row count is the default Alert on mode — the radio's second option, Execution time (ms), alerts on how long the statement takes instead. Warning threshold and Error threshold (both default 1) set the limits, and Alert when below threshold inverts the comparison for cases where too few rows are the problem.

Create the monitoring account

Three short steps on the database server, in order: choose which address the account is allowed to connect from, create it with the privileges the checks actually need, and confirm the server agrees before you configure anything in octoja.

1. Decide the host part

Write the grant for the address the agent connects from:

2. Create the account and grant it

This is everything the MySQL / MariaDB Health check needs:

CREATE USER 'octoja_monitor'@'10.20.0.15' IDENTIFIED BY 'a-long-random-password';
GRANT PROCESS ON *.* TO 'octoja_monitor'@'10.20.0.15';

• Being able to log in is already enough for connect time and uptime. CREATE USER grants the implicit USAGE privilege, and the uptime figure comes from a global status counter that needs no rights on any schema.
• PROCESS is what makes the reported thread count real. octoja counts the rows of information_schema.PROCESSLIST, and without PROCESS an account sees only its own connection — the check then reports Threads: 1 on every single run. PROCESS is a global privilege, so it has to be granted ON *.*; it does not give access to any table data.

For the MySQL / MariaDB Query check, add read access to exactly what the query touches — and nothing wider:

GRANT SELECT ON shop.* TO 'octoja_monitor'@'10.20.0.15';

If the query only reads one table, be more specific still: GRANT SELECT ON shop.orders TO 'octoja_monitor'@'10.20.0.15';

Do not reuse the application's database user. A dedicated account can be rotated or disabled without touching the application, it shows up unmistakably in the process list when you are hunting a connection, and a SELECT-only account cannot be turned into a write path by a mistyped query.

Repeat the CREATE USER and GRANT lines for each host part you decided on in step 1 — grants are per user@host pair, so 'octoja_monitor'@'localhost' and 'octoja_monitor'@'127.0.0.1' are two separate accounts that each need their own privileges.

3. Verify before you touch octoja

Check what the server thinks the account may do:

SHOW GRANTS FOR 'octoja_monitor'@'10.20.0.15';

Then log in from the machine the agent runs on — that is the whole point of the exercise:

mysql -h db01.example.com -P 3306 -u octoja_monitor -p -e "SELECT 1"

If that succeeds, the check will succeed. If there is no MySQL client on that machine, at least confirm that the port answers from there.

A server that only listens on loopback can never be reached remotely. Several MySQL and MariaDB distribution packages ship with the listening address bound to 127.0.0.1. No grant can work around that. If a remote login is refused or times out while a local one succeeds, check the server's bind address and the host firewall before you touch the account again.

PostgreSQL

Two things decide whether the PostgreSQL checks connect: how they reach the server, and whether the monitoring role may read the statistics views.

These checks connect over TCP — always

This is the single most common false start. The PostgreSQL checks are ordinary PostgreSQL clients. They do not read the data directory, they do not talk to a local service manager, and installing the octoja agent on the database server does not give them a shortcut. Even when host is 127.0.0.1, the connection arrives as a TCP connection and is matched against the host lines in pg_hba.conf — never the local (Unix-socket) lines. A local all all peer entry will not authenticate these checks.

One check per server, not one per device. The host is part of the check configuration, so rolling these checks out to a whole device group through a config package means every matched agent opens its own connection to the same database. Assign them to a single device — ideally the database server.

Prepare PostgreSQL

Two things have to line up before the check can connect: a role with just enough rights to read the statistics, and a pg_hba.conf entry that lets that role in from the agent’s address. Do them in this order.

1. Create a read-only monitoring role

Do not point the checks at postgres or at an application role. Create a dedicated login role — if its password ever leaks, you rotate one credential that can only read.

CREATE ROLE octoja_monitor WITH LOGIN PASSWORD 'a-long-random-password';

2. Grant pg_monitor

This is the grant that actually matters, and it is easy to skip because the check still goes green without it.

PostgreSQL hides the interesting columns of pg_stat_activity from ordinary roles: for backends belonging to other roles, the state column reads as NULL. The PostgreSQL Health check counts sessions with state = 'active', so an unprivileged monitoring role sees exactly one — its own — no matter how busy the server is. The check reports OK, the number is meaningless, and nothing tells you.

pg_monitor is a built-in role that exists on every supported PostgreSQL version. It bundles pg_read_all_stats, pg_read_all_settings and pg_stat_scan_tables — read-only access to the statistics views and configuration, and nothing else. pg_read_all_stats alone is enough for these two checks; pg_monitor is the conventional grant and leaves room for future statistics.

GRANT pg_monitor TO octoja_monitor;

The database-size figure works without this grant (it only needs CONNECT on the database), which is why a half-configured role looks convincing at a glance. Check the Active sessions number on a server you know is busy — if it sits at 1 forever, the grant is missing.

3. Grant read access for the Query check

PostgreSQL Health needs nothing beyond the above. PostgreSQL Query reads your tables, so it needs the usual read chain on the database it connects to:

GRANT CONNECT ON DATABASE your_database TO octoja_monitor;
GRANT USAGE ON SCHEMA public TO octoja_monitor;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO octoja_monitor;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO octoja_monitor;

Substitute your own database and schema names. The last statement only covers tables created later by the role that runs it — run it as the role that owns the application's tables, or new tables will not inherit the grant and the check will start failing with a privilege error one day.

Grant SELECT only. octoja runs your query inside a READ ONLY transaction and rolls it back afterwards, so the server rejects anything that would write — but treat that as a seatbelt, not as the lock.

4. Let the agent through pg_hba.conf

pg_hba.conf decides which role may connect from which address with which authentication method. Ask the server where the file is rather than guessing — distribution packages move it out of the data directory:

SHOW hba_file;
SHOW config_file;

Add a line for the monitoring role. Rules are evaluated top to bottom and the first matching line wins — if a broader reject or a restrictive line sits above yours, yours never runs.

# agent on the database server itself
host    all    octoja_monitor    127.0.0.1/32    scram-sha-256

# agent on another host
host    all    octoja_monitor    10.0.0.25/32    scram-sha-256

Then reload — a pg_hba.conf change does not need a restart:

SELECT pg_reload_conf();

If the agent is not on the database server, also confirm listen_addresses in postgresql.conf includes an address the agent can reach. A stock server listens on localhost only. Unlike pg_hba.conf, listen_addresses takes effect on restart, not on reload.

scram-sha-256 or md5? Match the method to how the role's password was stored. A role whose password was set while the server had password_encryption = md5 cannot authenticate against a scram-sha-256 line. If that bites you, re-set the password after switching the setting — the hash is written at ALTER ROLE ... PASSWORD time.

5. Verify from the agent host

Prove the path works before you touch octoja. From the device the agent runs on:

psql "host=10.0.0.10 port=5432 dbname=postgres user=octoja_monitor sslmode=prefer"

Then run the two statements the Health check itself issues:

SELECT count(*) FROM pg_stat_activity WHERE state = 'active';
SELECT pg_database_size(current_database());

Do not read that first number as a count of connected clients. It counts only the backends that are executing a statement at that instant — a client that is connected but waiting reports idle, not active — so on a server that is merely connected-to rather than working, 1 (your own session, running that very query) is the honest answer and says nothing about the grant. To test the pg_monitor grant itself, read the state column directly:

SELECT usename, state FROM pg_stat_activity;

Without the grant, state is NULL for every backend except your own. With it, you see each backend's real state.

If psql connects and the statements answer, the role, the pg_hba.conf line and the network path are proven — the grants are not. Both statements the Health check issues answer without pg_monitor, so only the state column test above settles step 2, and nothing here reads the tables the Query check will touch, so the step 3 grants stay untested until you run your own query through psql as well. The check still runs within the agent's service rather than in your session, so that is the one variable left; see the last tip below if it fails anyway. If psql does not connect, the error it prints is the same one octoja will show you — and it is easier to read here.

Add the checks in octoja

You need the Monitoring Check Management permission; administrators assign permissions under Administration → Groups.

1. Go to Devices and open the device that will run the check — for a database server with an agent, that server itself.
2. Click the Checks tab → Add Check and pick the check for your engine.
3. Enter the connection details and the credentials you created above, then fill in the thresholds. Each field explains itself in the editor.
4. Click Add Check.

Tips

• Grant the monitoring account on every instance you want watched. On a Microsoft SQL Server host with several instances, use the same login name and password on each — the check sends one set of credentials to all instances it finds.
• A server that listens only on loopback can never be reached from another device, whatever the grant says. If a remote login fails while a local one succeeds, check the listening address and the host firewall before touching the account again.
• Keep the Query checks narrow. Grant SELECT on exactly the schema or table the statement reads, and write the statement so it returns one row per problem — the Query checks count rows, not values.

See also: Checks · Assign checks with config packages