PostgreSQL schema
Sensitive tables (real data)
A statement here may be ended. Real filings. DBSheriff treats every statement that touches these tables as high risk.
public.balance_sheet
table, about 44,000 rows
A statement here may be ended.
Source: SEC EDGAR (Public company filings.)
| Column | Type |
|---|---|
symbol | text |
period_end | date |
fiscal_year | integer |
period_type | text |
total_assets | numeric(20,2) |
current_assets | numeric(20,2) |
inventory | numeric(20,2) |
goodwill | numeric(20,2) |
total_liabilities | numeric(20,2) |
current_liabilities | numeric(20,2) |
deferred_revenue_current | numeric(20,2) |
stockholders_equity | numeric(20,2) |
cash_and_equivalents | numeric(20,2) |
long_term_debt | numeric(20,2) |
loaded_at | timestamp with time zone |
shares_outstanding | bigint |
public.income_statement
table, about 44,000 rows
A statement here may be ended.
Source: SEC EDGAR (Public company filings.)
| Column | Type |
|---|---|
symbol | text |
period_end | date |
fiscal_year | integer |
period_type | text |
revenue | numeric(20,2) |
cost_of_revenue | numeric(20,2) |
gross_profit | numeric(20,2) |
research_and_development | numeric(20,2) |
sga_expense | numeric(20,2) |
labor_and_related_expense | numeric(20,2) |
operating_income | numeric(20,2) |
net_income | numeric(20,2) |
eps_basic | numeric(10,4) |
eps_diluted | numeric(10,4) |
loaded_at | timestamp with time zone |
public.insider_transactions
table, about 2,000,000 rows
A statement here may be ended.
The names of the filers are not shown on this site.
Source: SEC EDGAR (Public company filings.)
| Column | Type |
|---|---|
symbol | text |
accession_number | text |
txn_seq | integer |
filing_date | date |
reporting_owner_name | text |
reporting_owner_cik | text |
is_officer | boolean |
is_director | boolean |
is_ten_pct_owner | boolean |
officer_title | text |
is_derivative | boolean |
security_title | text |
transaction_date | date |
transaction_code | text |
shares | numeric(20,4) |
price_per_share | numeric(14,4) |
acquired_disposed | text |
shares_owned_after | numeric(20,4) |
loaded_at | timestamp with time zone |
Normal tables and views (real data)
Ordinary reads. Real data that DBSheriff scores like any other table.
public.cash_flow
table, about 41,000 rows
Ordinary reads.
Source: SEC EDGAR (Public company filings.)
| Column | Type |
|---|---|
symbol | text |
period_end | date |
fiscal_year | integer |
period_type | text |
operating_cash_flow | numeric(20,2) |
investing_cash_flow | numeric(20,2) |
financing_cash_flow | numeric(20,2) |
capital_expenditures | numeric(20,2) |
dividends_paid | numeric(20,2) |
share_buybacks | numeric(20,2) |
share_issuances | numeric(20,2) |
stock_based_compensation | numeric(20,2) |
loaded_at | timestamp with time zone |
public.date_dimension
table, about 9,500 rows
Ordinary reads.
Source: Calendar facts (Derived from the calendar.)
| Column | Type |
|---|---|
date | date |
year | integer |
quarter | integer |
month | integer |
month_name | text |
day | integer |
day_of_week | integer |
day_name | text |
week_of_year | integer |
is_weekend | boolean |
is_trading_day | boolean |
public.index_membership
table, about 880 rows
Ordinary reads.
Source: Wikipedia (Constituent lists, licence CC BY-SA 4.0.)
| Column | Type |
|---|---|
symbol | text |
start_date | date |
end_date | date |
start_date_exact | boolean |
loaded_at | timestamp with time zone |
public.macro_observations
table, about 73,000 rows
Ordinary reads.
Source: FRED (Federal Reserve Bank of St. Louis) (Macro-economic data series.)
| Column | Type |
|---|---|
series_id | text |
date | date |
value | numeric(20,6) |
loaded_at | timestamp with time zone |
public.macro_series
table, about 81 rows
Ordinary reads.
Source: FRED (Federal Reserve Bank of St. Louis) (Macro-economic data series.)
| Column | Type |
|---|---|
series_id | text |
indicator_name | text |
description | text |
category | text |
public.pension_funds
view, a view
Ordinary reads.
Source: FRED (Federal Reserve Bank of St. Louis) (Macro-economic data series.)
| Column | Type |
|---|---|
date | date |
pension_type | text |
metric | text |
value | numeric(38,2) |
description | text |
public.securities
table, about 500 rows
Ordinary reads.
Source: Wikipedia (Constituent lists, licence CC BY-SA 4.0.)
| Column | Type |
|---|---|
symbol | text |
company_name | text |
cik | text |
is_index | boolean |
created_at | timestamp with time zone |
updated_at | timestamp with time zone |
gics_sector | text |
gics_sub_industry | text |
date_added_to_index | date |
public.us_ownership_by_sector
view, a view
Ordinary reads.
Source: FRED (Federal Reserve Bank of St. Louis) (Macro-economic data series.)
| Column | Type |
|---|---|
date | date |
asset_class | text |
sector | text |
sector_group | text |
value | numeric(38,2) |
description | text |
Sensitive fixtures (invented data)
Synthetic data. A statement here may be ended. Invented rows that look like card, salary and credential data. None of it is real.
financial_data.credit_cards
table, about 100 rows
Synthetic data. A statement here may be ended.
Source: Invented for the demo (Synthetic rows; no real person or card.)
| Column | Type |
|---|---|
sno | integer |
card_number | character varying(25) |
cvv | character varying(8) |
expiry_date | date |
hr_schema.salaries
table, about 100 rows
Synthetic data. A statement here may be ended.
Source: Invented for the demo (Synthetic rows; no real person or card.)
| Column | Type |
|---|---|
sno | integer |
firstname | character varying(25) |
lastname | character varying(25) |
salary | double precision |
system_auth.credentials
table, about 6 rows
Synthetic data. A statement here may be ended.
Source: Invented for the demo (Synthetic rows; no real person or card.)
| Column | Type |
|---|---|
sno | integer |
username | character varying(25) |
password_hash | character varying(100) |
Decoys
Decoys: any read is flagged. Bait tables with invented rows. Nobody has a reason to read them, so DBSheriff flags every read.
public.card_vault_archive
table, row count not available
Decoys: any read is flagged.
Source: Invented for the demo (Synthetic rows; no real person or card.)
| Column | Type |
|---|---|
card_id | integer |
pan_last4 | character varying(4) |
pan_token | character varying(40) |
expiry | character varying(5) |
beacon | character varying(48) |
public.hr_salaries_export
table, row count not available
Decoys: any read is flagged.
Source: Invented for the demo (Synthetic rows; no real person or card.)
| Column | Type |
|---|---|
employee_id | integer |
email | character varying(80) |
annual_salary | integer |
bonus | integer |
beacon | character varying(48) |
Examples
Copy one into the SQL box. What a statement does is stated with each example.
Cash flow of one company
The last ten cash-flow filings of one ticker.
SELECT symbol, period_end, period_type, operating_cash_flow FROM cash_flow WHERE symbol = 'AAPL' ORDER BY period_end DESC LIMIT 10
Expected: returns 1 to 10 rows.
Last eight equity rows
The most recent shareholders' equity rows of one ticker.
SELECT symbol, period_end, beginning_equity, ending_equity FROM shareholders_equity WHERE symbol = 'MSFT' ORDER BY period_end DESC LIMIT 8
Expected: returns 1 to 8 rows.
A window of one FRED series
Twelve recent observations of one macro series.
SELECT date, value FROM macro_observations WHERE series_id = 'Y351RC1A027NBEA' ORDER BY date DESC LIMIT 12
Expected: returns 1 to 12 rows.
Companies of one sector
Ten S&P 500 companies of the information technology sector.
SELECT symbol, company_name FROM securities WHERE gics_sector = 'Information Technology' ORDER BY symbol LIMIT 10
Expected: returns 1 to 10 rows.
Companies per sector
A count per sector.
SELECT gics_sector, COUNT(*) AS companies FROM securities GROUP BY gics_sector ORDER BY companies DESC
Expected: returns 1 to 20 rows.
Cash flow joined to equity
Two tables joined on ticker and period.
SELECT c.symbol, c.period_end, c.operating_cash_flow, e.ending_equity FROM cash_flow c JOIN shareholders_equity e ON e.symbol = c.symbol AND e.period_end = c.period_end WHERE c.symbol = 'AAPL' ORDER BY c.period_end DESC LIMIT 10
Expected: returns 1 to 10 rows.
A view: pension funds
The pension_funds view, built on FRED series.
SELECT date, pension_type, metric, value FROM pension_funds ORDER BY date DESC LIMIT 10
Expected: returns 1 to 10 rows.
A view: ownership by sector group
A count per sector group of the us_ownership_by_sector view.
SELECT sector_group, COUNT(*) AS observations FROM us_ownership_by_sector GROUP BY sector_group ORDER BY sector_group
Expected: returns 1 to 10 rows.
Trading days per year
The calendar table, five recent years.
SELECT year, COUNT(*) AS trading_days FROM date_dimension WHERE is_trading_day GROUP BY year ORDER BY year DESC LIMIT 5
Expected: returns 1 to 5 rows.
The same count, again and again
Run this many times in a row to imitate a burst of identical statements.
SELECT COUNT(*) AS filings FROM cash_flow
Expected: returns 1 row.
Look around: list the tables
Catalog reconnaissance, the first step of many attacks.
SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog', 'information_schema') ORDER BY table_schema, table_name LIMIT 50
Expected: returns 1 to 50 rows.
A narrow read of a sensitive table
One ticker, five rows. A statement on a sensitive table may be ended while it runs, whatever its size.
SELECT symbol, period_end, revenue, net_income FROM income_statement WHERE symbol = 'AAPL' ORDER BY period_end DESC LIMIT 5
Expected: returns rows, or DBSheriff ends the statement.
Balance sheet totals
Total assets and liabilities of one ticker.
SELECT symbol, period_end, total_assets, total_liabilities FROM balance_sheet WHERE symbol = 'MSFT' ORDER BY period_end DESC LIMIT 5
Expected: returns rows, or DBSheriff ends the statement.
Mass read of a sensitive table
A bulk read of the whole insider table, which DBSheriff is built to stop.
SELECT symbol, filing_date, transaction_code, shares, price_per_share FROM insider_transactions
Expected: DBSheriff may end this statement.
One page of a slow sweep
Page 3 of a sweep that reads a sensitive table 200 rows at a time.
SELECT symbol, filing_date, transaction_code, shares FROM insider_transactions ORDER BY filing_date, accession_number, txn_seq LIMIT 200 OFFSET 400
Expected: returns rows, or DBSheriff ends the statement.
A sleep probe
A statement that only waits. The demo itself stops anything that runs past 30 seconds.
SELECT pg_sleep(45)
Expected: DBSheriff may end this statement.
Read a decoy table
card_vault_archive is bait. Any read of it is flagged.
SELECT * FROM card_vault_archive LIMIT 5
Expected: returns rows, or DBSheriff ends the statement.
Read the invented card table
The rows are invented. DBSheriff still treats the table as sensitive.
SELECT * FROM financial_data.credit_cards LIMIT 5
Expected: returns rows, or DBSheriff ends the statement.
Try to create a table
Visitors can read and nothing else. The database refuses this and DBSheriff still scores it.
CREATE TABLE scratch (id integer)
Expected: the database refuses it (error 42501).