steve-chavez

How to setup your own CA with OpenSSL

For educational reasons I've decided to create my own CA. Here is what I learned.

First things first

Lets get some context first.

steve-chavez

HTTP Streaming (or Chunked vs Store & Forward)

The standard way of understanding the HTTP protocol is via the request reply pattern. Each HTTP transaction consists of a finitely bounded HTTP request and a finitely bounded HTTP response.

However it's also possible for both parts of an HTTP 1.1 transaction to stream their possibly infinitely bounded data. The advantages is that the sender can send data that is beyond the sender's memory limit, and the receiver can act on

steve-chavez

-- Prior 9.6 \setrandom :rid 1 999900
\set rid random(1, 999900)

BEGIN ISOLATION LEVEL READ COMMITTED READ ONLY;

WITH pg_source AS
  (SELECT "parent_query_test"."items"."id",
          "parent_query_test"."items"."name",
          row_to_json("parent_items_parent_items".*) AS "parent_items"
   FROM "parent_query_test"."items"

steve-chavez

select current_setting('response.smth', true) is null; -- gives true
begin;
set local "response.smth" = 'value';
commit; -- or rollback
select current_setting('response.smth', true) is null; -- gives false since current_setting('response.smth') now is equal to ''

steve-chavez

-- child embed, cte better cost, almost half of subselect

-- cost=31864.78..31864.80
explain WITH pg_source AS (
  SELECT  "test"."clients".*, COALESCE((SELECT array_to_json(array_agg(row_to_json("projects".*))) FROM (SELECT  "test"."projects"."name" FROM  "test"."projects"  WHERE  "test"."projects"."client_id" = "test"."clients"."id"  ) "projects"), '[]') AS "projects" FROM  "test"."clients"    
) SELECT null AS total_result_set, 
         pg_catalog.count(_postgrest_t) AS page_total, 
         array[]::text[] AS header, 
         coalesce(array_to_json(array_agg(row_to_json(_postgrest_t))), '[]')::character varying AS body
FROM ( SELECT * FROM pg_source) _postgrest_t;

steve-chavez

%.hex: %.asm
	avra -fI $<
	rm *.eep.hex *.obj *.cof

all: $(patsubst %.asm,%.hex,$(wildcard *.asm))

upload: ${program}.hex
	avrdude -c arduino -p m328p -P /dev/arduino-uno -b 115200 -U flash:w:$<

monitor:

steve-chavez

# Minimal example of getting a PostgREST API running from scratch for
# testing purposes.  It uses docker to launch a postgres database and
# a postgrest api server.

# This should not be used to deploy a production system but to
# understand how postgrest works.  In particular there is no security
# implemented, see the docs for more.

# https://postgrest.org/en/v4.4/

steve-chavez

version: '3'
services:
  db:
    image: timescale/timescaledb:latest-pg10
    ports:
      - "5432:5432"
    environment:
      POSTGRES_DB: devices_small
      POSTGRES_USER: app_user
  pgrest:

steve-chavez

Download Audio from YouTube

-i - ignore errors

-c - continue

-t - use video title as file name

--extract-audio - extract audio track

steve-chavez

docker run -d -p 5433:5432 pipelinedb/pipelinedb:0.9.9

PGPASSWORD=pipeline psql -h localhost -p 5433 -U pipeline -d pipeline <<EOF
  CREATE STREAM wiki_stream (hour timestamp, project text, title text, view_count bigint, size bigint);

  CREATE CONTINUOUS VIEW wiki_stats AS
  SELECT hour, project,
          count(*) AS total_pages,
          sum(view_count) AS total_views,
          min(view_count) AS min_views,