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	// A bind parameter in ORDER BY sorts by a constant, so map to a column.
256	orderBy := "p.created_at"
257	if by == "updated_at" {
258		orderBy = "p.updated_at"
259	}
260	projects := []*db.Project{}
261	err := me.Db.Select(
262		&projects,
263		fmt.Sprintf(`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
264		FROM projects AS p
265		LEFT JOIN app_users AS u ON u.id = p.user_id
266		ORDER BY %s DESC`, orderBy),
267	)
268	return projects, err
269}
270
271func (me *PgsPsqlDB) UpdateProjectAcl(userID, name string, acl db.ProjectAcl) error {
272	_, err := me.Db.Exec(
273		"UPDATE projects SET acl=$3, updated_at=$4 WHERE user_id=$1 AND name=$2",
274		userID, name, acl, time.Now(),
275	)
276	return err
277}
278
279func (me *PgsPsqlDB) RegisterAdmin(username, pubkey, pubkeyName string) error {
280	if pubkeyName == "" {
281		pubkeyName = "main"
282	}
283	var userID string
284	row := me.Db.QueryRow("INSERT INTO app_users (name) VALUES ($1) RETURNING id", username)
285	err := row.Scan(&userID)
286	if err != nil {
287		return err
288	}
289
290	_, err = me.Db.Exec("INSERT INTO public_keys (user_id, name, public_key) VALUES ($1, $2, $3)", userID, pubkeyName, pubkey)
291	if err != nil {
292		return err
293	}
294
295	_, err = me.Db.Exec("INSERT INTO feature_flags (user_id, name, expires_at) VALUES (1, 'plus', '2100-01-01')", userID)
296	if err != nil {
297		return err
298	}
299
300	_, err = me.Db.Exec("INSERT INTO feature_flags (user_id, name, expires_at) VALUES (1, 'admin', '2100-01-01')", userID)
301	return err
302}
303
304func (me *PgsPsqlDB) InsertFormEntry(userID, name string, data map[string]interface{}) error {
305	dataJSON, err := json.Marshal(data)
306	if err != nil {
307		return err
308	}
309
310	_, err = me.Db.Exec(
311		"INSERT INTO form_entries (user_id, name, data) VALUES ($1, $2, $3)",
312		userID, name, dataJSON,
313	)
314	return err
315}
316
317func (me *PgsPsqlDB) FindFormEntriesByUserAndName(userID, name string) ([]*db.FormEntry, error) {
318	entries := []*db.FormEntry{}
319	err := me.Db.Select(
320		&entries,
321		"SELECT * FROM form_entries WHERE user_id=$1 AND name=$2 ORDER BY created_at DESC",
322		userID, name,
323	)
324	return entries, err
325}
326
327func (me *PgsPsqlDB) FindFormNamesByUser(userID string) ([]string, error) {
328	names := []string{}
329	err := me.Db.Select(
330		&names,
331		"SELECT DISTINCT name FROM form_entries WHERE user_id=$1 ORDER BY name ASC",
332		userID,
333	)
334	return names, err
335}
336
337func (me *PgsPsqlDB) RemoveFormEntriesByUserAndName(userID, name string) error {
338	_, err := me.Db.Exec("DELETE FROM form_entries WHERE user_id=$1 AND name=$2", userID, name)
339	return err
340}