Certificate Pinning Example | Dataford Interview Questions

What is certificate pinning and give an example.

Write a PostgreSQL query that evaluates each TLS connection event against the certificate pin configured for its endpoint and hostname. A connection is allowed only when its observed fingerprint matches an active pin during the connection time.

Output

One row per connection event with event_id, endpoint_id, hostname, connected_at, observed_fingerprint, and pinning_decision.
Include all events, label matches ALLOWED and non-matches REJECTED, and order by connected_at, then event_id.

Schema

certificate_pins

Column	Type	Description
pin_idPK	INT	Unique certificate pin identifier
endpoint_id	INT	Endpoint associated with the pin
hostname	VARCHAR(255)	TLS hostname covered by the pin
pinned_fingerprint	VARCHAR(128)	Expected certificate fingerprint
active_from	TIMESTAMP	Timestamp when the pin becomes active
active_to	TIMESTAMP	Exclusive timestamp when the pin expires

tls_connection_events

Column	Type	Description
event_idPK	INT	Unique TLS connection event identifier
endpoint_id	INT	Endpoint that initiated the connection
hostname	VARCHAR(255)	Requested TLS hostname
connected_at	TIMESTAMP	Connection timestamp
observed_fingerprint	VARCHAR(128)	Fingerprint presented by the server

Your question is Certificate Pinning Example. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem