Postgres Query Anonymizer

Author: Mujeeb · Version: 1.0.0

Mask the customer data in a PostgreSQL query or an EXPLAIN plan before you share it. The values are replaced with placeholders, and the query and the plan stay readable.

SET search_path TO shop;/*app=orders-api,user=4711,client=192.168.10.77*/ SELECT ... WHERE (c.email = 'jane@example.com') AND (o.status IN ('Open','Can''t ship','Open'))
   Filter: (((email)::text = 'jane@example.com'::text) AND ((status)::text = ANY ('{Open,"Can''t ship"}'::text[])))
   Foreign File: /srv/exports/acme-corp/customers.csv

becomes

SET search_path TO shop;/*$C1*/ SELECT ... WHERE (c.email = '$S1') AND (o.status IN ('$S2','$S3','$S2'))
   Filter: (((email)::text = '$S1'::text) AND ((status)::text = ANY ('$S4[2]'::text[])))
   Foreign File: $PATH1

The same value always gets the same placeholder, in the query and in the plan, so you can still see that two filters compare against the same string.

How to use it

• Postgres: Anonymize Query / EXPLAIN to New Tab (Command Palette). It anonymizes the selection in the editor, or the whole editor, or, if the editor is empty, the clipboard. The result opens in a new tab.
• Postgres: Anonymize Query / EXPLAIN in Place replaces the selection (or the whole editor) with its anonymized text. With several selections, they share placeholders.
• Postgres: Copy Anonymized Query / EXPLAIN copies the anonymized selection (or the whole editor) to the clipboard and leaves the editor as it is.
• Postgres: Anonymize Clipboard anonymizes what's on the clipboard, in place. Copy, run it, paste.
• In VS Code's terminal, select the output, right-click and choose Anonymize to New Tab. The clipboard is left holding the anonymized text, not the original.

The first three are also in the editor's right-click menu when there's a selection. Each command says what it masked ("Masked 12 strings, 1 comment and 2 IP addresses."). Placeholders already in the text, from an earlier run, are kept, and new ones are numbered after them.

What it masks

Kept as they are:

• Empty strings ('') and empty arrays ('{}'), since there's nothing in them.
• Optimizer hints (/*+ SeqScan(t) */).
• Dollar-quoted function and DO bodies (AS $$ ... $$). They're code, so only the strings in them are masked.
• Numbers, including the IDs in NOT IN (9000000000000000001, ...). (EXPLAIN often prints them as strings, '9000000000000000001'::bigint, and those are masked.)
• Table, column, index and schema names (SET search_path TO shop), and the plan itself: costs, times, row counts.
• What only looks like an address or a path: a number right after the word version (version 19.3.0.0), ::1, casts (a::text), divisions (size/1024/1024), URLs and /dev/null.

What it doesn't mask

Read the result before you share it. These are left as they are:

• The rows of a query's result. In psql's table output only the email and IP addresses are masked.
• Host, database, role and user names (db.example.com, -U app), and the names in double quotes ("Order Status").
• Values that PostgreSQL prints without quotes, such as Key (name)=(Jane Doe) in an error.
• Relative paths (exports/customers.csv), and a password= that stands alone. Next to another key=value, as in a connection string, it is masked; in SQL (WHERE password=$1) it's a column.

Markdown and pasted notes

In Markdown, fenced code blocks are kept and the strings in them masked, and in the prose around them an apostrophe isn't taken for a quote: Here's the query; it's slow for the users' list stays as it is. A quote in prose that isn't closed on its line (the '90s) is left alone too.

Apostrophes are recognized in queries as well: in comments (-- don't), in quoted names ("it's"), and inside a quoted value in a sentence (it fails for 'O'Brien' masks the whole name).

When to check the result

The extension warns you, with the line and a Go to Line button, when:

• A string literal isn't closed (a query cut off in a log, say). Everything from that quote to the end (or to the end of the code block) is masked, because it can't tell where the string stops.
• A string literal runs over several lines. That's fine for a string that really has line breaks. If it doesn't, a stray quote has paired with the wrong one; check what was masked around it.

If there are more such places, the warning names their lines too.

Settings

Privacy

The text is anonymized on your machine. The extension makes no network requests, starts no other program and writes no files; it only reads and changes the editor and the clipboard.

The Python script

python/anonymize_query.py, in this extension's repository, does the same from the command line, with no VS Code or packages needed:

python3 anonymize_query.py input.md
python3 anonymize_query.py input.md -o output.md
cat plan.txt | python3 anonymize_query.py

--keep-comments, --keep-backticks, --keep-ips, --keep-emails, --keep-paths and --keep-passwords turn masks off. Warnings go to standard error. The file is read and written as UTF-8, with its line endings unchanged.

Tests

• npm run test:unit runs the cases in test/cases.json and the fixtures in test/fixtures. It also runs a fuzz test of 3,000 random queries, plans and notes, with secrets in literals, paths, connection strings and email addresses: no secret may survive, and a second run must change nothing.
• npm run test:python runs the same cases and fixtures against the Python script, plus its command line.
• npm run test:integration runs the commands in a separate VS Code window. It uses the VS Code in /Applications; set VSCODE_EXECUTABLE for another one. It puts its own text on the clipboard.

Release

Bump version in package.json, add the changes to CHANGELOG.md, commit, then push a matching tag. The Publish workflow runs the unit tests and publishes to the Marketplace.

git tag v1.0.1
git push origin v1.0.1

License

MIT. The full text is in the LICENSE file.