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