1 package database
2
3 import (
4 "context"
5 "log"
6 "runtime/debug"
7 "strings"
8 "time"
9
10 . "codeberg.org/tslocum/sriracha/model"
11 "github.com/alexedwards/argon2id"
12 "github.com/jackc/pgx/v5"
13 )
14
15 func (db *DB) AddAccount(a *Account, password string) {
16 sessionKey := db.newSessionKey()
17 _, err := db.conn.Exec(context.Background(), "INSERT INTO account VALUES (DEFAULT, $1, $2, $3, 0, $4, $5, $6)",
18 a.Username,
19 encryptPassword(db.config.SaltPass, password),
20 a.Role,
21 sessionKey,
22 a.Style,
23 a.Locale,
24 )
25 if err != nil {
26 log.Fatalf("failed to insert account: %s", err)
27 }
28 err = db.conn.QueryRow(context.Background(), "SELECT id FROM account WHERE username = $1", a.Username).Scan(&a.ID)
29 if err != nil || a.ID == 0 {
30 log.Fatalf("failed to select id of inserted account: %s", err)
31 }
32 }
33
34 func (db *DB) createSuperAdminAccount(salt string) {
35 var numAdmins int
36 err := db.conn.QueryRow(context.Background(), "SELECT COUNT(*) FROM account WHERE role = $1", RoleSuperAdmin).Scan(&numAdmins)
37 if err != nil {
38 log.Fatalf("failed to select number of super-administrator accounts: %s", err)
39 } else if numAdmins > 0 {
40 return
41 }
42 err = db.conn.QueryRow(context.Background(), "SELECT COUNT(*) FROM account WHERE username = 'admin'").Scan(&numAdmins)
43 if err != nil {
44 log.Fatalf("failed to select number of super-administrator accounts: %s", err)
45 } else if numAdmins > 0 {
46 sessionKey := db.newSessionKey()
47 _, err = db.conn.Exec(context.Background(), "UPDATE account SET password = $1, role = $2, session = $3 WHERE username = 'admin'",
48 encryptPassword(salt, "admin"),
49 RoleSuperAdmin,
50 sessionKey,
51 )
52 if err != nil {
53 log.Fatalf("failed to insert account: %s", err)
54 }
55 return
56 }
57 _, err = db.conn.Exec(context.Background(), "INSERT INTO account VALUES (DEFAULT, 'admin', $1, $2, 0, '')", encryptPassword(db.config.SaltPass, "admin"), RoleSuperAdmin)
58 if err != nil {
59 log.Fatalf("failed to insert account: %s", err)
60 }
61 }
62
63 func (db *DB) AccountByID(id int) *Account {
64 a := &Account{}
65 err := scanAccount(a, db.conn.QueryRow(context.Background(), "SELECT * FROM account WHERE id = $1", id))
66 if err == pgx.ErrNoRows {
67 return nil
68 } else if err != nil {
69 log.Fatalf("failed to select account: %s", err)
70 }
71 return a
72 }
73
74 func (db *DB) AccountByUsername(username string) *Account {
75 a := &Account{}
76 err := scanAccount(a, db.conn.QueryRow(context.Background(), "SELECT * FROM account WHERE username = $1 AND role != $2", username, RoleDisabled))
77 if err == pgx.ErrNoRows {
78 return nil
79 } else if err != nil {
80 log.Fatalf("failed to select account: %s", err)
81 } else if a.ID == 0 {
82 return nil
83 }
84 return a
85 }
86
87 func (db *DB) AccountBySessionKey(sessionKey string) *Account {
88 if strings.TrimSpace(sessionKey) == "" {
89 return nil
90 }
91
92 a := &Account{}
93 err := scanAccount(a, db.conn.QueryRow(context.Background(), "SELECT * FROM account WHERE session = $1 AND role != $2", sessionKey, RoleDisabled))
94 if err == pgx.ErrNoRows {
95 return nil
96 } else if err != nil {
97 log.Fatalf("failed to select account: %s", err)
98 }
99 return a
100 }
101
102 func (db *DB) AllAccounts() []*Account {
103 rows, err := db.conn.Query(context.Background(), "SELECT * FROM account ORDER BY role ASC, username ASC")
104 if err != nil {
105 log.Fatalf("failed to select accounts: %s", err)
106 }
107 var accounts []*Account
108 for rows.Next() {
109 a := &Account{}
110 err = scanAccount(a, rows)
111 if err != nil {
112 log.Fatalf("failed to select accounts: %s", err)
113 }
114 accounts = append(accounts, a)
115 }
116 return accounts
117 }
118
119 func (db *DB) UpdateAccountUsername(a *Account) {
120 if a == nil || a.ID <= 0 {
121 log.Fatalf("invalid account")
122 }
123 sessionKey := db.newSessionKey()
124 _, err := db.conn.Exec(context.Background(), "UPDATE account SET username = $1, session = $2 WHERE id = $3", a.Username, sessionKey, a.ID)
125 if err != nil {
126 log.Fatalf("failed to update account: %s", err)
127 }
128 }
129
130 func (db *DB) UpdateAccountRole(a *Account) {
131 if a == nil || a.ID <= 0 {
132 log.Fatalf("invalid account")
133 }
134 _, err := db.conn.Exec(context.Background(), "UPDATE account SET role = $1 WHERE id = $2", a.Role, a.ID)
135 if err != nil {
136 log.Fatalf("failed to update account: %s", err)
137 }
138 }
139
140 func (db *DB) UpdateAccountPassword(id int, password string) {
141 if id <= 0 {
142 log.Fatalf("invalid account ID %d", id)
143 }
144 sessionKey := db.newSessionKey()
145 _, err := db.conn.Exec(context.Background(), "UPDATE account SET password = $1, session = $2 WHERE id = $3", encryptPassword(db.config.SaltPass, password), sessionKey, id)
146 if err != nil {
147 log.Fatalf("failed to update account: %s", err)
148 }
149 }
150
151 func (db *DB) UpdateAccountLastActive(id int) {
152 if id <= 0 {
153 log.Fatalf("invalid account ID %d", id)
154 }
155 _, err := db.conn.Exec(context.Background(), "UPDATE account SET lastactive = $1 WHERE id = $2", time.Now().Unix(), id)
156 if err != nil {
157 log.Fatalf("failed to update account: %s", err)
158 }
159 }
160
161 func (db *DB) UpdateAccountStyle(id int, style string) {
162 if id <= 0 {
163 log.Fatalf("invalid account ID %d", id)
164 }
165 _, err := db.conn.Exec(context.Background(), "UPDATE account SET style = $1 WHERE id = $2", style, id)
166 if err != nil {
167 log.Fatalf("failed to update account: %s", err)
168 }
169 }
170
171 func (db *DB) UpdateAccountLocale(id int, locale string) {
172 if id <= 0 {
173 log.Fatalf("invalid account ID %d", id)
174 }
175 _, err := db.conn.Exec(context.Background(), "UPDATE account SET locale = $1 WHERE id = $2", locale, id)
176 if err != nil {
177 log.Fatalf("failed to update account: %s", err)
178 }
179 }
180
181 func (db *DB) LoginAccount(username string, password string) *Account {
182 a := &Account{}
183 err := scanAccount(a, db.conn.QueryRow(context.Background(), "SELECT * FROM account WHERE username = $1 AND role != $2", username, RoleDisabled))
184 if err == pgx.ErrNoRows {
185 return nil
186 } else if err != nil {
187 log.Fatalf("failed to select account: %s", err)
188 } else if a.ID == 0 || !comparePassword(db.config.SaltPass, password, a.Password) {
189 return nil
190 }
191 a.Session = db.newSessionKey()
192 _, err = db.conn.Exec(context.Background(), "UPDATE account SET session = $1 WHERE id = $2", a.Session, a.ID)
193 if err != nil {
194 log.Fatalf("failed to update account: %s", err)
195 }
196 return a
197 }
198
199 func scanAccount(a *Account, row pgx.Row) error {
200 return row.Scan(
201 &a.ID,
202 &a.Username,
203 &a.Password,
204 &a.Role,
205 &a.LastActive,
206 &a.Session,
207 &a.Style,
208 &a.Locale,
209 )
210 }
211
212 func encryptPassword(salt string, password string) string {
213 hash, err := argon2id.CreateHash(password+salt, argon2idParameters)
214 debug.FreeOSMemory()
215 if err != nil {
216 log.Fatal(err)
217 }
218 return hash
219 }
220
221 func comparePassword(salt string, password string, hash string) bool {
222 match, err := argon2id.ComparePasswordAndHash(password+salt, hash)
223 debug.FreeOSMemory()
224 if err != nil {
225 log.Fatal(err)
226 }
227 return match
228 }
229
View as plain text