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