package oltp
import (
"database/sql"
"errors"
"regexp"
"sort"
"strings"
"time"
)
// Environments and the secrets they hold (RFC 0019 §environments):
// what a CI job may be given, and which check-ins may have it. The
// values are sealed by the caller (internal/cisecret) before they reach
// this table, so a copied database or a backup holds no usable secret.
//
// An environment's branch policy is the safeguard a workflow file cannot
// edit: empty means protected branches only (RFC 0007), which is the
// default and means reviewed code only; otherwise it lists the branch
// patterns that qualify. The repository's own secrets are the
// environment named "" and follow the same rule.
const ciSecretSchema = `
CREATE TABLE IF NOT EXISTS ci_environment (
repo TEXT NOT NULL,
name TEXT NOT NULL, -- '' is the repository's own secrets
branches TEXT NOT NULL DEFAULT '', -- '' = protected branches only; else space-separated patterns
PRIMARY KEY (repo, name)
);
CREATE TABLE IF NOT EXISTS ci_secret (
repo TEXT NOT NULL,
environment TEXT NOT NULL, -- '' is the repository's own
name TEXT NOT NULL,
sealed BLOB NOT NULL, -- nonce || AES-256-GCM ciphertext, see internal/cisecret
updated TEXT NOT NULL,
updated_by TEXT NOT NULL DEFAULT '',
PRIMARY KEY (repo, environment, name)
);`
// Environment is one environment as the settings page lists it: never a
// value, only names and when each was last set.
type Environment struct {
Name string `json:"name"`
Branches []string `json:"branches"` // empty: protected branches only
Secrets []SecretRecord `json:"secrets"`
}
// SecretRecord names a secret without its value.
type SecretRecord struct {
Name string `json:"name"`
Updated string `json:"updated"`
UpdatedBy string `json:"updatedBy"`
}
// ErrNoEnvironment is a secret addressed to an environment the
// repository does not have.
var ErrNoEnvironment = errors.New("no such environment")
var (
// Env var names, the rule the workflow compiler applies to the names
// it will export (dagnats#728).
secretNameRE = regexp.MustCompile(`^[A-Za-z_][A-Za-z0-9_]*$`)
// Environment names: Actions' own are free text; these are kept to a
// shape that reads well in a URL and a log.
environmentNameRE = regexp.MustCompile(`^[A-Za-z0-9][A-Za-z0-9._-]{0,63}$`)
)
// ValidSecretName reports whether name can be exported as an env var.
func ValidSecretName(name string) bool { return secretNameRE.MatchString(name) }
// ValidEnvironmentName reports whether name can name an environment.
// The repository's own secrets are "", which is not a name one creates.
func ValidEnvironmentName(name string) bool { return environmentNameRE.MatchString(name) }
// SetEnvironment creates an environment or changes its branch policy.
// branches empty is protected branches only.
func (d *DB) SetEnvironment(repo, name string, branches []string, actor string) error {
if name != "" && !ValidEnvironmentName(name) {
return errors.New("an environment name is letters, digits, '.', '_' and '-', starting with a letter or digit")
}
now := strings.Join(normalizePatterns(branches), " ")
tx, err := d.w.Begin()
if err != nil {
return err
}
defer tx.Rollback()
var was string
err = tx.QueryRow(`SELECT branches FROM ci_environment WHERE repo = ? AND name = ?`, repo, name).Scan(&was)
existed := err == nil
if err != nil && !errors.Is(err, sql.ErrNoRows) {
return err
}
if existed && was == now {
return tx.Commit()
}
if _, err := tx.Exec(`INSERT INTO ci_environment (repo, name, branches) VALUES (?, ?, ?)
ON CONFLICT (repo, name) DO UPDATE SET branches = excluded.branches`, repo, name, now); err != nil {
return err
}
if !existed {
was = "(none)"
}
if err := logSetting(tx, repo, actor, "environment:"+name, policyText(was, existed), policyText(now, true)); err != nil {
return err
}
return tx.Commit()
}
// DeleteEnvironment removes an environment and every secret in it.
func (d *DB) DeleteEnvironment(repo, name, actor string) error {
if name == "" {
return errors.New("the repository's own secrets are not an environment to delete; delete the secrets")
}
tx, err := d.w.Begin()
if err != nil {
return err
}
defer tx.Rollback()
res, err := tx.Exec(`DELETE FROM ci_environment WHERE repo = ? AND name = ?`, repo, name)
if err != nil {
return err
}
if n, _ := res.RowsAffected(); n == 0 {
return ErrNoEnvironment
}
if _, err := tx.Exec(`DELETE FROM ci_secret WHERE repo = ? AND environment = ?`, repo, name); err != nil {
return err
}
if err := logSetting(tx, repo, actor, "environment:"+name, "present", "deleted"); err != nil {
return err
}
return tx.Commit()
}
// SetSecret stores a sealed value. The repository's own environment
// ("") is created on first use, with the default policy; a named one
// must already exist, so a typo cannot create an environment nobody
// chose a policy for.
func (d *DB) SetSecret(repo, environment, name string, sealed []byte, actor string) error {
if !ValidSecretName(name) {
return errors.New("a secret's name is an env var name: letters, digits and '_', not starting with a digit")
}
tx, err := d.w.Begin()
if err != nil {
return err
}
defer tx.Rollback()
if environment == "" {
if _, err := tx.Exec(`INSERT INTO ci_environment (repo, name) VALUES (?, '') ON CONFLICT DO NOTHING`, repo); err != nil {
return err
}
} else {
var one int
if err := tx.QueryRow(`SELECT 1 FROM ci_environment WHERE repo = ? AND name = ?`, repo, environment).Scan(&one); err != nil {
if errors.Is(err, sql.ErrNoRows) {
return ErrNoEnvironment
}
return err
}
}
var existing int
err = tx.QueryRow(`SELECT 1 FROM ci_secret WHERE repo = ? AND environment = ? AND name = ?`, repo, environment, name).Scan(&existing)
replaced := err == nil
if err != nil && !errors.Is(err, sql.ErrNoRows) {
return err
}
if _, err := tx.Exec(`INSERT INTO ci_secret (repo, environment, name, sealed, updated, updated_by) VALUES (?, ?, ?, ?, ?, ?)
ON CONFLICT (repo, environment, name) DO UPDATE SET sealed = excluded.sealed, updated = excluded.updated, updated_by = excluded.updated_by`,
repo, environment, name, sealed, time.Now().UTC().Format(time.RFC3339), actor); err != nil {
return err
}
was, now := "(none)", "set"
if replaced {
was, now = "set", "replaced"
}
if err := logSetting(tx, repo, actor, "secret:"+environment+"/"+name, was, now); err != nil {
return err
}
return tx.Commit()
}
// DeleteSecret removes one secret.
func (d *DB) DeleteSecret(repo, environment, name, actor string) error {
tx, err := d.w.Begin()
if err != nil {
return err
}
defer tx.Rollback()
res, err := tx.Exec(`DELETE FROM ci_secret WHERE repo = ? AND environment = ? AND name = ?`, repo, environment, name)
if err != nil {
return err
}
if n, _ := res.RowsAffected(); n == 0 {
return sql.ErrNoRows
}
if err := logSetting(tx, repo, actor, "secret:"+environment+"/"+name, "set", "deleted"); err != nil {
return err
}
return tx.Commit()
}
// Environments lists a repository's environments with their secrets'
// names, the repository's own ("") first when it has any.
func (d *DB) Environments(repo string) ([]Environment, error) {
rows, err := d.db.Query(`SELECT name, branches FROM ci_environment WHERE repo = ? ORDER BY name`, repo)
if err != nil {
return nil, err
}
var out []Environment
for rows.Next() {
var e Environment
var branches string
if err := rows.Scan(&e.Name, &branches); err != nil {
rows.Close()
return nil, err
}
e.Branches = strings.Fields(branches)
out = append(out, e)
}
rows.Close()
if err := rows.Err(); err != nil {
return nil, err
}
srows, err := d.db.Query(`SELECT environment, name, updated, updated_by FROM ci_secret WHERE repo = ? ORDER BY environment, name`, repo)
if err != nil {
return nil, err
}
defer srows.Close()
at := map[string]int{}
for i, e := range out {
at[e.Name] = i
}
for srows.Next() {
var env string
var s SecretRecord
if err := srows.Scan(&env, &s.Name, &s.Updated, &s.UpdatedBy); err != nil {
return nil, err
}
if i, ok := at[env]; ok {
out[i].Secrets = append(out[i].Secrets, s)
}
}
return out, srows.Err()
}
// SealedSecret is one secret as delivery reads it: its sealed value and
// the branch policy of the environment it lives in.
type SealedSecret struct {
Environment string
Name string
Sealed []byte
Branches []string // empty: protected branches only
}
// SealedSecrets reads the named secrets a job asks for: each from the
// job's environment when it has it there, else from the repository's
// own. A name found in neither is left out; the caller reports it.
func (d *DB) SealedSecrets(repo, environment string, names []string) (map[string]SealedSecret, error) {
out := map[string]SealedSecret{}
envs := []string{""}
if environment != "" {
envs = []string{environment, ""}
}
for _, name := range names {
for _, env := range envs {
var s SealedSecret
var branches string
err := d.db.QueryRow(`SELECT s.sealed, e.branches FROM ci_secret s
JOIN ci_environment e ON e.repo = s.repo AND e.name = s.environment
WHERE s.repo = ? AND s.environment = ? AND s.name = ?`, repo, env, name).Scan(&s.Sealed, &branches)
if errors.Is(err, sql.ErrNoRows) {
continue
}
if err != nil {
return nil, err
}
s.Environment, s.Name, s.Branches = env, name, strings.Fields(branches)
out[name] = s
break
}
}
return out, nil
}
// HasEnvironment reports whether a repository has a named environment.
func (d *DB) HasEnvironment(repo, name string) (bool, error) {
var one int
err := d.db.QueryRow(`SELECT 1 FROM ci_environment WHERE repo = ? AND name = ?`, repo, name).Scan(&one)
if errors.Is(err, sql.ErrNoRows) {
return false, nil
}
return err == nil, err
}
func normalizePatterns(ps []string) []string {
seen := map[string]bool{}
var out []string
for _, p := range ps {
for _, f := range strings.Fields(p) {
if !seen[f] {
seen[f] = true
out = append(out, f)
}
}
}
sort.Strings(out)
return out
}
func policyText(branches string, exists bool) string {
switch {
case !exists:
return branches
case branches == "":
return "protected branches"
}
return branches
}
// logSetting appends one repo_settings_log row inside tx, as
// PatchRepo does for its own fields.
func logSetting(tx *sql.Tx, repo, actor, field, was, now string) error {
_, err := tx.Exec(`INSERT INTO repo_settings_log (repo, at, actor, field, was, "now") VALUES (?, ?, ?, ?, ?, ?)`,
repo, time.Now().UTC().Format(time.RFC3339), actor, field, was, now)
return err
}