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