Skip to content

Instantly share code, notes, and snippets.

Daniel Fone danielfone

Block or report user

Report or block danielfone

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
View markov.sql
with recursive
-- extract all the messages as rows from the several `conversations.history`
-- API responses in the `slack_history` table
messages as (
select jsonb_array_elements(response->'messages') as message from markov.slack_history
),
-- remove @heres, @names, exclude bot messages, etc
cleaned_messages as (
View cycletimes.rb
#!/usr/bin/env ruby -w
require 'time'
require 'json'
#
# Helper methods
#
module Enumerable
def mean(method=:self)
@danielfone
danielfone / authenticate.sql
Created Aug 4, 2019
Timing-safe SQL authentication in postgresql
View authenticate.sql
-- This is a constant time query for authenticating password-based credentials
-- The key is that the bcrypt work is done whether or not the user key (email)
-- matches so the query takes the same time whether or not there was a matching
-- record. In this example, the supplied credentials are:
-- `admin@example.com` / `password`
-- This was written for postgres with pgcrypto
with
View password_auth.sql
-- This is an effective but naive approach to selecting an authenticated user from a users table.
-- The problem is that the query is (intentionally) slow if it has to check a digest,
-- but finishes very quickly if the email doesn't match, and so provides a mechanism for user enumeration.
select *
from users
where email = 'daniel@fone.net.nz'
and password_digest = crypt('password', password_digest);
-- The key goal is to provide a roughly constant time query whether the email or the password is wrong
with
View 1-create-table.sql
-- Create table to hold raw imported data
--
DROP TABLE IF EXISTS imported_reports;
CREATE TABLE imported_reports (
reference varchar primary key,
project_reference varchar,
project_name varchar,
version varchar,
generation_time varchar,
-- etc
View 4-cleanup.sql
DROP TABLE imported_reports;
VACUUM FULL ANALYZE VERBOSE;
View 3b-insert-records.sql
INSERT INTO reports (reference, program_id, generation_time, version)
SELECT
i.reference
programs.id AS program_id,
nullif(i.generation_time, '')::timestamp AS generation_time,
nullif(i.version, '')::numeric AS version,
-- etc
FROM imported_reports i
JOIN programs ON programs.reference = i.program_reference
ON CONFLICT (external_id) DO UPDATE SET
View 3a-insert-parents.sql
-- Load projects from import
--
INSERT INTO projects (reference, name, created_at, updated_at)
SELECT
i.project_reference AS reference,
i.project_name AS name
now() AS created_at,
now() AS updated_at
FROM (SELECT DISTINCT project_reference, project_name FROM imported_reports) i
ON CONFLICT (reference) DO UPDATE SET
View 2-copy-csv.sql
-- Import data
--
COPY imported_reports
FROM '/tmp/large-enterprise-export.csv'
WITH (format csv, header)
;
@danielfone
danielfone / 1-create-table.sql
Created Aug 23, 2018
4-step Postgres CSV Import
View 1-create-table.sql
-- Create table to hold raw imported data
--
DROP TABLE IF EXISTS imported_reports;
CREATE TABLE imported_reports (
reference varchar primary key,
project_reference varchar,
project_name varchar,
version varchar,
generation_time varchar,
-- etc
You can’t perform that action at this time.