Access audit of baresql_fx_shop
13 October 2026, 09:00 UTC · PostgreSQL 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1) · 2 logins · BareSQL 0.1.0
1high
2medium
0low
2logins that read personal data
10columns with personal data
BareSQL logged in as postgres (a superuser) over an unencrypted connection and read the system catalogs inside one read-only transaction.
It read up to 200 rows of each table to see what the columns hold; the values were looked at in memory and are not in this report.
For every login, the server's own privilege checks were asked about every table and column, and each answer below comes with the grant, membership, ownership or view that gives it.
Findings
HIGHEvery role, including roles created later, can read personal data in shop.newsletter.
Details
- email (email)
MEDIUMNew tables that shop_owner creates in schema shop are granted SELECT to PUBLIC.
MEDIUMshop.count_orders() runs with the rights of shop_owner, who can read 10 personal data columns; it can be run by every role (PUBLIC).
Who can read personal data
| Login | Personal data | Columns | Tables | Changes | Notes |
|---|---|---|---|---|---|
| shop_alice | email addressperson's namephone number | 5 | 2 | 0 | |
| shop_bob | email addressperson's namephone number | 5 | 2 | 0 | some only after SET ROLE |
Personal data in this database
| Column | Holds | How BareSQL knows | Read by |
|---|---|---|---|
| shop.api_credentials.credential | key, token or secret | 4 of 4 sampled values look like random tokens; 4 of 4 sampled values contain a key or token | |
| shop.customers.first_name | person's name | the column name suggests person's name | shop_alice, shop_bob |
| shop.customers.last_name | person's name | the column name suggests person's name | shop_alice, shop_bob |
| shop.customers.email | email address | 40 of 40 sampled values are email addresses | shop_alice, shop_bob |
| shop.customers.phone | phone number | 40 of 40 sampled values are phone numbers | shop_alice, shop_bob |
| shop.customers.card_number | payment card number | 40 of 40 sampled values are payment card numbers (valid check digit) | |
| shop.customers.notes | email address | 1 of 40 sampled values contain an email address | |
| shop.newsletter.email | email address | 25 of 25 sampled values are email addresses | shop_alice, shop_bob |
| shop.users.password_hash | password or password hash | 20 of 20 sampled values are password hashes | |
| shop.users.last_login_ip | IP address | 20 of 20 sampled values are IP addresses | |
| support.customer_lookup.customer_name | person's name | comes from shop.customers.first_name; comes from shop.customers.last_name | shop_alice, shop_bob |
| support.customer_lookup.email | email address | comes from shop.customers.email | shop_alice, shop_bob |
| support.customer_lookup.phone | phone number | comes from shop.customers.phone | shop_alice, shop_bob |
Logins
shop_alice
| Personal data it can read | Holds | How |
|---|---|---|
| shop.customers.first_name | person's name |
|
| shop.customers.last_name | person's name |
|
| shop.customers.email | email address |
|
| shop.customers.phone | phone number |
|
| shop.newsletter.email | email address |
|
| support.customer_lookup.customer_name | person's name |
|
| support.customer_lookup.email | email address |
|
| support.customer_lookup.phone | phone number |
|
10 other columns it can read
| Column | How |
|---|---|
| shop.customers.id |
|
| shop.newsletter.id |
|
| shop.newsletter.subscribed_at |
|
| shop.orders.id |
|
| shop.orders.tracking_number |
|
| shop.orders.shipped_at |
|
| support.customer_lookup.id |
|
| support.order_status.id |
|
| support.order_status.shipped_at |
|
| support.order_status.tracking_number |
|
Functions that run with someone else's rights (1)
| Function | Runs as | How |
|---|---|---|
| shop.count_orders() | shop_owner |
|
shop_bob
| Personal data it can read | Holds | How |
|---|---|---|
| shop.customers.first_name | person's name |
|
| shop.customers.last_name | person's name |
|
| shop.customers.email | email address |
|
| shop.customers.phone | phone number |
|
| shop.newsletter.email | email address |
|
| support.customer_lookup.customer_name | person's name |
|
| support.customer_lookup.email | email address |
|
| support.customer_lookup.phone | phone number |
|
10 other columns it can read
| Column | How |
|---|---|
| shop.customers.id |
|
| shop.newsletter.id |
|
| shop.newsletter.subscribed_at |
|
| shop.orders.id |
|
| shop.orders.tracking_number |
|
| shop.orders.shipped_at |
|
| support.customer_lookup.id |
|
| support.order_status.id |
|
| support.order_status.shipped_at |
|
| support.order_status.tracking_number |
|
Functions that run with someone else's rights (1)
| Function | Runs as | How |
|---|---|---|
| shop.count_orders() | shop_owner |
|
Everyone (PUBLIC)
What every role can read, including roles created after this audit.
| Personal data | Holds | How |
|---|---|---|
| shop.newsletter.email | email address |
|
2 other columns every role can read
| Column | How |
|---|---|
| shop.newsletter.id |
|
| shop.newsletter.subscribed_at |
|
Tables created later
Default privileges hand these rights out on objects that don't exist yet.
| When | creates | in | it grants | to |
|---|---|---|---|---|
| shop_owner | tables | shop | SELECT | PUBLIC |
Method
Schemas audited: public, shop, support. Connection: postgres://[email protected]:5432/baresql_fx_shop, login method md5.
BareSQL never calls a function written in SQL or a procedural language, never reads views or foreign tables, and never writes. Sampled values are turned into text by their type's own output function, never by a cast, and each table is read on its own, without its partitions or child tables. Views are traced to the columns they read from their stored definitions, without running them.
Every fact in this report is also in audit.json, which baresql diff and baresql audit --baseline compare with a later audit.