Database users and privileges

Two ways to register

• Register an administrator connection string: If the connection string has privileges to create users (CREATEROLE in PostgreSQL, CREATE USER in MySQL, and so on), Scandock automatically creates a read-only monitoring user (named scandock_...) and uses only that user for monitoring and investigation. The administrator connection string is stored encrypted and used only to run SQL actions a person has approved. Deleting the connection also deletes the user Scandock created.
• Create a read-only user yourself: Register the connection string of a user created with the steps below. In this case, SQL actions are not available (notification actions are).

How read-only is checked

When you register, Scandock checks that the user cannot write. If it has any write privilege, the connection string is treated as an administrator one and Scandock tries to create a monitoring user. Without the privilege to create users, registration fails.

• PostgreSQL: No superuser or CREATEROLE, and no INSERT, UPDATE, DELETE or TRUNCATE privilege on any table. Connections to a read replica are treated as read-only.
• MySQL, MariaDB, TiDB: Cannot create temporary tables (do not grant CREATE TEMPORARY TABLES).
• SQL Server: No db_owner, db_datawriter or db_ddladmin role, no CREATE TABLE permission, and no INSERT, UPDATE or DELETE permission on any table. A read-only database is treated as read-only.

PostgreSQL

CREATE ROLE scandock_ro LOGIN PASSWORD 'a-strong-password';
GRANT CONNECT ON DATABASE app TO scandock_ro;
-- PostgreSQL 14 and later: read tables in every schema
GRANT pg_read_all_data TO scandock_ro;
-- to read statistics such as running queries and locks
GRANT pg_monitor TO scandock_ro;

GRANT USAGE ON SCHEMA public TO scandock_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO scandock_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO scandock_ro;

MySQL, MariaDB, TiDB

CREATE USER 'scandock_ro'@'%' IDENTIFIED BY 'a-strong-password';
GRANT SELECT ON app.* TO 'scandock_ro'@'%';
-- to see running queries
GRANT PROCESS ON *.* TO 'scandock_ro'@'%';
-- to read per-query statistics
GRANT SELECT ON performance_schema.* TO 'scandock_ro'@'%';

Azure SQL Database

-- run in the database to monitor
CREATE USER scandock_ro WITH PASSWORD = 'a-strong-password';
ALTER ROLE db_datareader ADD MEMBER scandock_ro;
GRANT VIEW DATABASE STATE TO scandock_ro;

SQL Server

-- run in master
CREATE LOGIN scandock_ro WITH PASSWORD = 'a-strong-password', CHECK_POLICY = ON;
GRANT VIEW SERVER STATE TO scandock_ro;
-- run in the database to monitor
CREATE USER scandock_ro FOR LOGIN scandock_ro;
ALTER ROLE db_datareader ADD MEMBER scandock_ro;
GRANT VIEW DATABASE STATE TO scandock_ro;

Read restrictions

Monitoring connections also fix the session to read-only. Queries generated by the AI run only as single read-only statements.