database

README

This directory contains:

• golang source and test files for connecting and querying a postgresql DB
• a Dockerfile to setup a test database for CI and local development
• key and cert files for the development docker DB root CA and server

The key, CSR, and cert files were generated per the "To create a server certificate whose identity can be validated by clients, first create a certificate signing request (CSR) and a public/private key file:" section of https://www.postgresql.org/docs/11/ssl-tcp.html#SSL-CERTIFICATE-CREATION with the docker compose CN of db i.e.

» openssl req -new -nodes -text -out root.csr -keyout root.key -subj "/CN=db" && chmod og-rwx root.key
Generating a 2048 bit RSA private key
........................+++
......................................................................................+++
writing new private key to 'root.key'
-----
» openssl x509 -req -in root.csr -text -days 3650 -extfile /etc/ssl/openssl.cnf -extensions v3_ca -signkey root.key -out root.crt
Signature ok
subject=CN = db
Getting Private key
» openssl req -new -nodes -text -out server.csr -keyout server.key -subj "/CN=db" && chmod og-rwx server.key
Generating a 2048 bit RSA private key
.................................+++
........+++
writing new private key to 'server.key'
-----
» openssl x509 -req -in server.csr -text -days 365 -CA root.crt -CAkey root.key -CAcreateserial -out server.crt
Signature ok
subject=CN = db
Getting CA Private Key

Documentation

Index

• Variables
• func GetTestDBHost() string
• type Config
• type Handler
  • func Connect(config Config) (*Handler, error)
  • func (db *Handler) BeginEndEntityOperations() (*Transaction, error)
  • func (db *Handler) CheckConnectionContext(ctx context.Context) error
  • func (db *Handler) GetLabelOfLatestEE(signerID string, youngerThan time.Duration) (label, x5u string, err error)
  • func (db *Handler) Monitor(pollInterval time.Duration, quit chan bool)
• type Transaction
  • func (tx *Transaction) End() error
  • func (tx *Transaction) InsertEE(x5u, label, signerID string) (err error)

Variables

var (
	// ErrNoSuitableEEFound is returned when no suitable key is found in database
	ErrNoSuitableEEFound = fmt.Errorf("no suitable key found in database")
)

Functions

func GetTestDBHost

func GetTestDBHost() string

GetTestDBHost returns the env var AUTOGRAPH_DB_HOST value or default of 127.0.0.1

Types

type Config

type Config struct {
	Name                string
	User                string
	Password            string
	Host                string
	SSLMode             string
	SSLRootCert         string
	MaxOpenConns        int
	MaxIdleConns        int
	MonitorPollInterval time.Duration
}

Config holds the parameters to connect to a database

type Handler

type Handler struct {
	*sql.DB
}

Handler handles a database connection

func Connect

func Connect(config Config) (*Handler, error)

Connect creates a database connection and returns a handler

func (*Handler) BeginEndEntityOperations

func (db *Handler) BeginEndEntityOperations() (*Transaction, error)

BeginEndEntityOperations creates a database transaction that locks the endentities table, this should be called before doing any lookup or generation operation with endentities.

This global lock will effectively prevent any sort of concurrent operation, which is exactly what we want in the case of key generation. Being slow and blocking is OK, risking two key generation the happen in parallel is not.

func (*Handler) CheckConnectionContext

func (db *Handler) CheckConnectionContext(ctx context.Context) error

CheckConnectionContext runs a test query against the database and returns an error if it fails

func (*Handler) GetLabelOfLatestEE

func (db *Handler) GetLabelOfLatestEE(signerID string, youngerThan time.Duration) (label, x5u string, err error)

GetLabelOfLatestEE returns the label of the latest end-entity for the specified signer that is no older than a given duration

func (*Handler) Monitor

func (db *Handler) Monitor(pollInterval time.Duration, quit chan bool)

Monitor queries the database every pollInterval until it gets a quit signal logging an error when the test query fails. It can be used in a goroutine to check when the database becomes unavailable.

type Transaction

type Transaction struct {
	*sql.Tx
	ID uint64
}

Transaction owns a sql transaction

func (*Transaction) End

func (tx *Transaction) End() error

End commits a transaction

func (*Transaction) InsertEE

func (tx *Transaction) InsertEE(x5u, label, signerID string) (err error)

InsertEE uses an existing transaction to insert an end-entity in database