DBSheriff for PostgreSQL — prerequisites and roles

DBSheriff connects with its own dedicated roles — never your application's credentials — each limited to what its job needs (the last section says exactly what each one can do if its password leaks). Setup has two parts: server prerequisites (an extension or two, some platform-specific configuration) and the roles themselves. Three scripts automate all of it. PostgreSQL 14 or later is required for completed-statement monitoring: on 12 and 13 DBSheriff still sees statements while they are in pg_stat_activity, but the pg_stat_statements capture needs 14.

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 postgresql db1.acme.internal. 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 which platform, which roles, and for database credentials), connect only as a database user — never a cloud credential — and never restart the database server. If a change needs a restart to take effect, they tell you so, point you to the restart command in your platform section below, and stop there; you restart 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).

Run them against a server DBSheriff already monitors with kill on only after pausing it. The administrator login these scripts use is not a DBSheriff account, and its statements (CREATE ROLE, ALTER ROLE ... PASSWORD, SHOW shared_preload_libraries) look like an attack to the kill: on a monitored server with kill on, a reset has had its own administrator session terminated half way ("terminating connection due to administrator command" in the server log), with some roles reset and others not. DBSheriff does not leave that login out of the kill by name. onboard_target.py asks the Admin Console and stops before it touches the server when the server's name is registered with kill on and not paused; the other two scripts do not ask the console, so pause it yourself: Monitored Servers, the Pause button of the server (it switches monitoring_enabled off), wait for the next registry reload (REGISTRY_RELOAD_INTERVAL_S, five minutes by default), run the script, then press Resume. Switching kill off in the server's row does the same.

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 roles

Connecting to the target

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 role's password cross that connection. They therefore verify the server's certificate by default (--ssl-mode verify-full; libpq's default prefer can be downgraded by anyone on the path). Give the CA that signed it with --ca-file <bundle> (Amazon RDS / Aurora: the RDS CA bundle for your region; a private or self-signed server: its CA or its own certificate); without --ca-file the system trust store is used (libpq 16 or newer). 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); --ssl-mode disable --allow-plaintext sends everything in clear text and is for a trusted private link only.

A generated password is never printed: it is stored in the registry (encrypted) when the server is registered, and otherwise written to a new file with mode 600 (--credentials-file, default ./dbsheriff-credentials-<server>-<time>.txt) whose path is shown; move it into your password manager and delete the file. Role names must match [A-Za-z][A-Za-z0-9_]*. Every database and schema name the scripts discover is quoted as an identifier before it goes into a statement (a schema named x" TO PUBLIC; ALTER ROLE … used to run as your administrator); a name with a control character is skipped with a note.

Creating the roles (identical on every platform)

Run as a superuser (self-managed) or the master/admin user (managed platforms), substituting a real password:

-- required
CREATE ROLE dbsheriff_monitor LOGIN PASSWORD '<password>';
GRANT pg_monitor, pg_read_all_stats TO dbsheriff_monitor;

-- optional
CREATE ROLE dbsheriff_kill LOGIN PASSWORD '<password>';
GRANT pg_monitor, pg_signal_backend TO dbsheriff_kill;   -- pg_monitor is required here too, for the pre-kill check

CREATE ROLE dbsheriff_lock LOGIN PASSWORD '<password>' CREATEROLE;
GRANT pg_signal_backend TO dbsheriff_lock;
-- PostgreSQL 16+ also needs ADMIN OPTION on every role dbsheriff_lock should be able to lock —
-- the scripts above do this automatically (and re-run it for you every time); by hand:
-- GRANT <role> TO dbsheriff_lock WITH ADMIN OPTION;

CREATE ROLE dbsheriff_posture LOGIN PASSWORD '<password>';
GRANT pg_monitor, pg_read_all_stats TO dbsheriff_posture;

CREATE ROLE dbsheriff_classify LOGIN PASSWORD '<password>';

CREATE ROLE dbsheriff_plan LOGIN PASSWORD '<password>';   -- the EXPLAIN login: no role memberships, no attributes

Then, per database to monitor:

\c <database>
GRANT CONNECT ON DATABASE <database>
  TO dbsheriff_monitor, dbsheriff_kill, dbsheriff_lock, dbsheriff_posture, dbsheriff_classify, dbsheriff_plan;  -- only the roles you created

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- keep only the roles you created in each role list of the DO statement below
DO $$ DECLARE s text; BEGIN
  FOR s IN SELECT nspname FROM pg_namespace
           WHERE nspname NOT IN ('pg_catalog','information_schema') AND nspname NOT LIKE 'pg\_%' ESCAPE '\'
  LOOP
    EXECUTE format('GRANT USAGE ON SCHEMA %I TO dbsheriff_monitor, dbsheriff_posture, dbsheriff_classify, dbsheriff_plan', s);
    EXECUTE format('GRANT REFERENCES ON ALL TABLES IN SCHEMA %I TO dbsheriff_monitor, dbsheriff_posture', s);  -- metadata only, never rows
    EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT REFERENCES ON TABLES TO dbsheriff_monitor, dbsheriff_posture', s);
    EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO dbsheriff_classify, dbsheriff_plan', s);
    EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON TABLES TO dbsheriff_classify, dbsheriff_plan', s);
  END LOOP;
END $$;

GRANT CREATE ON DATABASE <database> TO dbsheriff_lock;  -- optional by hand (the setup scripts always grant it): write back approved classifications

If you're enabling pgaudit (optional statement-level audit trail — see your platform section below for how it's enabled there), also run this once per database, substituting <audit_role> with the name your platform uses (this guide uses dbsheriff_audit; AWS requires rds_pgaudit):

CREATE ROLE <audit_role> NOLOGIN;  -- skip on AWS: rds_pgaudit already exists

CREATE SCHEMA IF NOT EXISTS dbsheriff;
CREATE OR REPLACE FUNCTION dbsheriff.audit_grant(p_schema text, p_table text, p_grant boolean)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog AS $$
BEGIN
  IF p_grant THEN
    EXECUTE format('GRANT SELECT, INSERT, UPDATE, DELETE ON %I.%I TO <audit_role>', p_schema, p_table);
  ELSE
    EXECUTE format('REVOKE SELECT, INSERT, UPDATE, DELETE ON %I.%I FROM <audit_role>', p_schema, p_table);
  END IF;
END $$;
REVOKE ALL ON FUNCTION dbsheriff.audit_grant(text, text, boolean) FROM PUBLIC;
GRANT USAGE ON SCHEMA dbsheriff TO dbsheriff_lock;
GRANT EXECUTE ON FUNCTION dbsheriff.audit_grant(text, text, boolean) TO dbsheriff_lock;

This grants/revokes a classified table's audit visibility automatically as you approve or reject classifications. Nothing to register anywhere else — DBSheriff reads whichever role pgaudit.role is actually set to directly from the server, so any name works without telling DBSheriff about it separately. The server does need pgaudit.role set to that role, on every platform (your platform section below shows how; once pgaudit is loaded and you asked for pgaudit (--optional-prereqs=pgaudit on setup_dbsheriff.py, or yes to the pgaudit item in install_prereqs.py), the setup scripts set it for you on self-managed PostgreSQL, on RDS / Aurora and on Azure while it is empty and never replace a role you already named, and their --check reports an empty one as a gap; on Cloud SQL it is a database flag you set yourself): while it is empty, pgaudit logs nothing for those grants and the posture check PS82 reports it. If your pgaudit.log also includes the read or write classes, set pgaudit.log_relation = on as well, so each line names its table.

Prerequisites, by platform

DBSheriff needs pg_stat_statements preloaded (required — without it you lose fast-query and burst detection) and, optionally, pgaudit preloaded too. How you enable these is different on every platform — jump to yours.

pg_stat_statements also has to be recording: its setting pg_stat_statements.track must be top (the stock value) or all. With none the extension is installed, CREATE EXTENSION succeeds and the view stays empty, so every statement that finishes between two polls (a sub-second read, DDL, CREATE ROLE, COPY) is never seen. Azure Database for PostgreSQL ships it as none, and a parameter group, a flag or postgresql.conf can set it on any platform. The scripts therefore read the value the server runs with: --check and the prerequisite step of setup_dbsheriff.py / onboard_target.py call a none a gap, name pg_stat_statements.track and print the fix, and a run applies it where it applies the preload (ALTER SYSTEM on a self-managed server, the parameter group on RDS and Aurora, az postgres flexible-server parameter set on Azure; on Google Cloud they print the flag to set, like every flag). It is a dynamic parameter: no restart. On a running server the posture check PS84 reports it too (a monitoring-readiness warning), so a server changed later is found at the next scan.

Onprem / self-managed

scripts/setup_dbsheriff.py postgresql <hostname>   # choose "onprem" when asked

postgresql.conf location (or ask the server: SHOW config_file;):

SHOW shared_preload_libraries;   -- ALTER SYSTEM replaces the whole value: put every library already listed first, e.g. 'timescaledb,pg_stat_statements'
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';   -- add ',pgaudit' too if you want the audit trail

Restart to apply (this is a postmaster-context setting — a reload isn't enough):

sudo systemctl restart postgresql@16-main      # Debian/Ubuntu — check the real name with pg_lsclusters
sudo systemctl restart postgresql-16           # RHEL/Rocky/CentOS
brew services restart postgresql@16            # Homebrew
# Windows, elevated — confirm the real service name first: sc query state= all | findstr /i postgres
net stop postgresql-x64-16 && net start postgresql-x64-16

Then, per database: CREATE EXTENSION IF NOT EXISTS pg_stat_statements; (and pgaudit too, if enabled). Check that it records (SHOW pg_stat_statements.track; says top or all); if it says none, no restart is needed:

ALTER SYSTEM SET pg_stat_statements.track = 'top';
SELECT pg_reload_conf();

With pgaudit, also name the object-level audit role from "Creating the roles" (a reload is enough; once pgaudit is loaded and pgaudit.role is empty, setup_dbsheriff.py --optional-prereqs=pgaudit and the pgaudit item of install_prereqs.py run these two statements for you):

ALTER SYSTEM SET pgaudit.role = 'dbsheriff_audit';
SELECT pg_reload_conf();

Recommended, not required (reload with SELECT pg_reload_conf(); unless noted):

track_activity_query_size = 8192      # restart; whole statement text, not truncated
logging_collector = on                # restart
compute_query_id = auto               # default on PG14+
log_connections = on
log_disconnections = on
log_line_prefix = '%m [%p] %u@%d %r %a '   # who, from where, which application: see below
password_encryption = scram-sha-256   # takes effect immediately, no reload needed
pgaudit.log = 'ddl, role, misc'       # if using pgaudit; `misc` covers the SET statements (SET ROLE) and works on every platform: RDS and Aurora reject `misc_set`
pgaudit.log_catalog = off
pgaudit.log_parameter = off
pgaudit.log_relation = on             # one line per table; matters only if pgaudit.log has read or write

DBSheriff takes the login, database, client address and application name of a pgaudit line, and the client address of a failed password login, from the text log's log_line_prefix; a prefix without them leaves those empty. With pgaudit on PostgreSQL 14 and later, end it with qid=%Q ('%m [%p] %u@%d %r %a qid=%Q ') so an audit line can be matched to the statement it belongs to. A server that writes csvlog (log_destination = 'csvlog' or 'stderr,csvlog') or, on PostgreSQL 15 and later, jsonlog is read field by field and does not depend on the prefix.

Reading the server log through SQL (optional). The logins, failed logins and pgaudit lines DBSheriff records come from the server's own log. The governance collector reads that file with the posture login (dbsheriff_posture; dbsheriff_monitor when no posture login exists), over its normal connection, with no log directory mounted anywhere. The file functions are superuser-only by default, so nothing reads the log this way until you grant them (DBSheriff then uses a mounted file or the session list, and the posture check PS29 reports the pg_hba.conf rules as not checked). It needs logging_collector = on and the log_* settings above. As a superuser, substituting the login that reads the log (dbsheriff_posture if you created it, otherwise dbsheriff_monitor) for <posture-or-monitor-login>:

GRANT pg_read_server_files TO <posture-or-monitor-login>;
GRANT EXECUTE ON FUNCTION pg_current_logfile(), pg_current_logfile(text),
                          pg_stat_file(text),
                          pg_read_binary_file(text, bigint, bigint, boolean),
                          pg_hba_file_rules() TO <posture-or-monitor-login>;
GRANT SELECT ON pg_hba_file_rules TO <posture-or-monitor-login>;   -- the view

pg_current_logfile names the file being written (the plain-text log, or the csvlog / jsonlog), pg_stat_file gives its size, pg_read_binary_file reads it in pieces as bytes, and pg_hba_file_rules is what the posture check PS29 and the access-change snapshot read. pg_read_server_files is what lets those functions open a log file that lies outside the data directory. The posture scan reads the rules with the same login, so these grants serve it too. This widens that login: see the last section for what it can then read. RDS and Aurora have no file access to grant: use log_fdw (AWS section below).

AWS (RDS / Aurora PostgreSQL)

scripts/setup_dbsheriff.py postgresql <hostname>   # choose "aws"; asks for the RDS instance/cluster id

Stages the change through your own authenticated aws CLI session — no cloud credential is asked for. From a source checkout, with the aws CLI installed and signed in, the script stages the change itself. Through stack.sh tool on an installed host the container has no cloud CLI, so the script says it could not stage the change and sends you here: run the commands under "By hand instead of the script" below yourself. It refuses only if the instance is still on AWS's uneditable default parameter group; create and attach a custom one first, then re-run:

aws rds create-db-parameter-group --db-parameter-group-name dbsheriff-postgres16 \
    --db-parameter-group-family postgres16 --description "DBSheriff settings"
aws rds modify-db-instance --db-instance-identifier <instance> \
    --db-parameter-group-name dbsheriff-postgres16 --apply-immediately
# Aurora: create-db-cluster-parameter-group / modify-db-cluster instead, family aurora-postgresql16 —
# shared_preload_libraries is cluster-scoped, the instance-level group has no effect on it.

By hand instead of the script:

aws rds modify-db-parameter-group --db-parameter-group-name dbsheriff-postgres16 \
    --parameters "ParameterName=shared_preload_libraries,ParameterValue=pg_stat_statements,ApplyMethod=pending-reboot"
aws rds reboot-db-instance --db-instance-identifier <instance>
# Aurora has no cluster-wide reboot — reboot-db-instance on EACH member, readers first, writer last

Then, per database: CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

pg_stat_statements.track is top unless a parameter group sets it otherwise; if SHOW pg_stat_statements.track; says none (posture check PS84), the script sets it in the attached group, ApplyMethod=immediate, no reboot (on Aurora in the cluster parameter group):

aws rds modify-db-parameter-group --db-parameter-group-name dbsheriff-postgres16 \
    --parameters "ParameterName=pg_stat_statements.track,ParameterValue=top,ApplyMethod=immediate"

Recommended, not required — the same parameter group and commands, with a different name, value and ApplyMethod; they are the settings behind the posture checks PS31 (track_activity_query_size), PS09 (log_connections, log_disconnections) and PS32 (password_encryption). Check a parameter's type before you pick one (on Aurora use describe-db-cluster-parameters --db-cluster-parameter-group-name; a parameter the cluster group does not list is in the instance-level group):

aws rds describe-db-parameters --db-parameter-group-name dbsheriff-postgres16 \
    --query "Parameters[?ParameterName=='<name>'].[ParameterName,ApplyType]" --output table

pgaudit: the object-level role must be named exactly rds_pgaudit (it already exists — don't create it) and pgaudit must be preloaded the same way as above:

aws rds modify-db-parameter-group --db-parameter-group-name dbsheriff-postgres16 --parameters \
  "ParameterName=shared_preload_libraries,ParameterValue=pg_stat_statements\,pgaudit,ApplyMethod=pending-reboot" \
  "ParameterName=pgaudit.log,ParameterValue=ddl\,role\,misc,ApplyMethod=immediate" \
  "ParameterName=pgaudit.role,ParameterValue=rds_pgaudit,ApplyMethod=immediate"

RDS and Aurora accept these classes in pgaudit.log: ddl, function, misc, read, role, write, none and all, each with a leading - to take it off. Write misc, which includes the SET statements: misc_set, a narrower class that a pgaudit you manage yourself may take, is refused with Invalid parameter value: misc_set for: pgaudit.log.

Once pgaudit is loaded (after the reboot), the setup script, when you ask for pgaudit (--optional-prereqs=pgaudit), sets pgaudit.role in the attached parameter group the same way when it is empty; RDS applies it without another reboot.

Reading the server log (logins, failed logins — RDS/Aurora have no filesystem access) needs log_fdw instead. Per database, as the master user:

CREATE EXTENSION IF NOT EXISTS log_fdw;
CREATE SERVER IF NOT EXISTS log_fdw_server FOREIGN DATA WRAPPER log_fdw;
GRANT USAGE ON FOREIGN SERVER log_fdw_server TO <posture-or-monitor-login>;
GRANT EXECUTE ON FUNCTION list_postgres_log_files() TO <posture-or-monitor-login>;
CREATE SCHEMA IF NOT EXISTS dbsheriff_log AUTHORIZATION <posture-or-monitor-login>;

Here too the governance collector reads the log with the posture login, so substitute dbsheriff_posture if you created it, otherwise dbsheriff_monitor, for <posture-or-monitor-login>. Leave the log source (pg_log_source, default auto) as it is: auto tries the SQL read, then log_fdw, then a mounted file. There is no console field for it.

RDS's default log_line_prefix (%t:%r:%u@%d:[%p]:) is read as it is, so logins, failed logins and pgaudit lines need no change; it has no application name or query id, so to get them set log_line_prefix in the same parameter group to %m [%p] %u@%d %r %a (end it with qid=%Q for pgaudit on PostgreSQL 14 and later).

Aurora Serverless v2 with scale to zero: monitoring keeps the cluster awake, and that costs. A cluster created with a minimum capacity of 0 ACU pauses after seconds_until_auto_pause (300 s by default) without a connection. DBSheriff keeps its sessions to a monitored server open and polls them every few seconds (Admin Console, Poll Intervals), so a cluster that is monitored never pauses: it stays at its minimum capacity, 0.5 ACU, all day. That is 12 ACU-hours a day for each monitored cluster, plus whatever the load adds (about 1 to 2 ACU for four minutes after a resume). Multiply by your region's ACU-hour price: at 0.12 USD, which is the us-east-1 price for Aurora Standard at the time of writing (check the current price list), 0.5 ACU is 0.06 USD an hour, about 1.44 USD a day or 43 USD a month for each cluster. Lengthening the poll interval does not change this, because the sessions stay open; real-time detection is the product, so there is no pause-friendly mode. Where the cost matters more than watching that cluster, pause monitoring for it (Monitored Servers, the Pause button, which sets monitoring_enabled off): the sessions are released at the next registry reload and the cluster pauses about 6 to 9 minutes later (measured).

A paused cluster takes 12 to 16 seconds to resume on the first connection. When monitoring is switched on against a paused cluster the producer's first connection attempt times out while the cluster wakes; it tries again after 5 seconds and then backs off (10 s, 20 s, then 30 s and 60 s), so the server is watched within about 20 seconds instead of 45. A statement that starts while the producer is not yet connected is seen, and killed if it is risky, on the first poll after it connects.

GCP (Cloud SQL for PostgreSQL)

scripts/create_reset_dbuser.py postgresql <hostname>   # choose "gcp" — roles only; prerequisites below are manual

Prerequisites are not automated here, on purpose — gcloud's own apply step restarts the instance immediately with no separate stage-then-restart option, so run these yourself when you're ready for that restart. (AlloyDB for PostgreSQL is a different service with its own flags: it has its own section below.)

pg_stat_statements needs no flag at all — Cloud SQL preloads it by default:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;   -- per database

If SHOW pg_stat_statements.track; says none (posture check PS84; the stock value is top), set the database flag pg_stat_statements.track=top on the instance with the other flags (read the current list first: --database-flags replaces it, and applying a flag may restart the instance, which is why the scripts only print it).

For pgaudit and anything else needing a flag — --database-flags replaces the whole flag list, so read the current one first and include everything you want to keep:

gcloud sql instances describe <instance> --format="value(settings.databaseFlags)"
gcloud sql instances patch <instance> --database-flags=cloudsql.enable_pgaudit=on,pgaudit.log=all,pgaudit.role=dbsheriff_audit,<...the rest you read above...>

gcloud warns and prompts before a restart-requiring flag change (-q to auto-confirm). Then:

CREATE EXTENSION IF NOT EXISTS pgaudit;

The setup scripts never run gcloud (applying a flag may restart the instance), so pgaudit.role stays this manual step; their --check reports it while it is empty and prints the flag to add.

Recommended, not required — more flags in the same --database-flags list (keep everything you read above). They are the settings behind the posture checks PS31 (track_activity_query_size, which needs a restart, so expect the prompt), PS09 (log_connections, log_disconnections) and PS32 (password_encryption):

gcloud sql instances patch <instance> --database-flags=track_activity_query_size=8192,log_connections=on,log_disconnections=on,password_encryption=scram-sha-256,<...the rest you read above...>

Google Cloud AlloyDB for PostgreSQL

scripts/setup_dbsheriff.py postgresql <address>   # choose "gcp"

AlloyDB is a platform of the PostgreSQL engine: you register it as PostgreSQL and answer gcp when the scripts ask for the platform. It speaks the PostgreSQL protocol and has the PostgreSQL catalogs, so the six roles, their grants and the monitoring are the ones in "Creating the roles" above; this section is what differs. It was written from one live compatibility probe on AlloyDB for PostgreSQL 18.4 (2026-10-24: a 2 vCPU primary with a public IP, ENCRYPTED_ONLY). Anything the probe did not run is marked not exercised.

What to create, in your Google Cloud project:

1. A cluster and its primary instance. The roles are created on the primary only (a read pool is read-only, and a secondary cluster has no writer). The cluster's first user is postgres: it is a member of alloydbsuperuser and not a superuser, and it is the administrator you give the scripts. With gcloud (the private services access of the VPC must exist first, as in Google's AlloyDB quickstart; flag names checked against gcloud alloydb ... --help):

bash gcloud alloydb clusters create <cluster> --region=<region> --network=<vpc> --password=<password of postgres> gcloud alloydb instances create <instance> --cluster=<cluster> --region=<region> --instance-type=PRIMARY --cpu-count=2 \ --assign-inbound-public-ip=ASSIGN_IPV4 --authorized-external-networks=<address DBSheriff connects from>/32 \ --database-flags='^:^password.enforce_complexity=on:log_connections=on:log_disconnections=on'

2. The way in. The probe used a public IP with the address of the machine that runs the scripts, and of the DBSheriff host, as an authorized network (CIDR form). AlloyDB requires password.enforce_complexity=on whenever the instance has a public IP (the probe's first create was refused without it); the 24-character passwords the scripts generate (upper and lower case letters and digits) passed it, and a password you type for a DBSheriff role yourself has to as well. Not exercised: an instance with a private IP only, reached from inside the VPC, and the AlloyDB Auth Proxy. DBSheriff needs only an address and port that answer the PostgreSQL protocol, from the machine that runs the scripts and from the DBSheriff containers.

3. TLS. An instance takes --ssl-mode ENCRYPTED_ONLY (the default) or ALLOW_UNENCRYPTED_AND_ENCRYPTED; for both, gcloud says the CA is not verified by the server. On the probe the instance presented a self-signed certificate (subject CN=unused) that does not contain its address, so --ssl-mode verify-full, the scripts' default, cannot succeed. The probe ran the scripts with --ssl-mode require (encrypted, certificate not checked: the scripts print their warning) and registered the server with ssl_mode require. Not exercised: verify-ca with that certificate as --ca-file (openssl s_client -starttls postgres -connect <address>:5432 -showcerts prints it).

Prerequisites. pg_stat_statements is preloaded on every instance (version 1.12 on the probe), so only the per-database CREATE EXTENSION IF NOT EXISTS pg_stat_statements; is left, and the scripts run it. The settings that a self-managed server takes with ALTER SYSTEM are instance flags here: on the probe ALTER SYSTEM SET of pgaudit.role, log_connections and track_activity_query_size all answered permission denied to set parameter (ALTER DATABASE <db> SET pgaudit.role = ... worked as an alternative to the first). --database-flags replaces the whole flag list, so read the flags that are set first and keep them. The list is comma-separated, and pgaudit.log holds commas, so give it in gcloud's alternate-delimiter form (^:^ makes : the separator, see gcloud topic escaping):

gcloud alloydb instances describe <instance> --cluster=<cluster> --region=<region> --format="yaml(databaseFlags)"
gcloud alloydb instances update <instance> --cluster=<cluster> --region=<region> --database-flags='^:^alloydb.enable_pgaudit=on:pgaudit.log=ddl,role,write:pgaudit.role=dbsheriff_audit:log_connections=on:log_disconnections=on:<...the flags you read above...>'

The setup scripts never run gcloud for Google Cloud, for the reason in the GCP section (a flag update may restart the instance) and because the update replaces the list: for the pgaudit item they print this command, and --check shows it as a comment line. The flags, and whether changing one restarts the instance according to Google's database-flag reference (checked 2026-10-24; read it again before you update):

pg_stat_statements.track is top on AlloyDB unless a flag says otherwise; if SHOW pg_stat_statements.track; says none (posture check PS84), add the flag pg_stat_statements.track=top the same way. The probe did not set it, so whether AlloyDB accepts it as a flag, and whether it restarts the instance, is for Google's database-flag reference to say; read it before you update.

The roles on AlloyDB. The same statements as everywhere, with these differences, all seen on the probe:

• AlloyDB has two internal databases, alloydbadmin and alloydbmetadata, that refuse every ordinary login in pg_hba.conf. The scripts leave them out. A database that refuses the administrator's login in any other way (pg_hba.conf or no CONNECT) is skipped with a note that says so, and the run goes on; a failure that does stop a run now says which role exists, how far its grants got and, when the server gave one, the SQLSTATE of the refusal (for example OperationalError, SQLSTATE 28000), so a retry in reset mode finishes it. Before this, a run stopped at the first of those databases and left half-created roles behind.
• The grants also cover AlloyDB's own schemas (google_ml, ai, pgsnap, perfsnap, helpobj) in the databases they exist in.
• The lock account is not granted ADMIN OPTION on AlloyDB's own roles. dbsheriff_lock has CREATEROLE and, on PostgreSQL 16 and later, ADMIN OPTION on the application roles it may lock. The scripts leave out AlloyDB's own roles (alloydbsuperuser, alloydbreplica, alloydbexport, alloydbmetadata and the other AlloyDB roles listed by exact name, and every alloydbiam* role) and print a note when they find some: ADMIN OPTION on alloydbsuperuser would let the account make any role a platform superuser. Locking or killing a member of alloydbsuperuser still worked on the probe, because it needs ADMIN OPTION on that member, not on alloydbsuperuser. On PostgreSQL 15 and earlier CREATEROLE alone reaches those roles, with or without the grant. The design of the lock account is otherwise unchanged ("What each role can do if its password leaks").
• The optional server-log grants of the self-managed section cannot be made: AlloyDB refuses GRANT pg_read_server_files and the grants on pg_read_file, pg_stat_file and pg_hba_file_rules(), so DBSheriff reads no server log and no pg_hba rules on AlloyDB, and logins are derived from the session list. The posture checks that need those files report them as not checked.

What the probe ran, and what it did not. On AlloyDB: the six roles were created and registered; statements were captured and scored; a real kill ended a statement (also one of a member of alloydbsuperuser); the lock ladder locked and unlocked an account (also a member of alloydbsuperuser); the classification scan found its candidates; the posture scan ran (PS09 fires until log_connections and log_disconnections are set by flag). Not exercised: the compliance report on an AlloyDB server, the pgaudit capture path (the server log cannot be read through SQL here), an instance with a private IP only, the Auth Proxy, and --ssl-mode verify-ca. On the probed build AlloyDB's own sessions (alloydbadmin, alloydbagent, alloydbmetadata, alloydbreplica) were scored and alerted like application accounts (59 high-risk and 8,216 low-risk rows in 40 minutes, the same as cloudsqladmin on Cloud SQL), and DBSheriff tried to kill one of them. This release leaves the login accounts of a platform it finds from the server's own catalog out of scoring and refuses a kill of them; the producer log says once a minute how many rows it left out. That change is written from its code and tests and was not re-run on an AlloyDB server.

Azure (Database for PostgreSQL Flexible Server)

scripts/setup_dbsheriff.py postgresql <hostname>   # choose "azure"; asks for resource group + server name

Stages the change through your own authenticated az CLI session. From a source checkout, with the az CLI installed and signed in, the script stages the change itself. Through stack.sh tool on an installed host the container has no cloud CLI, so the script says it could not stage the change and sends you here: run the az commands below yourself. pg_stat_statements ships preloaded by default — check first, since setting shared_preload_libraries replaces the whole value:

az postgres flexible-server parameter show --resource-group <rg> --server-name <server> --name shared_preload_libraries

If it's missing, append it (comma-separated with whatever's already there) and restart:

az postgres flexible-server parameter set --resource-group <rg> --server-name <server> \
  --name shared_preload_libraries --value "<existing-value>,PG_STAT_STATEMENTS"
az postgres flexible-server restart --resource-group <rg> --name <server>

Then, per database: CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Azure ships pg_stat_statements.track as none (parameter show reports the default none), so a server that has the extension and the preload still records nothing: SELECT count(*) FROM pg_stat_statements; stays at 0 while clients run statements, and DBSheriff sees none of the statements that finish between two polls. Set it to top; it is a dynamic parameter, so no restart:

az postgres flexible-server parameter show --resource-group <rg> --server-name <server> --name pg_stat_statements.track
az postgres flexible-server parameter set --resource-group <rg> --server-name <server> --name pg_stat_statements.track --value top

The setup scripts check it and run the second command for you (--check prints it, and posture check PS84 reports a server that still has none). Run them again after the restart that loads the preload: the parameter is set in the same pass that creates the extension.

Recommended, not required — the same parameter set command for each; they are the settings behind the posture checks PS31 (track_activity_query_size, a static parameter that needs the restart), PS09 (log_connections, log_disconnections) and PS32 (password_encryption), and the other three apply without one:

az postgres flexible-server parameter set --resource-group <rg> --server-name <server> --name track_activity_query_size --value 8192
az postgres flexible-server parameter set --resource-group <rg> --server-name <server> --name log_connections --value on
az postgres flexible-server parameter set --resource-group <rg> --server-name <server> --name log_disconnections --value on
az postgres flexible-server parameter set --resource-group <rg> --server-name <server> --name password_encryption --value scram-sha-256
az postgres flexible-server restart --resource-group <rg> --name <server>   # only for track_activity_query_size

az postgres flexible-server parameter show reports isConfigPendingRestart for a parameter that is still waiting for a restart.

pgaudit needs azure.extensions allow-listed first, then the same preload step:

az postgres flexible-server parameter set --resource-group <rg> --server-name <server> --name azure.extensions --value PGAUDIT
az postgres flexible-server parameter set --resource-group <rg> --server-name <server> \
  --name shared_preload_libraries --value "<existing-value>,PGAUDIT"
az postgres flexible-server restart --resource-group <rg> --name <server>
az postgres flexible-server parameter set --resource-group <rg> --server-name <server> --name pgaudit.log --value "DDL,ROLE,WRITE"
az postgres flexible-server parameter set --resource-group <rg> --server-name <server> --name pgaudit.role --value dbsheriff_audit

Once pgaudit is loaded (after the restart), the setup script, when you ask for pgaudit (--optional-prereqs=pgaudit), runs the last command for you when pgaudit.role is empty; it needs no further restart unless parameter show reports isConfigPendingRestart for it.

Then: CREATE EXTENSION IF NOT EXISTS pgaudit;. Azure doesn't support pgaudit's -mod-style exclusion shorthand — list statement classes individually. A major-version upgrade drops and recreates the extension — reapply pgaudit.log afterward.

Register the server

Admin Console → Monitored Servers → Add: engine postgresql, host, port, database, the credentials for whichever roles you created (leave the rest blank), and ssl_mode. A new server defaults to verify-full, which needs POSTGRES_SSL_CA (the CA bundle: see the installation guide (inside the package), section 4.8, "TLS to the databases you monitor"); choose require only for a server whose certificate you do not verify. Turn on kill enabled / lock-on-burst enabled only if you created those roles, then save the server.

Verify

-- as dbsheriff_monitor
SELECT count(*) FROM pg_stat_activity;
SELECT count(*) FROM pg_stat_statements;
SHOW pg_stat_statements.track;      -- top or all; none records nothing (posture check PS84)

Saving the server does not test any credential. Within the registry reload interval (five minutes by default) the producer starts monitoring it and connects with the monitor credential; if the target refuses it, the producer's log shows an "initial connection failed" error. The kill, lock, posture and classification credentials are first used when their work runs (a kill or lock when one is carried out, the governance collector's next pass, a classification scan), so a wrong one shows up then. Live data appears within about five minutes of saving; the first posture/governance snapshot within the hour.

Optional: the Control account (native controls)

If you will let DBSheriff create standing native controls in this PostgreSQL server (masks, limits, time-boxed grants) through the database's own features, or write the SQL for your DBA to run, read native controls privileges (inside the package) first: it says what each tier can do, what the optional Control account needs here, and how to remove everything DBSheriff made. The account is created only on request, with create_reset_dbuser.py postgresql <host> --with-control; setup_dbsheriff.py never creates it, and native controls are off until an approver turns a server to script or managed.

What each role can do if its password leaks

DBSheriff keeps these credentials encrypted in its registry (SERVER_CREDENTIALS_KEY); whoever can read both the registry and that key holds every role above. Create only the roles you will use (--roles).