DBSheriff for Amazon Redshift — prerequisites and accounts

DBSheriff connects with its own dedicated accounts — never your application's credentials — each limited to what its job needs. Setup has two parts: one server prerequisite (recommended, not required) and the accounts themselves. Three scripts automate all of it. Redshift is always a fully managed AWS service — there is no self-managed or onprem variant to choose.

The three scripts

On an installed host there is no scripts/ folder, so read every scripts/<name>.py below as sudo /opt/dbsheriff/current/deploy/onprem/stack.sh tool <name> ..., for example sudo /opt/dbsheriff/current/deploy/onprem/stack.sh tool setup_dbsheriff redshift mycluster.abc123.us-east-1.redshift.amazonaws.com. The scripts/ form is for a source checkout. The details are under "How to start the scripts" in Before you install: creating DBSheriff's database users.

All three are interactive (they ask for the database admin credentials; setup_dbsheriff.py and install_prereqs.py also ask for the cluster identifier), connect only as a database user — never a cloud credential — and never reboot the cluster. If a change needs a reboot to take effect, they tell you and stop there; you reboot on your own schedule and re-run to finish. Add --dry-run to any of them to preview without changing anything. To create fewer than all six: pass --roles=monitor,kill to setup_dbsheriff.py, or answer create_reset_dbuser.py's own account-selection prompt with the numbers/names you want (or all). The guided command onboard_target.py creates five of them by default (monitor, kill, lock, posture and classify) and redshift_plan only when you name it with --roles; the other three scripts above create all six by default.

Add --check to any of them for a read-only status report instead: what's already there, what's missing, and the exact command to fix each gap — nothing is ever written, not even if you say yes to something (there's no prompt to say yes to). Exits with a non-zero status if anything's missing, so it's usable in a script.

The 6 accounts

Creating the accounts

Run as the cluster's master user, connected to the database DBSheriff should monitor (account-level statements are catalog-wide in Redshift, so any database connection reaches them):

-- required
CREATE USER redshift_monitor PASSWORD '<password>';
ALTER USER redshift_monitor SYSLOG ACCESS UNRESTRICTED;
GRANT ROLE "sys:monitor" TO redshift_monitor;

-- optional
CREATE USER redshift_kill_user PASSWORD '<password>';
ALTER USER redshift_kill_user SYSLOG ACCESS UNRESTRICTED;
GRANT ROLE "sys:monitor" TO redshift_kill_user;
GRANT ROLE "sys:operator" TO redshift_kill_user;   -- required to act on another user's session

CREATE USER redshift_lock_user PASSWORD '<password>';
ALTER USER redshift_lock_user SYSLOG ACCESS UNRESTRICTED;
GRANT ROLE "sys:monitor" TO redshift_lock_user;
GRANT ROLE "sys:secadmin" TO redshift_lock_user;   -- required for CONNECTION LIMIT 0
GRANT ROLE "sys:operator" TO redshift_lock_user;

CREATE USER redshift_posture PASSWORD '<password>';
ALTER USER redshift_posture SYSLOG ACCESS UNRESTRICTED;
GRANT ROLE "sys:monitor" TO redshift_posture;

CREATE USER redshift_classify PASSWORD '<password>';

SYSLOG ACCESS UNRESTRICTED plus RBAC role sys:monitor is what lets each account see every other user's rows in SYS_QUERY_HISTORY/PG_LOCKS — without it, every one of these system views silently restricts to the calling user's own rows (no error, just an empty feed). sys:operator alone is not enough to terminate another user's session, and sys:secadmin is what ALTER USER ... CONNECTION LIMIT 0 (the account-lock mechanism) actually needs — redshift_kill_user/redshift_lock_user each carry exactly the roles their job requires, nothing more.

Then, per database to monitor (redshift_monitor needs this for its EXPLAIN-based full-table-read estimate — EXPLAIN needs the same privilege as running the statement; redshift_classify needs it for the schema scan and value sampling; kill/lock/posture need no schema grants at all):

-- connected to the database, repeat per schema holding a table to watch
GRANT USAGE ON SCHEMA <schema> TO redshift_monitor, redshift_classify;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO redshift_monitor, redshift_classify;

Password rules: 8–64 characters, at least one uppercase, one lowercase, one number; ASCII 33–126 only, excluding ' " \ / @ and space (the same restriction the cluster's own master password has).

Prerequisite (AWS-only)

This is the setting of a provisioned cluster. A Redshift Serverless workgroup has no cluster parameter group and no cluster to reboot: see "Redshift Serverless" below for its command.

scripts/setup_dbsheriff.py redshift <cluster-endpoint> --optional-prereqs=enable_user_activity_logging   # asks for the cluster identifier

enable_user_activity_logging (recommended, not required) — query text is retained in the older STL_*/SVL_* system log tables and exported if CloudWatch/S3 audit logging is also turned on. It has no effect on SYS_QUERY_HISTORY itself — that view's query text is populated either way, so monitoring works fully without this setting. It's not readable over the database connection at all (even the master user gets "must be superuser"), so the scripts read and stage it through your own authenticated aws CLI session instead — no cloud credential is asked for.

Staging it only updates the cluster's parameter group; like most Redshift parameters, it needs a reboot to actually take effect, which these scripts never do for you:

aws redshift modify-cluster-parameter-group --parameter-group-name <group> \
    --parameters ParameterName=enable_user_activity_logging,ParameterValue=true
aws redshift reboot-cluster --cluster-identifier <cluster>

Redshift Serverless

A serverless workgroup is monitored with the same accounts, the same SQL and the same registration as a provisioned cluster: the statements of "Creating the accounts" ran unchanged on a workgroup in our test of 2026-10-24, and so did the kill and the lock. What differs:

• The endpoint is <workgroup>.<account-id>.<region>.redshift-serverless.amazonaws.com, port 5439, and the database is one you created in the namespace. There is no cluster identifier. The guided command (onboard_target.py) does not list workgroups; enter the endpoint at its manual line, where the cluster identifier may be left blank. setup_dbsheriff.py and install_prereqs.py ask no cluster identifier for an endpoint that ends in .redshift-serverless.amazonaws.com: a workgroup has none, so the question is skipped and the command says so. Redshift's one prerequisite, enable_user_activity_logging, is then reported as one that cannot be checked there (it is not run, whatever --optional-prereqs says), and the command prints the workgroup's own command, the one below.
• No parameter group, no reboot. enable_user_activity_logging is a workgroup setting, changed without a reboot:

bash aws redshift-serverless update-workgroup --workgroup-name <workgroup> \ --config-parameters parameterKey=enable_user_activity_logging,parameterValue=true

As on a cluster it has no effect on SYS_QUERY_HISTORY, so monitoring works without it. onboard_target.py does not stage it for a workgroup: with no cluster identifier it reports the prerequisite as one that cannot be checked here, prints the command above and points to this section. - The STL_* and STV_* system tables are not readable on a workgroup, by any user, the administrator included (permission denied). DBSheriff reads statements from SYS_QUERY_HISTORY as it does on a cluster, and reads the application name and the login events from SYS_CONNECTION_LOG instead of STL_CONNECTION_LOG. What the features guide builds on STV_PARTITIONS (cluster-wide space totals), STV_SESSIONS (connection counts) and STL_ALERT_EVENT_LOG (optimizer alerts) is not available on a workgroup. - The AWS-API posture checks (encrypted, publicly accessible, requires SSL, audit logging; see "AWS IAM permissions for posture" below) are read from the provisioned-cluster API, which has no answer for a workgroup. On a workgroup, whether it is publicly accessible (PS65) is read from the workgroup itself and needs the IAM action redshift-serverless:GetWorkgroup. Audit logging (PS09), encryption at rest (PS11) and require_ssl (PS66) are always reported as not checked, with the reason beside each: check them in the AWS console or with aws redshift-serverless get-workgroup. - The CA bundle is the Amazon Redshift bundle, amazon-trust-ca-bundle.crt, not the RDS bundle: see "Connecting to the target securely" at the end of this guide. - Monitoring keeps the workgroup's compute running, and that is billed. The rest of this section is the arithmetic and your choices.

What it costs. Redshift Serverless bills compute in RPU-hours, per second, for the time queries run (AWS bills a 60-second minimum for a burst of query activity); an idle workgroup bills no compute. DBSheriff reads the statement view (SYS_QUERY_HISTORY) every poll (3 seconds by default) and its collector reads the connection and statement logs every 30 seconds, so a monitored workgroup is never idle. Measured on 2026-10-24 on a workgroup with a base capacity of 8 RPU: SYS_SERVERLESS_USAGE showed 480 charged RPU-seconds (8 RPU x 60 s) in each of nine consecutive minutes. At 0.375 USD per RPU-hour (the us-east-1 price at the time) that is

base capacity (RPU) x price per RPU-hour x 24 hours
8 x 0.375 x 24 = 72 USD a day   (3.00 USD an hour, about 2,160 USD in 30 days)

The cost is linear in the workgroup's base capacity and depends on your region's price per RPU-hour; put your own two numbers into the line above. It comes on top of what the workgroup costs for your own queries.

What you can do about it.

• Lower the base capacity of the workgroup if the monitored workload does not need it (AWS console or aws redshift-serverless update-workgroup --base-capacity): the cost falls in proportion.
• Pause monitoring of the server when you do not need it watched: on Monitored Servers choose Pause for the server (an approver does this). The producer lets go of the workgroup at the next registry reload (up to five minutes) and the workgroup idles again. While it is paused nothing watches the workgroup: no capture, no alerts, no kill.
• The poll interval is the Redshift field of the Admin Console's Poll Intervals panel (Config Parameters): 1 to 60 seconds, 3 by default, one value for every Redshift server, changed by an unrestricted approver and picked up within about five minutes without a restart. It sets how many statements DBSheriff sends, at least one SYS_QUERY_HISTORY read per poll: 3600 / the interval, so 1,200 an hour at 3 seconds and 60 an hour at 60 seconds. It is not a way to let the workgroup sleep: only the 3-second default was measured, and with a 60-second billing minimum and a collector read every 30 seconds, no value from 1 to 60 is expected to leave a monitored workgroup idle, so expect the arithmetic above at any setting. What a longer interval does cost is detection time: a statement is seen up to one interval after it starts, and one that finishes inside the interval is seen only as a finished statement, which is scored and alerted but can no longer be killed. The window that finished statements are read from is never less than two intervals (120 seconds by default), so at 60 seconds no statement falls between two polls.
• DBSheriff has no pause-friendly mode. A poll slow enough for the workgroup to sleep would see a statement only after it had finished, which is not real-time detection, so it is not offered.

AWS IAM permissions for posture (optional)

Whether the cluster is encrypted at rest, publicly accessible, requires SSL, and has audit logging enabled are not readable from SQL at all on Redshift — DBSheriff reads them from the AWS Redshift API instead, which needs an IAM credential, a process-level AWS permission rather than a database grant. Skipping this is fine — every SQL-based finding works without it; you'll just see those specific posture checks report "not checked" instead of a real pass/fail.

Least-privilege policy for a dedicated IAM user (or an EC2 instance role, if the collector runs on one):

{
  "Version": "2012-10-17",
  "Statement": [{
    "Effect": "Allow",
    "Action": [
      "redshift:DescribeClusters",
      "redshift:DescribeClusterParameters",
      "redshift:DescribeLoggingStatus"
    ],
    "Resource": "*"
  }]
}

For a Redshift Serverless workgroup add a separate statement; the calls above are for provisioned clusters and have no answer for a workgroup:

{
  "Effect": "Allow",
  "Action": ["redshift-serverless:GetWorkgroup"],
  "Resource": "*"
}

Without it PS65 (publicly accessible) is reported not checked, and the note names redshift-serverless:GetWorkgroup. On a workgroup PS09, PS11 and PS66 are always not checked whatever the policy says, because the cluster calls behind them need a cluster identifier, which a workgroup does not have. The workgroup name and the region are read from the endpoint name.

Set AWS_REGION, AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY (plus AWS_SESSION_TOKEN for temporary credentials) wherever DBSheriff's governance collector runs, or skip this entirely on a host whose instance role already has these three permissions.

Monitoring more than one database on the same cluster

A Redshift cluster commonly hosts several real databases. DBSheriff monitors each one as its own registration, the same model every engine uses. To add another database on a cluster you've already registered:

1. Grant the same accounts access on the new database — logins are cluster-wide, so you're reusing the same accounts, but schema/table grants are per-database: sql -- connected to the NEW database GRANT USAGE ON SCHEMA public TO redshift_classify; GRANT SELECT ON ALL TABLES IN SCHEMA public TO redshift_classify, redshift_monitor;
2. Register it as a new server in the Admin Console's Monitored Servers tab — same cluster host/port, a new server name, the new database's name, and the same account credentials.
3. That's it — every subsystem picks up the new registration on its next reload (up to 5 minutes) and monitors it independently, automatic kill/lock included.

Register the server

Admin Console → Monitored Servers → Add: engine Redshift, host, port 5439, database, and the credentials for whichever accounts you created (leave the rest blank). Turn on kill enabled / lock-on-burst enabled only if you created those accounts, then save the server.

Verify

-- as redshift_monitor, not the master user
SELECT username, status, session_id, query_text
  FROM sys_query_history
  WHERE username NOT LIKE 'rdsdb%'
  ORDER BY start_time DESC LIMIT 10;

SELECT user_name, role_name FROM svv_user_grants
  WHERE user_name IN ('redshift_monitor','redshift_kill_user','redshift_lock_user','redshift_posture');

DBSheriff connects with the monitor account when it starts monitoring the server (within the registry reload interval, five minutes), and a credential the target refuses shows as a connection error in the producer's log. The kill, lock and classification accounts are used when a kill, a lock or a classification scan runs. Live data appears within about five minutes of saving the server; the first posture/governance snapshot within the hour.

Questions, or something here doesn't match what you see: SECURITY.md in your release package, or support@dbsheriff.ai.

Connecting to the target securely

setup_dbsheriff.py, create_reset_dbuser.py, install_prereqs.py and onboard_target.py connect as your administrator, and the administrator password and every new account's password cross that connection, so they verify the server's certificate by default (--ssl-mode verify-full; --ca-file <bundle> names the CA; for Amazon Redshift, a provisioned cluster or a serverless workgroup, it is the bundle AWS publishes for Redshift, amazon-trust-ca-bundle.crt (https://s3.amazonaws.com/redshift-downloads/amazon-trust-ca-bundle.crt; in the China regions https://s3.cn-north-1.amazonaws.com.cn/redshift-downloads-cn/amazon-trust-ca-bundle.crt). Do not use the RDS bundle (rds-global-bundle.pem): it is for RDS and Aurora endpoints, and it failed verify-full against a Redshift Serverless endpoint in our test of 2026-10-24. The same file is what REDSHIFT_SSL_CA names for the running product; that variable is read only by Redshift servers, so pointing it at this bundle does not change how your RDS servers are checked). A refused certificate stops the run and the message names the flag; --ssl-mode require encrypts without verifying (an explicit opt-down that prints a warning) and --ssl-mode disable --allow-plaintext sends everything in clear text (a trusted private link only). A generated password is never printed: it is stored in the registry (encrypted), or written to a new mode-600 file (--credentials-file) whose path is shown. Account names must match [A-Za-z][A-Za-z0-9_]*, and every database, schema or warehouse name the scripts discover is quoted as an identifier before it goes into a statement.

The redshift_plan account (the login an EXPLAIN of a captured statement can run under) holds USAGE and SELECT on the schemas and nothing else: no SYSLOG ACCESS, no RBAC role, no write or DDL right. setup_dbsheriff.py creates it for a new cluster and records it in the registry (plan_db_user); nothing uses it until the EXPLAIN path is switched to it.