Record where PII is in every table of a database

Scans every table of a database through a DBI connection and returns an inventory (see pii_inventory()). Each table is sampled by the database where it can - Postgres and IBM Db2 with TABLESAMPLE SYSTEM, DuckDB with USING SAMPLE - and only the sample is read; other databases are read from their first rows, and the inventory says so (sample_method "first-rows"). Only text and number columns are read. The rules are shared with the Python package (credgate.database; see the spec's "Databases").

Usage

inventory_database(
  con,
  source,
  path = NULL,
  schemas = NULL,
  include = NULL,
  exclude = NULL,
  views = FALSE,
  sample = 10000L,
  min_rate = 0,
  detectors = NULL,
  max_age = 30,
  on_error = c("warn", "stop")
)

Arguments

con

A DBI connection: Postgres (RPostgres), DuckDB, SQLite (RSQLite), MySQL/MariaDB, IBM Db2 (odbc), or another DBI backend.

source

Name of the data source for the inventory, e.g. "postgres:crm".

path

Optional inventory file (CSV); see Details.

schemas

Schemas to scan; NULL scans all except the database's own.

include, exclude

Shell-style patterns for schema.table to scan or leave out, e.g. "crm.*".

views

Also scan views (reading a view runs its query).

sample

Maximum number of rows read per table.

min_rate, detectors

As for scan_pii().

max_age

Days after which a table is scanned again even if unchanged.

on_error

"warn" (skip a table that cannot be read and list it in the failed attribute) or "stop".

Value

An inventory, with attributes failed (named character vector: table -> error message) and skipped (tables kept from an earlier run).

Details

With an inventory file (path), the scan can be interrupted and run again: tables scanned within max_age days whose row count has not changed are kept rather than scanned again, and the file is rewritten after each table.

Examples

if (FALSE) { # \dontrun{
con <- DBI::dbConnect(RPostgres::Postgres(), dbname = "crm")
inv <- inventory_database(con, source = "postgres:crm", path = "inventory.csv")
inv
} # }