Access audit of baresql_fx_shop

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

2high
4medium
0low
7logins 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.

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()

MEDIUMNew tables that shop_owner creates in schema shop are granted SELECT to PUBLIC.

default-grants-to-public · shop_owner

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 10 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 10 personal data columns; it can be run by shop_etl.

secdef-executable · shop.export_customers()

Who can read personal data

LoginPersonal dataColumnsTablesChangesNotes
shop_reportingpayment card numberemail addressIP addressperson's namepassword or password hashphone numberkey, token or secret1040
shop_apppayment card numberemail addressIP addressperson's namepassword or password hashphone number932
shop_alicepayment card numberemail addressperson's namephone number620
shop_bobpayment card numberemail addressperson's namephone number620some only after SET ROLE
shop_analystemail address110
shop_etlemail address110
shop_internemail address110

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

ColumnHoldsHow BareSQL knowsRead 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 tokenshop_reporting
shop.customers.first_name
table · text
person's name
medium confidence
the column name suggests person's nameshop_alice, shop_app, shop_bob, shop_reporting
shop.customers.last_name
table · text
person's name
medium confidence
the column name suggests person's nameshop_alice, shop_app, shop_bob, shop_reporting
shop.customers.email
table · text
email address
high confidence
40 of 40 sampled values are email addressesshop_alice, shop_app, shop_bob, shop_reporting
shop.customers.phone
table · text
phone number
high confidence
40 of 40 sampled values are phone numbersshop_alice, shop_app, shop_bob, 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_reporting
shop.customers.notes
table · text
email address
medium confidence
1 of 40 sampled values contain an email addressshop_app, shop_reporting
shop.newsletter.email
table · text
email address
high confidence
25 of 25 sampled values are email addressesshop_alice, shop_analyst, shop_app, shop_bob, shop_etl, shop_intern, shop_reporting
shop.users.password_hash
table · text
password or password hash
high confidence
20 of 20 sampled values are password hashesshop_app, shop_reporting
shop.users.last_login_ip
table · inet
IP address
high confidence
20 of 20 sampled values are IP addressesshop_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_nameshop_alice, shop_bob, shop_reporting
support.customer_lookup.email
view · text
email address
high confidence
comes from shop.customers.emailshop_alice, shop_bob, shop_reporting
support.customer_lookup.phone
view · text
phone number
high confidence
comes from shop.customers.phoneshop_alice, shop_bob, shop_reporting
support.customer_lookup.card_number
view · text
payment card number
high confidence
comes from shop.customers.card_numbershop_alice, shop_bob, shop_reporting

Logins

shop_alice

member of shop_support_team · no password set

Personal data it can readHoldsHow
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
ColumnHow
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)
FunctionRuns asHow
shop.count_orders()shop_owner
  • granted to PUBLIC

shop_analyst

member of no role · no password set

Personal data it can readHoldsHow
shop.newsletter.email
table
email address
  • granted to PUBLIC
14 other columns it can read
ColumnHow
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
shop.products.id
  • granted to shop_analyst
shop.products.name
  • granted to shop_analyst
shop.products.sku
  • granted to shop_analyst
shop.products.price
  • granted to shop_analyst
Functions that run with someone else's rights (1)
FunctionRuns asHow
shop.count_orders()shop_owner
  • granted to PUBLIC

shop_app

member of no role · password stored as a SCRAM hash

Personal data it can readHoldsHow
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
ColumnHow
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)
TablePrivilegeHow
shop.customers personal dataINSERT
  • granted to shop_app
shop.customers personal dataUPDATE
  • granted to shop_app
shop.ordersINSERT
  • granted to shop_app
shop.ordersUPDATE
  • granted to shop_app
shop.users personal dataUPDATE (password_hash, last_login_ip)
  • column granted to shop_app
Functions that run with someone else's rights (1)
FunctionRuns asHow
shop.count_orders()shop_owner
  • granted to PUBLIC

shop_bob

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

Personal data it can readHoldsHow
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
ColumnHow
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)
FunctionRuns asHow
shop.count_orders()shop_owner
  • granted to PUBLIC

shop_etl

member of no role · no password set

Personal data it can readHoldsHow
shop.newsletter.email
table
email address
  • granted to PUBLIC
2 other columns it can read
ColumnHow
shop.newsletter.id
  • granted to PUBLIC
shop.newsletter.subscribed_at
  • granted to PUBLIC
Functions that run with someone else's rights (2)
FunctionRuns asHow
shop.count_orders()shop_owner
  • granted to PUBLIC
shop.export_customers()shop_owner no search_path
  • granted to shop_etl

shop_intern

member of no role · no password set

Personal data it can readHoldsHow
shop.newsletter.email
table
email address
  • granted to PUBLIC
2 other columns it can read
ColumnHow
shop.newsletter.id
  • granted to PUBLIC
shop.newsletter.subscribed_at
  • granted to PUBLIC
Functions that run with someone else's rights (1)
FunctionRuns asHow
shop.count_orders()shop_owner
  • granted to PUBLIC

shop_reporting

member of pg_read_all_data · no password set

Personal data it can readHoldsHow
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.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
24 other columns it can read
ColumnHow
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.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)
FunctionRuns asHow
shop.count_orders()shop_owner
  • granted to PUBLIC

Everyone (PUBLIC)

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

Personal dataHoldsHow
shop.newsletter.emailemail address
  • granted to PUBLIC
2 other columns every role can read
ColumnHow
shop.newsletter.id
  • granted to PUBLIC
shop.newsletter.subscribed_at
  • granted to PUBLIC

Tables created later

Default privileges hand these rights out on objects that don't exist yet.

Whencreatesinit grantsto
shop_ownertablesshopSELECTPUBLIC

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.