BareSQL
BareSQL is one small program with two tools for SQL databases.
BareSQL Audit logs in to a PostgreSQL database and lists, for every
login, each column it can read, how it gets there (a grant, a role, a view,
a function, ownership) and whether the column holds personal data. It reads
the system catalogs and a small sample of values inside a read-only
transaction, and it never calls a function you wrote.
BareSQL Portable checks SQL that has to run on SQLite, PostgreSQL,
MySQL and MariaDB. baresql lint points out constructs that answer
differently on different engines, with a form that answers the same
everywhere. baresql compare runs the same script on real engines and
reports every statement whose answers differ. baresql rules lists the
differences BareSQL knows, each with a minimal example and the answer every
engine gave.
The program is a single file with no dependencies. It is written in Go with
the standard library only, and it talks to PostgreSQL, MySQL and MariaDB
over their own network protocols. SQLite runs through the sqlite3
program you have installed.
Install
Download the archive for your system from the latest release, unpack it
and put baresql (or baresql.exe) on your PATH:
SHA256SUMS
holds the checksums of all five, and the
releases page has
every version.
Or build it yourself with Go 1.24 or later (the release archives are built
with the current stable Go):
go install github.com/softdevstudio/baresql/cmd/baresql@latest
baresql version prints the version, and baresql help <command> the
flags of a command.
BareSQL Audit
baresql audit [flags] [connection]
baresql diff old.json new.json
baresql audit answers one question for a PostgreSQL database: which login
can read which column, and how. For each login it lists every column it can
read and every way it gets there, marks the columns that hold personal data,
and adds findings for the setups that usually lead to leaks. It writes two
files:
report.html, a page to read and share: findings first, then one table
per login with the personal data it reaches and the route to each column.audit.json, the same facts for scripts and for baresql diff.
It also prints a short summary. Values read while looking for personal
data are never written anywhere: the report says which category a column
looks like (an email address, a card number) and why, never what it holds.
JSON keys can be data too (a map keyed by email address), so evidence names
a key only when it is a word the column-name rules know, such as email
or contact, and shows any other key as *: JSON key *.email.
Connecting
The connection is a postgres:// URL or a key=value string, as for
psql:
baresql audit "postgres://[email protected]/shop?sslmode=verify-full"
baresql audit "host=db.example.com dbname=shop user=auditor"
Whatever the connection leaves out comes from the usual PG* variables
(PGHOST, PGUSER, PGPASSWORD ...) and from ~/.pgpass, so the password
never has to be on the command line. TLS follows sslmode (disable,
prefer by default, require, verify-ca, verify-full) with
sslrootcert; sslrootcert=system uses the system's certificates and
implies verify-full. Unix sockets work as with psql
(host=/var/run/postgresql). Logins use SCRAM-SHA-256 (with channel
binding over TLS), MD5 or a cleartext password, whichever the server asks
for.
Which login to audit with
Any login can run the audit: the catalogs it reads are visible to every
role, and the server's own has_*_privilege functions answer for every
role. Two things need more:
- Looking at values (to tell an
email column from one named notes that
holds email addresses) needs read access to the tables. A superuser can
sample everything. A member of pg_read_all_data samples every table
except those where row security applies to it, and another login samples
what it may read.
- Telling MD5 passwords from SCRAM needs a superuser, who can read
pg_authid.
Whatever the audit could not see is listed under "Not seen" in the report
and the summary, so a partial audit says what it left out.
What the audit does to the database
It opens one connection, starts a read-only transaction (BEGIN ISOLATION LEVEL REPEATABLE READ, READ ONLY), sets statement_timeout, lock_timeout,
search_path and DateStyle for that transaction only, and turns row
security off for its own sampling queries (a non-superuser then sees an
error instead of silently filtered rows, and the table is left out of
sampling). It never writes and rolls the transaction back at the end.
This is the beginning of the README. Read the rest at the original. Read the full README ↗
