Access audit of baresql_fx_shop

2 October 2026, 09:00 UTC · PostgreSQL 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1) · 7 logins · BareSQL 0.1.0

2high

3medium

0low

7logins that read personal data

11columns 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.

public-reads-personal · shop.newsletter

Details

email (email)

HIGHshop.export_customers() runs with the rights of shop_owner and doesn't set search_path: whoever can run it may be able to make it run their own code.

secdef-no-search-path · shop.export_customers()

MEDIUMshop_reporting is a member of pg_read_all_data: it reaches every table.

read-all-data · shop_reporting

MEDIUMshop.count_orders() runs with the rights of shop_owner, who can read 11 personal data columns; it can be run by every role (PUBLIC).

secdef-executable · shop.count_orders()

MEDIUMshop.export_customers() runs with the rights of shop_owner, who can read 11 personal data columns; it can be run by shop_etl.

secdef-executable · shop.export_customers()

Who can read personal data

Login	Personal data	Columns	Tables	Changes	Notes
shop_reporting	bank account (IBAN)payment card numberemail addressIP addressperson's namepassword or password hashphone numberkey, token or secret	11	5	0
shop_app	payment card numberemail addressIP addressperson's namepassword or password hashphone number	9	3	2
shop_alice	payment card numberemail addressperson's namephone number	6	2	0
shop_bob	payment card numberemail addressperson's namephone number	6	2	0	some only after SET ROLE
shop_intern	payment card numberemail addressperson's namephone number	6	2	0
shop_analyst	email address	1	1	0
shop_etl	email address	1	1	0

Columns and tables count base tables, partitions and materialized views, reached in any way listed below. Changes counts tables with personal data the login can insert into, update, delete from or empty.

Personal data in this database

Column	Holds	How BareSQL knows	Read by
shop.api_credentials.credential table · text	key, token or secret high confidence	4 of 4 sampled values look like random tokens; 4 of 4 sampled values contain a key or token	shop_reporting
shop.customers.first_name table · text	person's name medium confidence	the column name suggests person's name	shop_alice, shop_app, shop_bob, shop_intern, shop_reporting
shop.customers.last_name table · text	person's name medium confidence	the column name suggests person's name	shop_alice, shop_app, shop_bob, shop_intern, shop_reporting
shop.customers.email table · text	email address high confidence	40 of 40 sampled values are email addresses	shop_alice, shop_app, shop_bob, shop_intern, shop_reporting
shop.customers.phone table · text	phone number high confidence	40 of 40 sampled values are phone numbers	shop_alice, shop_app, shop_bob, shop_intern, shop_reporting
shop.customers.card_number table · text	payment card number high confidence	40 of 40 sampled values are payment card numbers (valid check digit)	shop_alice, shop_app, shop_bob, shop_intern, shop_reporting
shop.customers.notes table · text	email address medium confidence	1 of 40 sampled values contain an email address	shop_app, shop_reporting
shop.newsletter.email table · text	email address high confidence	25 of 25 sampled values are email addresses	shop_alice, shop_analyst, shop_app, shop_bob, shop_etl, shop_intern, shop_reporting
shop.refunds.iban table · text	bank account (IBAN) high confidence	12 of 12 sampled values are IBANs (valid check digits)	shop_reporting
shop.users.password_hash table · text	password or password hash high confidence	20 of 20 sampled values are password hashes	shop_app, shop_reporting
shop.users.last_login_ip table · inet	IP address high confidence	20 of 20 sampled values are IP addresses	shop_app, shop_reporting
support.customer_lookup.customer_name view · text	person's name medium confidence, computed	comes from shop.customers.first_name; comes from shop.customers.last_name	shop_alice, shop_bob, shop_intern, shop_reporting
support.customer_lookup.email view · text	email address high confidence	comes from shop.customers.email	shop_alice, shop_bob, shop_intern, shop_reporting
support.customer_lookup.phone view · text	phone number high confidence	comes from shop.customers.phone	shop_alice, shop_bob, shop_intern, shop_reporting
support.customer_lookup.card_number view · text	payment card number high confidence	comes from shop.customers.card_number	shop_alice, shop_bob, shop_intern, shop_reporting

Logins

member of shop_support_team · no password set

Personal data it can read	Holds	How
shop.customers.first_name table	person's name	through view support.customer_lookup (rights of shop_owner)
shop.customers.last_name table	person's name	through view support.customer_lookup (rights of shop_owner)
shop.customers.email table	email address	through view support.customer_lookup (rights of shop_owner)
shop.customers.phone table	phone number	through view support.customer_lookup (rights of shop_owner)
shop.customers.card_number table	payment card number	through view support.customer_lookup (rights of shop_owner)
shop.newsletter.email table	email address	granted to PUBLIC
support.customer_lookup.customer_name view	person's name	granted to shop_support_team
support.customer_lookup.email view	email address	granted to shop_support_team
support.customer_lookup.phone view	phone number	granted to shop_support_team
support.customer_lookup.card_number view	payment card number	granted to shop_support_team

10 other columns it can read

Column	How
shop.customers.id	through view support.customer_lookup (rights of shop_owner)
shop.newsletter.id	granted to PUBLIC
shop.newsletter.subscribed_at	granted to PUBLIC
shop.orders.id	through view support.order_status (rights of shop_owner)
shop.orders.tracking_number	through view support.order_status (rights of shop_owner)
shop.orders.shipped_at	through view support.order_status (rights of shop_owner)
support.customer_lookup.id	granted to shop_support_team
support.order_status.id	granted to shop_support_team
support.order_status.shipped_at	granted to shop_support_team
support.order_status.tracking_number	granted to shop_support_team

Functions that run with someone else's rights (1)

Function	Runs as	How
shop.count_orders()	shop_owner	granted to PUBLIC

member of no role · no password set

Personal data it can read	Holds	How
shop.newsletter.email table	email address	granted to PUBLIC

10 other columns it can read

Column	How
shop.customers.id	column granted to shop_analyst
shop.customers.city	column granted to shop_analyst
shop.customers.created_at	column granted to shop_analyst
shop.newsletter.id	granted to PUBLIC
shop.newsletter.subscribed_at	granted to PUBLIC
shop.orders.id	granted to shop_analyst
shop.orders.customer_id	granted to shop_analyst
shop.orders.total	granted to shop_analyst
shop.orders.tracking_number	granted to shop_analyst
shop.orders.shipped_at	granted to shop_analyst

Functions that run with someone else's rights (1)

Function	Runs as	How
shop.count_orders()	shop_owner	granted to PUBLIC

member of no role · password stored as a SCRAM hash

Personal data it can read	Holds	How
shop.customers.first_name table	person's name	granted to shop_app
shop.customers.last_name table	person's name	granted to shop_app
shop.customers.email table	email address	granted to shop_app
shop.customers.phone table	phone number	granted to shop_app
shop.customers.card_number table	payment card number	granted to shop_app
shop.customers.notes table	email address	granted to shop_app
shop.newsletter.email table	email address	granted to PUBLIC
shop.users.password_hash table	password or password hash	granted to shop_app
shop.users.last_login_ip table	IP address	granted to shop_app

18 other columns it can read

Column	How
shop.customers.id	granted to shop_app
shop.customers.city	granted to shop_app
shop.customers.created_at	granted to shop_app
shop.customers.marketing_opt_in	granted to shop_app
shop.newsletter.id	granted to PUBLIC
shop.newsletter.subscribed_at	granted to PUBLIC
shop.orders.id	granted to shop_app
shop.orders.customer_id	granted to shop_app
shop.orders.total	granted to shop_app
shop.orders.tracking_number	granted to shop_app
shop.orders.shipped_at	granted to shop_app
shop.products.id	granted to shop_app
shop.products.name	granted to shop_app
shop.products.sku	granted to shop_app
shop.products.price	granted to shop_app
shop.users.id	granted to shop_app
shop.users.username	granted to shop_app
shop.users.email_verified	granted to shop_app

What it can change (5)

Table	Privilege	How
shop.customers personal data	INSERT	granted to shop_app
shop.customers personal data	UPDATE	granted to shop_app
shop.orders	INSERT	granted to shop_app
shop.orders	UPDATE	granted to shop_app
shop.users personal data	UPDATE (password_hash, last_login_ip)	column granted to shop_app

Functions that run with someone else's rights (1)

Function	Runs as	How
shop.count_orders()	shop_owner	granted to PUBLIC

noinherit · member of shop_support_team (no inherit) · can SET ROLE shop_support_team · no password set

Personal data it can read	Holds	How
shop.customers.first_name table	person's name	after SET ROLE shop_support_team: through view support.customer_lookup (rights of shop_owner)
shop.customers.last_name table	person's name	after SET ROLE shop_support_team: through view support.customer_lookup (rights of shop_owner)
shop.customers.email table	email address	after SET ROLE shop_support_team: through view support.customer_lookup (rights of shop_owner)
shop.customers.phone table	phone number	after SET ROLE shop_support_team: through view support.customer_lookup (rights of shop_owner)
shop.customers.card_number table	payment card number	after SET ROLE shop_support_team: through view support.customer_lookup (rights of shop_owner)
shop.newsletter.email table	email address	granted to PUBLIC
support.customer_lookup.customer_name view	person's name	after SET ROLE shop_support_team: granted to shop_support_team
support.customer_lookup.email view	email address	after SET ROLE shop_support_team: granted to shop_support_team
support.customer_lookup.phone view	phone number	after SET ROLE shop_support_team: granted to shop_support_team
support.customer_lookup.card_number view	payment card number	after SET ROLE shop_support_team: granted to shop_support_team

10 other columns it can read

Column	How
shop.customers.id	after SET ROLE shop_support_team: through view support.customer_lookup (rights of shop_owner)
shop.newsletter.id	granted to PUBLIC
shop.newsletter.subscribed_at	granted to PUBLIC
shop.orders.id	after SET ROLE shop_support_team: through view support.order_status (rights of shop_owner)
shop.orders.tracking_number	after SET ROLE shop_support_team: through view support.order_status (rights of shop_owner)
shop.orders.shipped_at	after SET ROLE shop_support_team: through view support.order_status (rights of shop_owner)
support.customer_lookup.id	after SET ROLE shop_support_team: granted to shop_support_team
support.order_status.id	after SET ROLE shop_support_team: granted to shop_support_team
support.order_status.shipped_at	after SET ROLE shop_support_team: granted to shop_support_team
support.order_status.tracking_number	after SET ROLE shop_support_team: granted to shop_support_team

Functions that run with someone else's rights (1)

Function	Runs as	How
shop.count_orders()	shop_owner	granted to PUBLIC

member of no role · no password set

Personal data it can read	Holds	How
shop.newsletter.email table	email address	granted to PUBLIC

2 other columns it can read

Column	How
shop.newsletter.id	granted to PUBLIC
shop.newsletter.subscribed_at	granted to PUBLIC

Functions that run with someone else's rights (2)

Function	Runs as	How
shop.count_orders()	shop_owner	granted to PUBLIC
shop.export_customers()	shop_owner no search_path	granted to shop_etl

member of shop_support_team · no password set

Personal data it can read	Holds	How
shop.customers.first_name table	person's name	through view support.customer_lookup (rights of shop_owner)
shop.customers.last_name table	person's name	through view support.customer_lookup (rights of shop_owner)
shop.customers.email table	email address	through view support.customer_lookup (rights of shop_owner)
shop.customers.phone table	phone number	through view support.customer_lookup (rights of shop_owner)
shop.customers.card_number table	payment card number	through view support.customer_lookup (rights of shop_owner)
shop.newsletter.email table	email address	granted to PUBLIC
support.customer_lookup.customer_name view	person's name	granted to shop_support_team
support.customer_lookup.email view	email address	granted to shop_support_team
support.customer_lookup.phone view	phone number	granted to shop_support_team
support.customer_lookup.card_number view	payment card number	granted to shop_support_team

10 other columns it can read

Column	How
shop.customers.id	through view support.customer_lookup (rights of shop_owner)
shop.newsletter.id	granted to PUBLIC
shop.newsletter.subscribed_at	granted to PUBLIC
shop.orders.id	through view support.order_status (rights of shop_owner)
shop.orders.tracking_number	through view support.order_status (rights of shop_owner)
shop.orders.shipped_at	through view support.order_status (rights of shop_owner)
support.customer_lookup.id	granted to shop_support_team
support.order_status.id	granted to shop_support_team
support.order_status.shipped_at	granted to shop_support_team
support.order_status.tracking_number	granted to shop_support_team

Functions that run with someone else's rights (1)

Function	Runs as	How
shop.count_orders()	shop_owner	granted to PUBLIC

member of pg_read_all_data · no password set

Personal data it can read	Holds	How
shop.api_credentials.credential table	key, token or secret	member of pg_read_all_data
shop.customers.first_name table	person's name	member of pg_read_all_data through view support.customer_lookup (rights of shop_owner)
shop.customers.last_name table	person's name	member of pg_read_all_data through view support.customer_lookup (rights of shop_owner)
shop.customers.email table	email address	member of pg_read_all_data through view support.customer_lookup (rights of shop_owner)
shop.customers.phone table	phone number	member of pg_read_all_data through view support.customer_lookup (rights of shop_owner)
shop.customers.card_number table	payment card number	member of pg_read_all_data through view support.customer_lookup (rights of shop_owner)
shop.customers.notes table	email address	member of pg_read_all_data
shop.newsletter.email table	email address	granted to PUBLIC member of pg_read_all_data
shop.refunds.iban table	bank account (IBAN)	member of pg_read_all_data
shop.users.password_hash table	password or password hash	member of pg_read_all_data
shop.users.last_login_ip table	IP address	member of pg_read_all_data
support.customer_lookup.customer_name view	person's name	member of pg_read_all_data
support.customer_lookup.email view	email address	member of pg_read_all_data
support.customer_lookup.phone view	phone number	member of pg_read_all_data
support.customer_lookup.card_number view	payment card number	member of pg_read_all_data

27 other columns it can read

Column	How
shop.api_credentials.id	member of pg_read_all_data
shop.api_credentials.service	member of pg_read_all_data
shop.customers.id	member of pg_read_all_data through view support.customer_lookup (rights of shop_owner)
shop.customers.city	member of pg_read_all_data
shop.customers.created_at	member of pg_read_all_data
shop.customers.marketing_opt_in	member of pg_read_all_data
shop.newsletter.id	granted to PUBLIC member of pg_read_all_data
shop.newsletter.subscribed_at	granted to PUBLIC member of pg_read_all_data
shop.orders.id	member of pg_read_all_data through view support.order_status (rights of shop_owner)
shop.orders.customer_id	member of pg_read_all_data
shop.orders.total	member of pg_read_all_data
shop.orders.tracking_number	member of pg_read_all_data through view support.order_status (rights of shop_owner)
shop.orders.shipped_at	member of pg_read_all_data through view support.order_status (rights of shop_owner)
shop.products.id	member of pg_read_all_data
shop.products.name	member of pg_read_all_data
shop.products.sku	member of pg_read_all_data
shop.products.price	member of pg_read_all_data
shop.refunds.id	member of pg_read_all_data
shop.refunds.order_id	member of pg_read_all_data
shop.refunds.amount	member of pg_read_all_data
shop.users.id	member of pg_read_all_data
shop.users.username	member of pg_read_all_data
shop.users.email_verified	member of pg_read_all_data
support.customer_lookup.id	member of pg_read_all_data
support.order_status.id	member of pg_read_all_data
support.order_status.shipped_at	member of pg_read_all_data
support.order_status.tracking_number	member of pg_read_all_data

Functions that run with someone else's rights (1)

Function	Runs as	How
shop.count_orders()	shop_owner	granted to PUBLIC

Everyone (PUBLIC)

What every role can read, including roles created after this audit.

Personal data	Holds	How
shop.newsletter.email	email address	granted to PUBLIC

2 other columns every role can read

Column	How
shop.newsletter.id	granted to PUBLIC
shop.newsletter.subscribed_at	granted to 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.

Access audit of baresql_fx_shop

Findings

Who can read personal data

Personal data in this database

Logins

shop_alice

shop_analyst

shop_app

shop_bob

shop_etl

shop_intern

shop_reporting

Everyone (PUBLIC)

Method