Password-protect PDFs and zip report packs

Finance, payroll and member mail often must send files only the recipient can open: a statement PDF per member that needs their password, or one zip holding a client’s whole monthly pack. DRE does both during the run, so hold: and dre release send exactly what was reviewed, and the passwords never show in DRE’s own logs and results. New in DRE 0.7 (PDF passwords need the document plugin 1.1.0).

Three keys do it:

password: on a pdf output encrypts each PDF with an open password (AES-256).
zip: on a destination entry delivers one zip of the output’s files, plus every attach:ed output’s, optionally with a password (AES-256 by default).
secret_columns: on a query entry masks those columns’ values wherever DRE reports.

DRE never makes up or stores passwords. You make them in SQL (a column per member) or read them with env_var(); when a team needs the file → password list, it’s an ordinary output sent somewhere you control.

A password PDF per member

queries:
  - {query: members, secret_columns: [pdf_password]}   # member_id, email, alt_email, first_name, pdf_password
  - holdings
output:
  - name: statement
    format: pdf
    template: {file: statement.html}
    for_each: {query: members, key: member_id}
    password: "{{ each.pdf_password }}"
  - name: statement_mail
    format: message
    for_each: {query: members, key: member_id}
    title: "Your statement, {{ each.first_name }}"
    attach: [statement]
    destination: {profile: member_mail, to: "{{ each.email }}", hold: true}
  - name: password_mail                 # leave this out when the member already knows it
    format: message
    for_each: {query: members, key: member_id}
    title: "Your statement password"
    text: "Your password is {{ each.pdf_password }}."
    destination: {profile: member_mail, to: "{{ each.alt_email }}", hold: true}

The password can be anything the member knows, built in SQL, for example their date of birth: strftime(date_of_birth, '%d%m%Y') as pdf_password. Keep PDF passwords to ASCII letters, digits and punctuation: macOS Preview and Safari can’t open a PDF whose password has other characters (Acrobat, Chrome, Edge and Firefox can). See PDF and HTML documents.

A client pack in one zip

output:
  - name: workbook
    format: xlsx
    queries: [summary]
  - name: detail
    format: csv                          # detail_summary.csv, detail_fees.csv
    queries: [summary, fees]
    destination:
      profile: client_sftp
      path: "/out/pack-{{ run.date.yyyymmdd }}.zip"
      attach: [workbook]
      zip: {password: "{{ env_var('PACK_PW') }}"}    # zip: true for no password

What goes in: the output’s files and every attach:ed output’s files, flat (no folders). Two files with one name are an error, never renamed: dre validate reports it, or for a per-row output (whose names render per row) the row fails before anything is delivered. A message can’t go in a zip.
Name: the destination’s path (it must end in .zip), or the email’s attachment_name; with neither, <output>.zip (a per-row output: the row file’s name, like 101.zip).
Every destination: local, S3, Azure, GCS, SFTP, FTP, Databricks Volumes and email all receive the zip as one file. attach: works on any destination when the entry zips.
Per row: with for_each, each row gets its own zip, and each.* in the password.
One zip, built once: the zip is built during the run into the run folder. Entries zipping the same files the same way share it. A held entry records the zip and its SHA-256, and dre release sends it unchanged, or refuses if it changed.
Encryption: encryption: aes256 (default) or zipcrypto. AES-256 zips open in 7-Zip, WinRAR, Keka, The Unarchiver and most archive tools, but not in Windows Explorer or macOS Archive Utility. zipcrypto opens there, but it’s weak (it can be cracked), so dre validate warns.
An empty password is an error, never an unencrypted file. Email’s max_attachment_mb checks the zip.

Keep passwords out of DRE’s records

secret_columns: on a query entry lists columns whose values are secrets. Their values become ***** everywhere DRE reports: the console, dre.log, run_results.json, rows.jsonl, --preview, error messages, and --output-json (which is what dre-mcp answers from). They’re never masked in what DRE delivers: the PDFs, zips and messages, and the held files waiting for dre release, are the data. A password read with env_var() in password: or zip.password is masked the same way.

Every secret value is masked, however short. One under 4 characters (a two-digit PIN) is masked where it stands alone, not inside a longer word or number, so PIN 73 becomes PIN ***** while the year 2073 stays readable. mask_secrets: false in dre_project.yml turns all masking off.

dre validate warns when a password would show:

password: or zip.password reads each.<column> or results.<query> of a column not in that query’s secret_columns;
it’s written in the YAML as plain text (it’s masked in DRE’s output, but anyone who reads the project sees it), or reads var() (vars show in the run’s log): use env_var() or a secret column;
a message, title or path of a per-row output prints a driver column named like a password (password, pwd, pin, passcode, secret, or ending in _password…) that isn’t secret;
a password list goes to the destination profile that receives the files it unlocks.

--output-json saves secret columns masked, so a later --from-results replays ***** instead of the passwords.

The file → password list

When the client wants the mapping, make it an output and send it somewhere they control, never to the same place as the files it unlocks:

queries:
  - {query: password_list, secret_columns: [pdf_password]}   # member_id, pdf_password
output:
  - name: password_list
    format: csv
    queries: [password_list]
    destination: {profile: secure_sftp, path: "/keys/{{ run.date.yyyymmdd }}.csv"}

Not included

No xlsx or docx passwords (zip them instead), no PDF print or copy restrictions, no folders inside a zip and no other archive formats. DRE doesn’t generate or store passwords.