Private Agent for PostgreSQL and MySQL (Early Access)

SET password_encryption = 'scram-sha-256';
CREATE USER rootly_agent WITH PASSWORD '<generated-secret>'
  NOSUPERUSER NOCREATEDB NOCREATEROLE;
GRANT CONNECT ON DATABASE production TO rootly_agent;
GRANT USAGE ON SCHEMA app TO rootly_agent;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO rootly_agent;
ALTER DEFAULT PRIVILEGES FOR ROLE <table_owner> IN SCHEMA app GRANT SELECT ON TABLES TO rootly_agent;

SELECT has_database_privilege('rootly_agent', 'production', 'TEMPORARY');
SELECT has_schema_privilege('rootly_agent', 'public', 'CREATE');
SELECT p.oid::regprocedure AS executable_routine
FROM pg_proc AS p
JOIN pg_namespace AS n ON n.oid = p.pronamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND has_schema_privilege('rootly_agent', n.oid, 'USAGE')
  AND has_function_privilege('rootly_agent', p.oid, 'EXECUTE');

REVOKE TEMPORARY ON DATABASE production FROM PUBLIC;
GRANT TEMPORARY ON DATABASE production TO <legitimate_temp_role>;

REVOKE CREATE ON SCHEMA public FROM PUBLIC;
GRANT CREATE ON SCHEMA public TO <legitimate_creator_role>;

REVOKE EXECUTE ON ALL ROUTINES IN SCHEMA <routine_schema> FROM PUBLIC;
GRANT EXECUTE ON ALL ROUTINES IN SCHEMA <routine_schema> TO <legitimate_runtime_role>;

ALTER DEFAULT PRIVILEGES FOR ROLE <routine_owner> IN SCHEMA <routine_schema>
  REVOKE EXECUTE ON ROUTINES FROM PUBLIC;
ALTER DEFAULT PRIVILEGES FOR ROLE <routine_owner> IN SCHEMA <routine_schema>
  GRANT EXECUTE ON ROUTINES TO <legitimate_runtime_role>;

CREATE USER 'rootly_agent'@'10.%'
  IDENTIFIED WITH caching_sha2_password BY '<generated-secret>' REQUIRE SSL;
GRANT SELECT, SHOW VIEW ON app.* TO 'rootly_agent'@'10.%';
GRANT SELECT ON performance_schema.* TO 'rootly_agent'@'10.%';
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'rootly_agent'@'10.%';

providers:
  postgresql:
    - id: postgresql-checkout-production
      host: postgres.production.internal
      port: 5432
      database: production
      username_file: /run/secrets/postgresql/username
      password_file: /run/secrets/postgresql/password
      ca_bundle_file: /run/secrets/postgresql/ca.crt
      allowed_schemas:
        - app
      execute_sql_enabled: true
      policy:
        maximum_concurrency: 2
        maximum_rows: 200
        maximum_result_bytes: 262144
        maximum_query_bytes: 16384
        maximum_timeout_seconds: 15

  mysql:
    - id: mysql-billing-production
      host: mysql.production.internal
      port: 3306
      database: app
      username_file: /run/secrets/mysql/username
      password_file: /run/secrets/mysql/password
      ca_bundle_file: /run/secrets/mysql/ca.crt
      allowed_schemas:
        - app
      execute_sql_enabled: false

podSecurityContext:
  fsGroup: 65532

extraVolumes:
  - name: postgresql-credentials
    secret:
      secretName: rootly-private-agent-postgresql
      defaultMode: 0440
  - name: mysql-credentials
    secret:
      secretName: rootly-private-agent-mysql
      defaultMode: 0440

extraVolumeMounts:
  - name: postgresql-credentials
    mountPath: /run/secrets/postgresql
    readOnly: true
  - name: mysql-credentials
    mountPath: /run/secrets/mysql
    readOnly: true

{
  "query": "SELECT id, service_name, status, latency_ms FROM app.orders WHERE status = 'failed' ORDER BY latency_ms DESC",
  "timeout_seconds": 10
}