Eric Bower
·
2026-03-05
postgres.go
1package pgsdb
2
3import (
4 "encoding/json"
5 "fmt"
6 "log/slog"
7 "time"
8
9 "github.com/jmoiron/sqlx"
10 _ "github.com/lib/pq"
11 "github.com/picosh/pico/pkg/db"
12 "github.com/picosh/pico/pkg/shared"
13)
14
15type PgsPsqlDB struct {
16 Logger *slog.Logger
17 Db *sqlx.DB
18}
19
20var _ PgsDB = (*PgsPsqlDB)(nil)
21
22func NewDB(databaseUrl string, logger *slog.Logger) (*PgsPsqlDB, error) {
23 var err error
24 d := &PgsPsqlDB{
25 Logger: logger,
26 }
27 d.Logger.Info("connecting to postgres", "databaseUrl", databaseUrl)
28
29 db, err := sqlx.Connect("postgres", databaseUrl)
30 if err != nil {
31 return nil, err
32 }
33
34 d.Db = db
35 return d, nil
36}
37
38func NewDBWithConn(db *sqlx.DB, logger *slog.Logger) *PgsPsqlDB {
39 return &PgsPsqlDB{
40 Logger: logger,
41 Db: db,
42 }
43}
44
45func (me *PgsPsqlDB) Close() error {
46 return me.Db.Close()
47}
48
49func (me *PgsPsqlDB) FindUsers() ([]*db.User, error) {
50 users := []*db.User{}
51 err := me.Db.Select(&users, "SELECT * FROM app_users")
52 return users, err
53}
54
55func (me *PgsPsqlDB) FindUserByPubkey(key string) (*db.User, error) {
56 pk := []db.PublicKey{}
57 err := me.Db.Select(&pk, "SELECT * FROM public_keys WHERE public_key=$1", key)
58 if err != nil {
59 return nil, err
60 }
61 if len(pk) == 0 {
62 return nil, fmt.Errorf("pubkey not found in our database: [%s]", key)
63 }
64 // When we run PublicKeyByKey and there are multiple public keys returned from the database
65 // that should mean that we don't have the correct username for this public key.
66 // When that happens we need to reject the authentication and ask the user to provide the correct
67 // username when using ssh. So instead of `ssh <domain>` it should be `ssh user@<domain>`
68 if len(pk) > 1 {
69 return nil, &db.ErrMultiplePublicKeys{}
70 }
71
72 user, err := me.FindUser(pk[0].UserID)
73 if err != nil {
74 return nil, err
75 }
76
77 user.PublicKey = &pk[0]
78 return user, nil
79}
80
81func (me *PgsPsqlDB) FindUser(userID string) (*db.User, error) {
82 user := db.User{}
83 err := me.Db.Get(&user, "SELECT * FROM app_users WHERE id=$1", userID)
84 return &user, err
85}
86
87func (me *PgsPsqlDB) FindUserByName(name string) (*db.User, error) {
88 user := db.User{}
89 err := me.Db.Get(&user, "SELECT * FROM app_users WHERE name=$1", name)
90 return &user, err
91}
92
93func (me *PgsPsqlDB) FindFeature(userID, name string) (*db.FeatureFlag, error) {
94 ff := db.FeatureFlag{}
95 err := me.Db.Get(&ff, "SELECT * FROM feature_flags WHERE user_id=$1 AND name=$2 ORDER BY expires_at DESC LIMIT 1", userID, name)
96 return &ff, err
97}
98
99func (me *PgsPsqlDB) InsertAccessLog(log *db.AccessLog) error {
100 _, err := me.Db.Exec(
101 `INSERT INTO access_logs (user_id, service, pubkey, identity) VALUES ($1, $2, $3, $4);`,
102 log.UserID,
103 log.Service,
104 log.Pubkey,
105 log.Identity,
106 )
107 return err
108}
109
110func (me *PgsPsqlDB) InsertProject(userID, name, projectDir string) (string, error) {
111 if !shared.IsValidSubdomain(name) {
112 return "", fmt.Errorf("'%s' is not a valid project name, must match /^[a-z0-9-]+$/", name)
113 }
114
115 var projectID string
116 row := me.Db.QueryRow(
117 "INSERT INTO projects (user_id, name, project_dir) VALUES ($1, $2, $3) RETURNING id",
118 userID,
119 name,
120 projectDir,
121 )
122 err := row.Scan(&projectID)
123 return projectID, err
124}
125
126func (me *PgsPsqlDB) UpdateProject(userID, name string) error {
127 _, err := me.Db.Exec("UPDATE projects SET updated_at=$1 WHERE user_id=$2 AND name=$3", time.Now(), userID, name)
128 return err
129}
130
131func (me *PgsPsqlDB) UpsertProject(userID, projectName, projectDir string) (*db.Project, error) {
132 project, err := me.FindProjectByName(userID, projectName)
133 if err == nil {
134 // this just updates the `createdAt` timestamp, useful for book-keeping
135 err = me.UpdateProject(userID, projectName)
136 if err != nil {
137 me.Logger.Error(
138 "could not update project",
139 "err", err,
140 "projectName", projectName,
141 "projectDir", projectDir,
142 )
143 return nil, err
144 }
145 return project, nil
146 }
147
148 _, err = me.InsertProject(userID, projectName, projectName)
149 if err != nil {
150 me.Logger.Error(
151 "could not create project",
152 "err", err,
153 "projectName", projectName,
154 "projectDir", projectDir,
155 )
156 return nil, err
157 }
158 if IsProjectPrivate(projectName) {
159 err = me.UpdateProjectAcl(userID, projectName, db.ProjectAcl{Type: "private", Data: []string{}})
160 if err != nil {
161 return nil, err
162 }
163 }
164 return me.FindProjectByName(userID, projectName)
165}
166
167func (me *PgsPsqlDB) LinkToProject(userID, projectID, projectDir string, commit bool) error {
168 linkToProject, err := me.FindProjectByName(userID, projectDir)
169 if err != nil {
170 return err
171 }
172 isAlreadyLinked := linkToProject.Name != linkToProject.ProjectDir
173 sameProject := linkToProject.ID == projectID
174
175 /*
176 A project linked to another project which is also linked to a
177 project is forbidden. CI/CD Example:
178 - ProjectProd links to ProjectStaging
179 - ProjectStaging links to ProjectMain
180 - We merge `main` and trigger a deploy which uploads to ProjectMain
181 - All three get updated immediately
182 This scenario was not the intent of our CI/CD. What we actually
183 wanted was to create a snapshot of ProjectMain and have ProjectStaging
184 link to the snapshot, but that's not the intended design of pgs.
185
186 So we want to close that gap here.
187
188 We ensure that `project.Name` and `project.ProjectDir` are identical
189 when there is no aliasing.
190 */
191 if !sameProject && isAlreadyLinked {
192 return fmt.Errorf(
193 "cannot link (%s) to (%s) because it is also a link to (%s)",
194 projectID,
195 projectDir,
196 linkToProject.ProjectDir,
197 )
198 }
199
200 if commit {
201 _, err = me.Db.Exec(
202 "UPDATE projects SET project_dir=$1, updated_at=$2 WHERE id=$3",
203 projectDir,
204 time.Now(),
205 projectID,
206 )
207 }
208 return err
209}
210
211func (me *PgsPsqlDB) RemoveProject(projectID string) error {
212 _, err := me.Db.Exec("DELETE FROM projects WHERE id=$1", projectID)
213 return err
214}
215
216func (me *PgsPsqlDB) FindProjectByName(userID, name string) (*db.Project, error) {
217 project := db.Project{}
218 err := me.Db.Get(&project, "SELECT * FROM projects WHERE user_id=$1 AND name=$2", userID, name)
219 return &project, err
220}
221
222func (me *PgsPsqlDB) FindProjectLinks(userID, name string) ([]*db.Project, error) {
223 projects := []*db.Project{}
224 err := me.Db.Select(
225 &projects,
226 "SELECT * FROM projects WHERE user_id=$1 AND name != project_dir AND project_dir=$2 ORDER BY name ASC",
227 userID,
228 name,
229 )
230 return projects, err
231}
232
233func (me *PgsPsqlDB) FindProjectsByPrefix(userID, prefix string) ([]*db.Project, error) {
234 projects := []*db.Project{}
235 err := me.Db.Select(
236 &projects,
237 "SELECT * FROM projects WHERE user_id=$1 AND name=project_dir AND name ILIKE $2 ORDER BY updated_at ASC, name ASC",
238 userID,
239 prefix+"%",
240 )
241 return projects, err
242}
243
244func (me *PgsPsqlDB) FindProjectsByUser(userID string) ([]*db.Project, error) {
245 projects := []*db.Project{}
246 err := me.Db.Select(
247 &projects,
248 "SELECT * FROM projects WHERE user_id=$1 ORDER BY name ASC",
249 userID,
250 )
251 return projects, err
252}
253
254func (me *PgsPsqlDB) FindProjects(by string) ([]*db.Project, error) {
255 projects := []*db.Project{}
256 err := me.Db.Select(
257 &projects,
258 `SELECT p.id, p.user_id, u.name as username, p.name, p.project_dir, p.acl, p.blocked, p.created_at, p.updated_at
259 FROM projects AS p
260 LEFT JOIN app_users AS u ON u.id = p.user_id
261 ORDER BY $1 DESC`,
262 by,
263 )
264 return projects, err
265}
266
267func (me *PgsPsqlDB) UpdateProjectAcl(userID, name string, acl db.ProjectAcl) error {
268 _, err := me.Db.Exec(
269 "UPDATE projects SET acl=$3, updated_at=$4 WHERE user_id=$1 AND name=$2",
270 userID, name, acl, time.Now(),
271 )
272 return err
273}
274
275func (me *PgsPsqlDB) RegisterAdmin(username, pubkey, pubkeyName string) error {
276 if pubkeyName == "" {
277 pubkeyName = "main"
278 }
279 var userID string
280 row := me.Db.QueryRow("INSERT INTO app_users (name) VALUES ($1) RETURNING id", username)
281 err := row.Scan(&userID)
282 if err != nil {
283 return err
284 }
285
286 _, err = me.Db.Exec("INSERT INTO public_keys (user_id, name, public_key) VALUES ($1, $2, $3)", userID, pubkeyName, pubkey)
287 if err != nil {
288 return err
289 }
290
291 _, err = me.Db.Exec("INSERT INTO feature_flags (user_id, name, expires_at) VALUES (1, 'plus', '2100-01-01')", userID)
292 if err != nil {
293 return err
294 }
295
296 _, err = me.Db.Exec("INSERT INTO feature_flags (user_id, name, expires_at) VALUES (1, 'admin', '2100-01-01')", userID)
297 return err
298}
299
300func (me *PgsPsqlDB) InsertFormEntry(userID, name string, data map[string]interface{}) error {
301 dataJSON, err := json.Marshal(data)
302 if err != nil {
303 return err
304 }
305
306 _, err = me.Db.Exec(
307 "INSERT INTO form_entries (user_id, name, data) VALUES ($1, $2, $3)",
308 userID, name, dataJSON,
309 )
310 return err
311}
312
313func (me *PgsPsqlDB) FindFormEntriesByUserAndName(userID, name string) ([]*db.FormEntry, error) {
314 entries := []*db.FormEntry{}
315 err := me.Db.Select(
316 &entries,
317 "SELECT * FROM form_entries WHERE user_id=$1 AND name=$2 ORDER BY created_at DESC",
318 userID, name,
319 )
320 return entries, err
321}
322
323func (me *PgsPsqlDB) FindFormNamesByUser(userID string) ([]string, error) {
324 names := []string{}
325 err := me.Db.Select(
326 &names,
327 "SELECT DISTINCT name FROM form_entries WHERE user_id=$1 ORDER BY name ASC",
328 userID,
329 )
330 return names, err
331}
332
333func (me *PgsPsqlDB) RemoveFormEntriesByUserAndName(userID, name string) error {
334 _, err := me.Db.Exec("DELETE FROM form_entries WHERE user_id=$1 AND name=$2", userID, name)
335 return err
336}