Access audit of baresql_fx_clinic

26 October 2026, 12:00 UTC · PostgreSQL 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1) · 9 logins · BareSQL 0.1.0

1high
3medium
1low
7logins that read personal data
13columns 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

HIGHclinic_it can read files on the database server.

server-file-access · clinic_it
Details
  • may run pg_read_file(text)
  • member of pg_read_server_files

MEDIUMclinic_hr can make any role a member of clinic_doctor, which reads personal data.

admin-option · clinic_hr

MEDIUMclinic_billing may grant read access to personal data to other roles.

grant-option-personal · clinic_billing
Details
  • clinic.patients.email
  • clinic.patients.insurance_number

MEDIUMclinic_lab has an MD5 password hash, which is weak and deprecated.

md5-passwords · logins
Details
  • clinic_lab

LOWclinic_lab can use foreign server clinic_lab_server (clinic_fdw) through a user mapping.

foreign-server-mapping · clinic_lab

Who can read personal data

LoginPersonal dataColumnsTablesChangesNotes
clinic_auditorpostal addressdate of birthemail addresshealth datainsurance numberperson's namenational ID numberphone number1030
clinic_drsmithpostal addressdate of birthemail addresshealth datainsurance numberperson's namenational ID numberphone number1030
clinic_locumpostal addressdate of birthemail addresshealth datainsurance numberperson's namenational ID numberphone number1030some only after SET ROLE
clinic_frontdeskperson's namephone number310
clinic_billingemail addressinsurance number210
clinic_researchhealth data220
clinic_labemail addressperson's name110

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
clinic.diagnosis_stats.diagnosis
materialized view · text
health data
medium confidence
the column name suggests health data; comes from clinic.visits.diagnosisclinic_research
clinic.lab_results.payload
table · jsonb
email addressperson's name
high confidence
JSON key patient.email holds email address in 20 of 20 sampled values; JSON key patient.email (in 20 of 20 sampled values) is named like email address; JSON key patient.name (in 20 of 20 sampled values) is named like person's nameclinic_auditor, clinic_drsmith, clinic_lab, clinic_locum
clinic.patients.first_name
table · text
person's name
medium confidence
the column name suggests person's nameclinic_auditor, clinic_drsmith, clinic_frontdesk, clinic_locum
clinic.patients.last_name
table · text
person's name
medium confidence
the column name suggests person's nameclinic_auditor, clinic_drsmith, clinic_frontdesk, clinic_locum
clinic.patients.date_of_birth
table · date
date of birth
high confidence
30 of 30 sampled values are plausible dates of birthclinic_auditor, clinic_drsmith, clinic_locum
clinic.patients.national_id
table · text
national ID number
high confidence
30 of 30 sampled values are national ID numbers (es-dni)clinic_auditor, clinic_drsmith, clinic_locum
clinic.patients.email
table · text
email address
high confidence
30 of 30 sampled values are email addressesclinic_auditor, clinic_billing, clinic_drsmith, clinic_locum
clinic.patients.phone
table · text
phone number
high confidence
30 of 30 sampled values are phone numbersclinic_auditor, clinic_drsmith, clinic_frontdesk, clinic_locum
clinic.patients.home_address
table · text
postal address
medium confidence
the column name suggests postal addressclinic_auditor, clinic_drsmith, clinic_locum
clinic.patients.insurance_number
table · text
insurance number
medium confidence
the column name suggests insurance numberclinic_auditor, clinic_billing, clinic_drsmith, clinic_locum
clinic.visits.diagnosis
partitioned table · text
health data
medium confidence
the column name suggests health data; found in partitions clinic.visits_2025 and clinic.visits_2026clinic_auditor, clinic_drsmith, clinic_locum
clinic.visits_2025.diagnosis
partition · text
health data
medium confidence
the column name suggests health dataclinic_research
clinic.visits_2026.diagnosis
partition · text
health data
medium confidence
the column name suggests health datano audited login

Logins

clinic_auditor

member of clinic_doctor (no set) · no password set

Personal data it can readHoldsHow
clinic.lab_results.payload
table
email addressperson's name
  • granted to clinic_doctor
clinic.patients.first_name
table
person's name
  • granted to clinic_doctor
clinic.patients.last_name
table
person's name
  • granted to clinic_doctor
clinic.patients.date_of_birth
table
date of birth
  • granted to clinic_doctor
clinic.patients.national_id
table
national ID number
  • granted to clinic_doctor
clinic.patients.email
table
email address
  • granted to clinic_doctor
clinic.patients.phone
table
phone number
  • granted to clinic_doctor
clinic.patients.home_address
table
postal address
  • granted to clinic_doctor
clinic.patients.insurance_number
table
insurance number
  • granted to clinic_doctor
clinic.visits.diagnosis
partitioned table
health data
  • granted to clinic_doctor
11 other columns it can read
ColumnHow
clinic.appointments.id
  • granted to clinic_staff through clinic_doctor
clinic.appointments.patient_id
  • granted to clinic_staff through clinic_doctor
clinic.appointments.starts_at
  • granted to clinic_staff through clinic_doctor
clinic.appointments.room
  • granted to clinic_staff through clinic_doctor
clinic.lab_results.id
  • granted to clinic_doctor
clinic.lab_results.patient_id
  • granted to clinic_doctor
clinic.patients.id
  • granted to clinic_doctor
clinic.visits.id
  • granted to clinic_doctor
clinic.visits.patient_id
  • granted to clinic_doctor
clinic.visits.visit_date
  • granted to clinic_doctor
clinic.visits.notes
  • granted to clinic_doctor

clinic_billing

member of no role · no password set

Personal data it can readHoldsHow
clinic.patients.email
table
email address
  • column granted to clinic_billing with grant option
clinic.patients.insurance_number
table
insurance number
  • column granted to clinic_billing with grant option
1 other column it can read
ColumnHow
clinic.patients.id
  • column granted to clinic_billing with grant option

clinic_drsmith

member of clinic_doctor · no password set

Personal data it can readHoldsHow
clinic.lab_results.payload
table
email addressperson's name
  • granted to clinic_doctor
clinic.patients.first_name
table
person's name
  • granted to clinic_doctor
clinic.patients.last_name
table
person's name
  • granted to clinic_doctor
clinic.patients.date_of_birth
table
date of birth
  • granted to clinic_doctor
clinic.patients.national_id
table
national ID number
  • granted to clinic_doctor
clinic.patients.email
table
email address
  • granted to clinic_doctor
clinic.patients.phone
table
phone number
  • granted to clinic_doctor
clinic.patients.home_address
table
postal address
  • granted to clinic_doctor
clinic.patients.insurance_number
table
insurance number
  • granted to clinic_doctor
clinic.visits.diagnosis
partitioned table
health data
  • granted to clinic_doctor
11 other columns it can read
ColumnHow
clinic.appointments.id
  • granted to clinic_staff through clinic_doctor
clinic.appointments.patient_id
  • granted to clinic_staff through clinic_doctor
clinic.appointments.starts_at
  • granted to clinic_staff through clinic_doctor
clinic.appointments.room
  • granted to clinic_staff through clinic_doctor
clinic.lab_results.id
  • granted to clinic_doctor
clinic.lab_results.patient_id
  • granted to clinic_doctor
clinic.patients.id
  • granted to clinic_doctor
clinic.visits.id
  • granted to clinic_doctor
clinic.visits.patient_id
  • granted to clinic_doctor
clinic.visits.visit_date
  • granted to clinic_doctor
clinic.visits.notes
  • granted to clinic_doctor

clinic_frontdesk

member of clinic_staff · no password set

Personal data it can readHoldsHow
clinic.patients.first_name
table
person's name
  • column granted to clinic_frontdesk
clinic.patients.last_name
table
person's name
  • column granted to clinic_frontdesk
clinic.patients.phone
table
phone number
  • column granted to clinic_frontdesk
5 other columns it can read
ColumnHow
clinic.appointments.id
  • granted to clinic_staff
clinic.appointments.patient_id
  • granted to clinic_staff
clinic.appointments.starts_at
  • granted to clinic_staff
clinic.appointments.room
  • granted to clinic_staff
clinic.patients.id
  • column granted to clinic_frontdesk

clinic_hr

member of clinic_doctor (no inherit, no set, admin) · no password set

Reads no personal data.

clinic_it

createrole · member of pg_read_server_files · no password set

Reads no personal data.

Functions that run with someone else's rights (1)
FunctionRuns asHow
pg_read_file(text)server files
  • granted to clinic_it

On the database server it can: read files (may run pg_read_file(text), member of pg_read_server_files).

clinic_lab

member of no role · password stored as an MD5 hash

Personal data it can readHoldsHow
clinic.lab_results.payload
table
email addressperson's name
  • granted to clinic_lab
2 other columns it can read
ColumnHow
clinic.lab_results.id
  • granted to clinic_lab
clinic.lab_results.patient_id
  • granted to clinic_lab

Foreign servers it can use: clinic_lab_server (clinic_fdw).

clinic_locum

member of clinic_doctor (no inherit) · can SET ROLE clinic_doctor, clinic_staff · no password set

Personal data it can readHoldsHow
clinic.lab_results.payload
table
email addressperson's name
  • after SET ROLE clinic_doctor: granted to clinic_doctor
clinic.patients.first_name
table
person's name
  • after SET ROLE clinic_doctor: granted to clinic_doctor
clinic.patients.last_name
table
person's name
  • after SET ROLE clinic_doctor: granted to clinic_doctor
clinic.patients.date_of_birth
table
date of birth
  • after SET ROLE clinic_doctor: granted to clinic_doctor
clinic.patients.national_id
table
national ID number
  • after SET ROLE clinic_doctor: granted to clinic_doctor
clinic.patients.email
table
email address
  • after SET ROLE clinic_doctor: granted to clinic_doctor
clinic.patients.phone
table
phone number
  • after SET ROLE clinic_doctor: granted to clinic_doctor
clinic.patients.home_address
table
postal address
  • after SET ROLE clinic_doctor: granted to clinic_doctor
clinic.patients.insurance_number
table
insurance number
  • after SET ROLE clinic_doctor: granted to clinic_doctor
clinic.visits.diagnosis
partitioned table
health data
  • after SET ROLE clinic_doctor: granted to clinic_doctor
11 other columns it can read
ColumnHow
clinic.appointments.id
  • after SET ROLE clinic_doctor: granted to clinic_staff
  • after SET ROLE clinic_staff: granted to clinic_staff
clinic.appointments.patient_id
  • after SET ROLE clinic_doctor: granted to clinic_staff
  • after SET ROLE clinic_staff: granted to clinic_staff
clinic.appointments.starts_at
  • after SET ROLE clinic_doctor: granted to clinic_staff
  • after SET ROLE clinic_staff: granted to clinic_staff
clinic.appointments.room
  • after SET ROLE clinic_doctor: granted to clinic_staff
  • after SET ROLE clinic_staff: granted to clinic_staff
clinic.lab_results.id
  • after SET ROLE clinic_doctor: granted to clinic_doctor
clinic.lab_results.patient_id
  • after SET ROLE clinic_doctor: granted to clinic_doctor
clinic.patients.id
  • after SET ROLE clinic_doctor: granted to clinic_doctor
clinic.visits.id
  • after SET ROLE clinic_doctor: granted to clinic_doctor
clinic.visits.patient_id
  • after SET ROLE clinic_doctor: granted to clinic_doctor
clinic.visits.visit_date
  • after SET ROLE clinic_doctor: granted to clinic_doctor
clinic.visits.notes
  • after SET ROLE clinic_doctor: granted to clinic_doctor

clinic_research

member of no role · no password set

Personal data it can readHoldsHow
clinic.diagnosis_stats.diagnosis
materialized view
health data
  • granted to clinic_research
clinic.visits_2025.diagnosis
partition
health data
  • granted to clinic_research
5 other columns it can read
ColumnHow
clinic.diagnosis_stats.visits
  • granted to clinic_research
clinic.visits_2025.id
  • granted to clinic_research
clinic.visits_2025.patient_id
  • granted to clinic_research
clinic.visits_2025.visit_date
  • granted to clinic_research
clinic.visits_2025.notes
  • granted to clinic_research

Everyone (PUBLIC)

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

No personal data is open to every role.

Method

Schemas audited: clinic, public. Connection: postgres://[email protected]:5432/baresql_fx_clinic, 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.