Public sector cyber security: agency logs and records in one SQL engine
Public sector cyber security protects government agencies' systems and the records they keep. For the team that runs it, government cybersecurity is a set of questions: who signed in from where, which systems must be patched by when, which records answer a request and who may read them. SereneDB answers them in one Postgres-compatible engine, where sign-in logs, scan findings and records sit side by side in SQL.
curl https://install.serenedb.com | shWhat does public sector cyber security ask of its data? Five questions about two days at an agency
Public sector cyber security protects government agencies' systems and the records they keep, and much of the work is questions asked of data: who signed in from where, what an intruder read, which systems must be patched by when, which records answer a request. Below are two simulated days at an agency, with a password spray in the second night. Pick a question to run it; each is one SQL statement.
SELECT src_ip, count(*) AS attempts, count(*) FILTER (WHERE result = 'failure') AS failures, count(DISTINCT user_name) AS accounts, count(*) FILTER (WHERE result = 'success') AS successes FROM sign_ins WHERE app = 'vpn' GROUP BY src_ip ORDER BY accounts DESC, failures DESC, src_ip LIMIT 3;
| src_ip | attempts | failures | accounts | successes |
|---|---|---|---|---|
| 203.0.113.77 | 48 | 47 | 48 | 1 |
| 198.51.100.17 | 2 | 1 | 1 | 1 |
| 198.51.100.18 | 3 | 1 | 1 | 2 |
SELECT ts, src_ip, app, method, result FROM sign_ins_idx WHERE user_name = 'emp0207' ORDER BY ts;
| ts | src_ip | app | method | result |
|---|---|---|---|---|
| 2026-11-05 08:19:00 | 198.51.100.17 | vpn | saml_mfa | success |
| 2026-11-05 08:23:00 | 198.51.100.17 | records | saml_mfa | success |
| 2026-11-06 02:13:48 | 203.0.113.77 | vpn | password | success |
| 2026-11-06 02:44:05 | 203.0.113.77 | records | password | success |
| 2026-11-06 07:52:30 | 198.51.100.17 | vpn | saml_mfa | failure |
SELECT r.office, count(*) AS records, count(*) FILTER (WHERE r.exemption = 'b6') AS personal_info, min(v.ts)::TIME AS first, max(v.ts)::TIME AS last FROM record_views v JOIN records r USING (doc_id) WHERE v.user_name = 'emp0207' AND v.ts >= TIMESTAMP '2026-11-06 02:44:05' GROUP BY r.office ORDER BY records DESC;
| office | records | personal_info | first | last |
|---|---|---|---|---|
| Permits | 19 | 9 | 02:46:00 | 03:11:12 |
| Human Resources | 4 | 4 | 03:12:36 | 03:16:48 |
WITH kev AS ( SELECT "cveID" AS cve_id, "dateAdded" AS date_added FROM read_csv('https://www.cisa.gov/sites/default/files/csv/known_exploited_vulnerabilities.csv') ) SELECT f.asset, f.cve_id, k.cve_id IS NOT NULL AS in_kev, f.technical_impact = 'total' AS total, CASE WHEN in_kev AND (f.public AND (f.automatable OR total) OR f.automatable AND total) THEN 3 WHEN in_kev THEN 14 WHEN f.public AND f.automatable AND total THEN 3 WHEN f.public AND (f.automatable OR total) THEN 14 WHEN f.public OR f.automatable THEN 60 END AS days, in_kev AND total AND (f.public OR f.automatable) AS triage, least(k.date_added, f.first_seen) + days AS due FROM findings f LEFT JOIN kev k USING (cve_id) ORDER BY due NULLS LAST;
| asset | cve_id | in_kev | total | days | triage | due |
|---|---|---|---|---|---|---|
| records-legacy-01 | CVE-2025-53770 | t | t | 3 | t | 2025-07-23 |
| vpn-gw-02 | CVE-2026-118527 | t | f | 14 | f | 2026-11-13 |
| bastion-01 | CVE-2024-6387 | f | t | 14 | f | 2026-11-16 |
| vpn-gw-01 | CVE-2026-19868 | t | f | 14 | f | 2026-11-20 |
| wiki-01 | CVE-2023-48795 | f | f | f |
SELECT doc_id, created, doc_type, title, exemption FROM records_idx WHERE (body @@ ts_levenshtein('seawall', 1) OR body @@ ts_phrase('pier 9')) AND created BETWEEN DATE '2026-01-01' AND DATE '2026-11-05' ORDER BY created;
| doc_id | created | doc_type | title | exemption |
|---|---|---|---|---|
| R-0112 | 2026-01-14 | contract | Contract HF-26-014: Pier 9 seawall repair | |
| R-0131 | 2026-02-03 | Re: seawall repair schedule | ||
| R-0178 | 2026-03-19 | memo | Change order 1: Pier 9 seawall repair | |
| R-0203 | 2026-04-07 | report | Inspection report, week 5 | |
| R-0244 | 2026-05-12 | complaint | Noise complaint, Harbor Road | b6 |
| R-0251 | 2026-05-20 | Pile driving hours | ||
| R-0302 | 2026-07-02 | memo | Final acceptance: Pier 9 seawall repair | |
| R-0315 | 2026-07-21 | personnel | Personnel action: project engineer reassignment | b6 |
What public sector cyber security needs from its database, and how SereneDB does it in SQL
An agency's security and records teams ask the same data layer different questions: who signed in from where, which systems must be patched by when, which records answer a request and who may read them. These are the jobs, each with the SereneDB feature that does it and its docs.
| Need | How SereneDB does it | Looks like |
|---|---|---|
| Take in sign-in logs | The identity provider's and the VPN's logs arrive over the PostgreSQL protocol with COPY or INSERT. A collector can also send OpenTelemetry logs over OTLP into SereneDB's log tables. Docs → | COPY sign_ins FROM STDIN |
| Keep identity logs for years | OMB M-21-31 sets 12 months in active storage and 18 months in cold storage for federal agencies' identity logs. Recent months stay in a table; a job copies older rows to Parquet, which read_parquet still queries. Docs → | (FORMAT parquet, PARTITION_BY (day), APPEND) |
| Spot password spraying | One source trying many accounts is a count of distinct accounts per IP address, and FILTER splits failures from successes in the same pass. Docs → | count(DISTINCT user_name) AS accounts |
| Search log text | An inverted index over the messages: words ranked by BM25, and accounts, apps and IP addresses as exact terms. Docs → | message @@ 'failed' |
| Check scans against CISA's KEV catalog | read_csv reads the catalog's CSV from cisa.gov over HTTPS, so the scan joins the current list with no import step. Docs → | read_csv('https://www.cisa.gov/…/known_exploited_vulnerabilities.csv') |
| Patch deadlines as rules | BOD 26-04 sets each fix's deadline from four answers. A CASE expression encodes its table, and the date math gives the due date. Docs → | WHEN in_kev THEN 14 |
| Search records | Phrases, matches within an edit distance for typos, and date ranges over titles and bodies, in one WHERE clause. Docs → | body @@ ts_phrase('pier 9') |
| Scope a breach | The records system's access log joins the records, so who opened what, and how many carried personal information, is one query. Docs → | count(*) FILTER (WHERE r.exemption = 'b6') |
| Limit who sees what | PostgreSQL roles and grants: a column grant shows an analyst which records were read without their text, and a view gives a portal role released records only. Docs → | GRANT SELECT (doc_id, office, doc_type, title, exemption) ON records |
| Answer records requests on time | A working-day calendar is a series of dates without weekends and holidays, so a request's due date is the twentieth row. Docs → | isodow(d) < 6 AND d::DATE NOT IN (SELECT d FROM holidays) |
Architecture: sign-in logs, records and scan findings in one engine
Sign-ins arrive over the PostgreSQL protocol, records and scan findings land in tables beside them, inverted indexes cover their text, accounts and times, and every question is a SELECT: a count per address, a search, a join to CISA's catalog. Grants decide who reads what, and a job moves old sign-ins to Parquet. Hover a stage, or a lane inside the index.
Sign-ins arrive over the PostgreSQL protocol
The identity provider's and the VPN's logs are shipped with COPY or INSERT from any PostgreSQL client library, one sign-in per row. A collector can also send OpenTelemetry logs over OTLP into SereneDB's log tables.
Records, reads and scan findings as rows
The records system's documents and access log, and the scanner's export, arrive as INSERTs. A records system that keeps its own PostgreSQL database can stay there: ATTACH reads it in place, as on the zero-ETL search page. Each finding carries CISA's SSVC answers for its CVE.
The sign-in log, words and exact terms
Messages go through the agency_text dictionary: lowercased words. Accounts, apps and IP addresses are indexed whole, so a query for one address or one account is an exact match.
Records, and who opened them
Each record with its office, type, dates, text and exemption code; record_views logs every read. A count per type reads the term dictionary, and a phrase matches words in order.
Scan findings next to CISA's catalog
A view over read_csv makes the KEV catalog a table: queries read the CSV from cisa.gov, with no import step, and join it to the scan like any other table.
What is searched and filtered is indexed
One inverted index per searched table: text through a dictionary, accounts, addresses and exemption codes as exact terms, times and dates as ranges. New rows become searchable at the next refresh, every second by default, or at once after VACUUM (REFRESH_TABLE).
Sign-ins over time, per address
time_bucket groups sign-ins into windows, and count(DISTINCT user_name) shows one address trying 16 accounts in each 10 minutes of the spray.
Search records, ranked
Words in a query match any record that has them, and BM25 ranks the records that fit best first. ts_levenshtein matches within an edit distance, for the typos in a complaint or an email.
BOD 26-04's table as a CASE expression
BOD 26-04 sets a deadline from four answers: exposed to the internet, in the KEV catalog, automatable by an adversary, total or partial control. The CASE encodes its table, and the clock starts at the KEV entry or the first sighting, whichever came first.
Columns for the analyst, not the text
A role per person or application. The security analyst's column grant shows which records were opened and their exemption codes; reading a record's body fails.
A portal role that reads released records only
A view over the index relation keeps the released records, and the portal's role is granted the view alone. Full-text search runs through it.
Old sign-ins to Parquet, by a job
COPY … TO with PARTITION_BY writes one folder of Parquet files per day, and read_parquet queries them in place; with an S3 secret the same COPY writes to a bucket. A job does it daily and deletes what it moved.
What an agency's security data needs from one engine
sign_ins · records · findingsA spray is one address with many distinct accounts: 48 here, against one for each staff address.
count(DISTINCT user_name)BOD 26-04's table is a CASE expression, and the due date is date arithmetic over the KEV entry.
least(k.date_added, f.first_seen) + daysA complaint that spells it seawal still answers a request for seawall records.
ts_levenshtein('seawall', 1)SearchBench, 92 queries over 1B OpenTelemetry log rows: all 92 under the 60 s cap, median 31.0 ms.
otel_logs_1b · 26.09.1Tutorial: find a password spray, scope the breach and set patch deadlines in SQL, in five steps
From an empty server to the records request: start SereneDB, create the tables, load two days at an agency, find the spray and what the stolen session read, then set patch deadlines from CISA's catalog and search the records.
docker run -d --name serenedb -p 7890:7890 \ -e POSTGRES_PASSWORD=<password> serenedb/serenedb:26.11.2 psql -h localhost -p 7890 -U postgres
CREATE TABLE sign_ins ( ts TIMESTAMP, user_name VARCHAR, src_ip VARCHAR, app VARCHAR, -- vpn or records method VARCHAR, -- saml_mfa or password result VARCHAR, -- success or failure message VARCHAR ); CREATE TABLE records ( doc_id VARCHAR, created DATE, office VARCHAR, doc_type VARCHAR, -- contract, email, memo, report, complaint, permit, personnel title VARCHAR, body VARCHAR, exemption VARCHAR, -- b6 when it holds personal information released BOOLEAN -- already public ); CREATE TABLE record_views ( ts TIMESTAMP, user_name VARCHAR, doc_id VARCHAR ); CREATE TABLE findings ( asset VARCHAR, cve_id VARCHAR, first_seen DATE, public BOOLEAN, -- reachable from the internet automatable BOOLEAN, -- CISA's SSVC answer for the CVE technical_impact VARCHAR -- total or partial, also SSVC ); CREATE TEXT SEARCH DICTIONARY agency_text AS split_text(case := 'lower') WITH (frequency, position, norm); CREATE INDEX sign_ins_idx ON sign_ins USING inverted (ts, user_name, src_ip, app, result, message agency_text); CREATE INDEX records_idx ON records USING inverted (doc_id, created, office, doc_type, title agency_text, body agency_text, exemption, released);
-- Two days at the agency, 2026-11-05 and 2026-11-06. 48 staff accounts, -- emp0201 to emp0248, sign in to the VPN each morning with SAML and MFA, -- each from the address it always uses; the 30 records staff then open the -- records system. At 02:10 on the 6th, 203.0.113.77 tries one password on -- every account, 38 seconds apart. INSERT INTO sign_ins SELECT TIMESTAMP '2026-11-05 07:00:00' + d * INTERVAL '1 day' + ((k * 97) % 150) * INTERVAL '1 minute' + a * INTERVAL '4 minutes', 'emp0' || (200 + k), '198.51.100.' || (10 + k), CASE WHEN a = 0 THEN 'vpn' ELSE 'records' END, 'saml_mfa', 'success', 'Login succeeded' FROM generate_series(1, 48) AS u(k), generate_series(0, 1) AS dd(d), generate_series(0, 1) AS aa(a) WHERE (a = 0 OR k <= 30) AND NOT (k = 7 AND d = 1) UNION ALL SELECT TIMESTAMP '2026-11-05 07:00:00' + d * INTERVAL '1 day' + ((k * 97) % 150) * INTERVAL '1 minute' - INTERVAL '40 seconds', 'emp0' || (200 + k), '198.51.100.' || (10 + k), 'vpn', 'saml_mfa', 'failure', 'Login failed: MFA code rejected' FROM generate_series(1, 48) AS u(k), generate_series(0, 1) AS dd(d) WHERE (k + d) % 9 = 0 UNION ALL SELECT TIMESTAMP '2026-11-06 02:10:00' + (k - 1) * INTERVAL '38 seconds', 'emp0' || (200 + k), '203.0.113.77', 'vpn', 'password', CASE WHEN k = 7 THEN 'success' ELSE 'failure' END, CASE WHEN k = 7 THEN 'Login succeeded with local password' ELSE 'Login failed: invalid password' END FROM generate_series(1, 48) AS u(k); INSERT INTO sign_ins VALUES ('2026-11-06 02:44:05', 'emp0207', '203.0.113.77', 'records', 'password', 'success', 'Login succeeded with local password'), ('2026-11-06 07:52:30', 'emp0207', '198.51.100.17', 'vpn', 'saml_mfa', 'failure', 'Login failed: account disabled'); INSERT INTO records VALUES ('R-0089', '2025-11-20', 'Facilities', 'report', 'Seawall condition survey 2025', 'Annual survey of the Pier 9 seawall: corroded tie rods along 60 feet, repair recommended within a year.', NULL, true), ('R-0112', '2026-01-14', 'Facilities', 'contract', 'Contract HF-26-014: Pier 9 seawall repair', 'Firm-fixed-price contract with Coastline Marine Builders for the seawall repair at Pier 9: 180 feet of steel sheet pile and new tie rods, $1,240,000.', NULL, true), ('R-0131', '2026-02-03', 'Facilities', 'email', 'Re: seawall repair schedule', 'Coastline Marine Builders can mobilize on March 2. Pier 9 stays closed to boats during the seawall repair.', NULL, false), ('R-0140', '2026-02-11', 'Facilities', 'contract', 'Parking lot repair, Building 4', 'Asphalt patching and restriping of the Building 4 parking lot, $38,500.', NULL, true), ('R-0178', '2026-03-19', 'Facilities', 'memo', 'Change order 1: Pier 9 seawall repair', 'The inspection found 40 more feet of corroded tie rods. Change order 1 adds them to the seawall repair, $86,400.', NULL, false), ('R-0203', '2026-04-07', 'Facilities', 'report', 'Inspection report, week 5', 'Sheet pile driven to depth along 120 feet; tie rods replaced on the north section of the seawall.', NULL, false), ('R-0244', '2026-05-12', 'Clerk', 'complaint', 'Noise complaint, Harbor Road', 'Resident at 14 Harbor Road, phone 555-0142, reports pile driving before 7 am during the seawal repair.', 'b6', false), ('R-0251', '2026-05-20', 'Facilities', 'email', 'Pile driving hours', 'After a resident complaint, Coastline Marine Builders will start pile driving at Pier 9 no earlier than 8 am.', NULL, false), ('R-0302', '2026-07-02', 'Facilities', 'memo', 'Final acceptance: Pier 9 seawall repair', 'The seawall repair passed final inspection on June 30. Retainage of $62,000 released to Coastline Marine Builders.', NULL, false), ('R-0315', '2026-07-21', 'Human Resources', 'personnel', 'Personnel action: project engineer reassignment', 'A project engineer, home address on file, moves from the Pier 9 seawall repair to the Building 4 roof project.', 'b6', false), ('R-0330', '2026-08-04', 'Human Resources', 'personnel', 'Personnel action: promotion, permits office', 'Promotion of a permits clerk to permits specialist, GS-9 to GS-11. Home address and date of birth on file.', 'b6', false), ('R-0341', '2026-08-19', 'Human Resources', 'personnel', 'Medical leave approval', 'Six weeks of medical leave approved for an inspector; medical certification attached.', 'b6', false), ('R-0356', '2026-09-08', 'Human Resources', 'personnel', 'Background investigation summary', 'Background investigation of a new facilities engineer, with prior addresses and references.', 'b6', false), ('R-0372', '2026-09-22', 'Human Resources', 'personnel', 'Performance improvement plan', 'A 90-day performance improvement plan for an IT specialist, with the employee''s written response.', 'b6', false); -- 24 building permits: a homeowner's application carries a phone number. INSERT INTO records SELECT 'BP-26-' || (100 + n), DATE '2026-06-01' + n * 5, 'Permits', 'permit', 'Building permit BP-26-' || (100 + n) || ', ' || (10 + n * 4) || ' Harbor Road', CASE WHEN n % 2 = 1 THEN 'Deck addition. Applicant: the homeowner, phone 555-01' || (10 + n) || '.' ELSE 'Storefront renovation. Applicant: a licensed contractor.' END, CASE WHEN n % 2 = 1 THEN 'b6' END, n % 2 = 0 FROM generate_series(1, 24) AS g(n); -- Who opened which record: the records staff in the daytime, each in their -- own office's records, then emp0207's session from 203.0.113.77. INSERT INTO record_views SELECT TIMESTAMP '2026-11-05 08:00:00' + d * INTERVAL '1 day' + ((k * 37 + r.n * 13) % 420) * INTERVAL '1 minute', 'emp0' || (200 + k), r.doc_id FROM generate_series(1, 30) AS u(k), generate_series(0, 1) AS dd(d), (SELECT doc_id, office, row_number() OVER (PARTITION BY office ORDER BY doc_id) AS n FROM records) AS r WHERE r.office = CASE WHEN k <= 12 THEN 'Permits' WHEN k <= 20 THEN 'Facilities' WHEN k <= 25 THEN 'Human Resources' ELSE 'Clerk' END AND (k * 7 + r.n * 3 + d) % 4 = 0 AND NOT (k = 7 AND d = 1); INSERT INTO record_views SELECT TIMESTAMP '2026-11-06 02:46:00' + (row_number() OVER (ORDER BY doc_id) - 1) * INTERVAL '84 seconds', 'emp0207', doc_id FROM records WHERE (office = 'Permits' AND created >= DATE '2026-07-01') OR doc_id IN ('R-0330', 'R-0341', 'R-0356', 'R-0372'); -- This week's vulnerability scan. automatable and technical_impact are -- CISA's SSVC answers from each CVE record. INSERT INTO findings VALUES ('vpn-gw-01', 'CVE-2026-19868', '2026-11-06', true, false, 'partial'), ('vpn-gw-02', 'CVE-2026-118527', '2026-11-02', true, false, 'partial'), ('bastion-01', 'CVE-2024-6387', '2026-11-02', true, false, 'total'), ('records-legacy-01', 'CVE-2025-53770', '2026-11-05', false, true, 'total'), ('wiki-01', 'CVE-2023-48795', '2026-10-20', false, false, 'partial'); VACUUM (REFRESH_TABLE) sign_ins; VACUUM (REFRESH_TABLE) records; SELECT (SELECT count(*) FROM sign_ins) AS sign_ins, (SELECT count(*) FROM records) AS records, (SELECT count(*) FROM record_views) AS record_views, (SELECT count(*) FROM findings) AS findings;
SELECT src_ip, count(*) AS attempts, count(*) FILTER (WHERE result = 'failure') AS failures, count(DISTINCT user_name) AS accounts, count(*) FILTER (WHERE result = 'success') AS successes FROM sign_ins WHERE app = 'vpn' GROUP BY src_ip ORDER BY accounts DESC, failures DESC, src_ip LIMIT 3; SELECT ts, src_ip, app, method, result FROM sign_ins_idx WHERE user_name = 'emp0207' ORDER BY ts; SELECT r.office, count(*) AS records, count(*) FILTER (WHERE r.exemption = 'b6') AS personal_info, min(v.ts)::TIME AS first, max(v.ts)::TIME AS last FROM record_views v JOIN records r USING (doc_id) WHERE v.user_name = 'emp0207' AND v.ts >= TIMESTAMP '2026-11-06 02:44:05' GROUP BY r.office ORDER BY records DESC;
WITH kev AS ( SELECT "cveID" AS cve_id, "dateAdded" AS date_added FROM read_csv('https://www.cisa.gov/sites/default/files/csv/known_exploited_vulnerabilities.csv') ) SELECT f.asset, f.cve_id, k.cve_id IS NOT NULL AS in_kev, f.technical_impact = 'total' AS total, CASE WHEN in_kev AND (f.public AND (f.automatable OR total) OR f.automatable AND total) THEN 3 WHEN in_kev THEN 14 WHEN f.public AND f.automatable AND total THEN 3 WHEN f.public AND (f.automatable OR total) THEN 14 WHEN f.public OR f.automatable THEN 60 END AS days, in_kev AND total AND (f.public OR f.automatable) AS triage, least(k.date_added, f.first_seen) + days AS due FROM findings f LEFT JOIN kev k USING (cve_id) ORDER BY due NULLS LAST; SELECT doc_id, created, doc_type, title, exemption FROM records_idx WHERE (body @@ ts_levenshtein('seawall', 1) OR body @@ ts_phrase('pier 9')) AND created BETWEEN DATE '2026-01-01' AND DATE '2026-11-05' ORDER BY created;
What agency security and records teams build on SereneDB
A public portal that searches released records only
The portal connects as its own role, granted one view: the released records, without the columns the public should not see. Full-text search runs through the view, and every table the role was not granted answers permission denied. Records staff keep their own roles.
Roles for AI agents →CREATE VIEW reading_room AS SELECT doc_id, created, office, doc_type, title, body FROM records_idx WHERE released; CREATE ROLE portal LOGIN PASSWORD 'change-me'; GRANT SELECT ON reading_room TO portal; SET ROLE portal; SELECT doc_id, created, title FROM reading_room WHERE body @@ 'seawall' ORDER BY created; SELECT count(*) FROM records; -- ERROR: permission denied for table records RESET ROLE;
| doc_id | created | title |
|---|---|---|
| R-0089 | 2025-11-20 | Seawall condition survey 2025 |
| R-0112 | 2026-01-14 | Contract HF-26-014: Pier 9 seawall repair |
A records request's due date, in working days
The Freedom of Information Act gives an agency 20 days, excepting Saturdays, Sundays and legal public holidays, to decide on a request. A series of dates without weekends and federal holidays makes the deadline its twentieth row, and isodow tells weekdays apart.
-- 20 working days after receipt: no weekends, no federal holidays. WITH holidays(d) AS ( VALUES (DATE '2026-11-11'), (DATE '2026-11-26'), (DATE '2026-12-25') ) SELECT d::DATE AS due, isodow(d) AS weekday FROM ( SELECT d, row_number() OVER (ORDER BY d) AS working_day FROM generate_series(DATE '2026-11-06', DATE '2026-12-31', INTERVAL '1 day') AS g(d) WHERE isodow(d) < 6 AND d::DATE NOT IN (SELECT d FROM holidays) ) WHERE working_day = 20;
| due | weekday |
|---|---|
| 2026-12-07 | 1 |
Identity logs in two tiers: a table, then Parquet
OMB M-21-31 sets federal agencies' identity and credential logs at 12 months in active storage and 18 months in cold storage, as minimums. Recent sign-ins stay in the table; a job copies older ones to Parquet files partitioned by day and deletes them from the table, and read_parquet still queries the files.
Search over a data lake →COPY (SELECT *, ts::DATE AS day FROM sign_ins WHERE ts < TIMESTAMP '2026-11-06 00:00:00') TO '/tmp/sign_ins_cold' (FORMAT parquet, PARTITION_BY (day), APPEND); SELECT day, count(*) AS sign_ins, count(DISTINCT user_name) AS accounts FROM read_parquet('/tmp/sign_ins_cold/*/*.parquet', hive_partitioning = true) GROUP BY day ORDER BY day; CREATE JOB sign_ins_cold EVERY 1 DAY OFFSET 3 HOURS AS $$ COPY (SELECT *, ts::DATE AS day FROM sign_ins WHERE ts < current_date - INTERVAL '12 months') TO '/tmp/sign_ins_cold' (FORMAT parquet, PARTITION_BY (day), APPEND); DELETE FROM sign_ins WHERE ts < current_date - INTERVAL '12 months'; $$;
| day | sign_ins | accounts |
|---|---|---|
| 2026-11-05 | 83 | 48 |
Public sector cyber security is the protection of government agencies' systems, networks and data: the services residents use, the staff accounts that run them and the records an agency keeps. Much of the work runs on data, from sign-in logs and vulnerability scans to the records themselves, and SereneDB is a database for that data, queried in SQL.
Identity and sign-in logs, the logs of VPNs, servers and networks, vulnerability scans of the agency's assets, and the agency's records, many of which hold personal information. On this page all four sit in one SQL engine: sign_ins, findings, records and record_views, as in the architecture above.
CISA's KEV catalog lists the vulnerabilities known to be exploited, each with its CVE ID, vendor, product, the date CISA added it and a due date. CISA publishes it as CSV and JSON; on 2026-11-08 it held 1,769 entries. SereneDB's read_csv reads the CSV from cisa.gov, so a scan joins the current catalog with no import.
Binding Operational Directive 26-04, issued by CISA on June 10, 2026, tells federal civilian executive branch agencies how fast to fix vulnerabilities. It supersedes BOD 22-01 and BOD 19-02 and sets each deadline from four answers: is the asset publicly exposed, is the CVE in the KEV catalog, can an adversary automate the exploit, and does it give partial or total control. The deadlines run from 3 days, some with forensic triage, to 14 and 60 days or the next system upgrade.
OMB memorandum M-21-31 of August 27, 2021 sets minimum retention per log category. Identity and credential management logs, criticality 0, are kept 12 months in active storage and 18 months in cold data storage; full packet capture, 72 hours. The retention example above keeps recent sign-ins in a table and older ones in Parquet.
Group failed sign-ins by source address and count the distinct accounts each one tried. A person mistyping a password fails on one account; a spray fails on many, one attempt each. In the lab, 203.0.113.77 tried 48 accounts in 30 minutes, and the one success, emp0207, then signed in with a password instead of its usual SAML and MFA.
Index the records' titles and bodies, then search with the request's terms: phrases such as "pier 9", edit-distance matches for misspellings, and a date range for the period the request covers. Each hit is a row with its exemption code, so the records that need review before release are listed with the rest.
Under 5 U.S.C. § 552(a)(6)(A)(i), 20 days, excepting Saturdays, Sundays and legal public holidays, from receipt to decide whether to comply; unusual circumstances can extend that by up to ten working days. A request received on 2026-11-05 is due 2026-12-07, as the deadline example computes.
Give every person and application its own PostgreSQL role. Column grants limit the columns a role reads: an analyst sees which records were opened and their exemption codes, not their text. A view limits the rows: the portal role sees released records only. Tables a role was not granted answer permission denied.
Yes. read_csv and read_parquet read files over HTTPS and from S3, as the patch deadlines read CISA's CSV, and ATTACH reads a PostgreSQL database in place, such as a records system's own.
Yes. A listener with the otel API makes SereneDB an OTLP endpoint, and logs, traces and metrics land in tables where a log body is full-text searchable and ranked with BM25. The observability and log analysis pages go further.
Yes, under the Apache-2.0 license, with its source on GitHub. Its Docker image runs the walkthrough above with one command.
Get started with SereneDB
Start SereneDB, load your sign-in logs, scan findings and records, and query them in SQL from any PostgreSQL client.