veryroundbird.house
1// an sqlite3 backed secret manager
2package secrets
3
4import (
5 "context"
6 "database/sql"
7 "fmt"
8 "time"
9
10 _ "github.com/mattn/go-sqlite3"
11)
12
13type SqliteManager struct {
14 db *sql.DB
15 tableName string
16}
17
18type SqliteManagerOpt func(*SqliteManager)
19
20func WithTableName(name string) SqliteManagerOpt {
21 return func(s *SqliteManager) {
22 s.tableName = name
23 }
24}
25
26func NewSQLiteManager(dbPath string, opts ...SqliteManagerOpt) (*SqliteManager, error) {
27 db, err := sql.Open("sqlite3", dbPath)
28 if err != nil {
29 return nil, fmt.Errorf("failed to open sqlite database: %w", err)
30 }
31
32 manager := &SqliteManager{
33 db: db,
34 tableName: "secrets",
35 }
36
37 for _, o := range opts {
38 o(manager)
39 }
40
41 if err := manager.init(); err != nil {
42 return nil, err
43 }
44
45 return manager, nil
46}
47
48// creates a table and sets up the schema, migrations if any can go here
49func (s *SqliteManager) init() error {
50 createTable :=
51 `create table if not exists ` + s.tableName + `(
52 id integer primary key autoincrement,
53 repo text not null,
54 key text not null,
55 value text not null,
56 created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
57 created_by text not null,
58
59 unique(repo, key)
60 );`
61 _, err := s.db.Exec(createTable)
62 return err
63}
64
65func (s *SqliteManager) AddSecret(ctx context.Context, secret UnlockedSecret) error {
66 query := fmt.Sprintf(`
67 insert or ignore into %s (repo, key, value, created_by)
68 values (?, ?, ?, ?);
69 `, s.tableName)
70
71 res, err := s.db.ExecContext(ctx, query, secret.Repo, secret.Key, secret.Value, secret.CreatedBy)
72 if err != nil {
73 return err
74 }
75
76 num, err := res.RowsAffected()
77 if err != nil {
78 return err
79 }
80
81 if num == 0 {
82 return ErrKeyAlreadyPresent
83 }
84
85 return nil
86}
87
88func (s *SqliteManager) RemoveSecret(ctx context.Context, secret Secret[any]) error {
89 query := fmt.Sprintf(`
90 delete from %s where repo = ? and key = ?;
91 `, s.tableName)
92
93 res, err := s.db.ExecContext(ctx, query, secret.Repo, secret.Key)
94 if err != nil {
95 return err
96 }
97
98 num, err := res.RowsAffected()
99 if err != nil {
100 return err
101 }
102
103 if num == 0 {
104 return ErrKeyNotFound
105 }
106
107 return nil
108}
109
110func (s *SqliteManager) GetSecretsLocked(ctx context.Context, didSlashRepo DidSlashRepo) ([]LockedSecret, error) {
111 query := fmt.Sprintf(`
112 select repo, key, created_at, created_by from %s where repo = ?;
113 `, s.tableName)
114
115 rows, err := s.db.QueryContext(ctx, query, didSlashRepo)
116 if err != nil {
117 return nil, err
118 }
119
120 var ls []LockedSecret
121 for rows.Next() {
122 var l LockedSecret
123 var createdAt string
124 if err = rows.Scan(&l.Repo, &l.Key, &createdAt, &l.CreatedBy); err != nil {
125 return nil, err
126 }
127
128 if t, err := time.Parse(time.RFC3339, createdAt); err == nil {
129 l.CreatedAt = t
130 }
131
132 ls = append(ls, l)
133 }
134
135 if err = rows.Err(); err != nil {
136 return nil, err
137 }
138
139 return ls, nil
140}
141
142func (s *SqliteManager) GetSecretsUnlocked(ctx context.Context, didSlashRepo DidSlashRepo) ([]UnlockedSecret, error) {
143 query := fmt.Sprintf(`
144 select repo, key, value, created_at, created_by from %s where repo = ?;
145 `, s.tableName)
146
147 rows, err := s.db.QueryContext(ctx, query, didSlashRepo)
148 if err != nil {
149 return nil, err
150 }
151
152 var ls []UnlockedSecret
153 for rows.Next() {
154 var l UnlockedSecret
155 var createdAt string
156 if err = rows.Scan(&l.Repo, &l.Key, &l.Value, &createdAt, &l.CreatedBy); err != nil {
157 return nil, err
158 }
159
160 if t, err := time.Parse(time.RFC3339, createdAt); err == nil {
161 l.CreatedAt = t
162 }
163
164 ls = append(ls, l)
165 }
166
167 if err = rows.Err(); err != nil {
168 return nil, err
169 }
170
171 return ls, nil
172}