auth
auth/profile_postgres.go
Start to support deleting profiles through the API. Create a removeLoginsByProfile method on the profileStore, to allow an easy way to bulk-delete logins associated with a Profile after the Profile has been deleted. Create postgres and memstore implementations of the removeLoginsByProfile method. Create a cleanUpAfterProfileDeletion helper method that will clean up the child objects of a Profile (its Sessions, Tokens, Clients, etc.). The intended usage is to call this in a goroutine after a Profile has been deleted, to try and get things back in order. Detect when the UpdateProfileHandler API is used to set the Deleted flag of a Profile to true, and clean up after the Profile when that's the case. Add a DeleteProfileHandler API endpoint that is a shortcut to setting the Deleted flag of a Profile to true and cleaning up after the Profile. The problem with our approach thus far is that some of it is reversible and some is not. If a Profile is maliciously/accidentally deleted, it's simple enough to use the API as a superuser to restore the Profile. But doing that will not (and cannot) restore the Logins associated with that Profile, for example. While it would be nice to add a Deleted flag to our Logins that we could simply toggle, that would wreak havoc with our database constraints and ensuring uniqueness of Login values. I still don't have a solution for this, outside the superuser manually restoring a Login for the Profile, after which the user can authenticate themselves and add more Logins as desired. But there has to be a better way. I suppose since the passphrase is being stored with the Profile and not the Login, we could offer an endpoint that would automate this, but... well, that would be tricky. It would require the user remembering their Profile ID, and let's be honest, nobody's going to remember a UUID. Maybe such an endpoint would help from a customer service standpoint: we identify their Profile manually, then send them to /profiles/ID/restorelogin or something, and that lets them add a Login back to the Profile. I'll figure it out later. For now, we know we at least have enough information to identify a user is who they say they are and resolve the situation manually.
1 package auth
3 import (
4 "time"
6 "code.secondbit.org/uuid.hg"
7 "github.com/lib/pq"
8 "github.com/secondbit/pan"
9 )
11 func (p Profile) GetSQLTableName() string {
12 return "profiles"
13 }
15 func (l Login) GetSQLTableName() string {
16 return "logins"
17 }
19 func (p *postgres) getProfileByIDSQL(id uuid.ID) *pan.Query {
20 var profile Profile
21 fields, _ := pan.GetFields(profile)
22 query := pan.New(pan.POSTGRES, "SELECT "+pan.QueryList(fields)+" FROM "+pan.GetTableName(profile))
23 query.IncludeWhere()
24 query.Include(pan.GetUnquotedColumn(profile, "ID")+" = ? AND "+pan.GetUnquotedColumn(profile, "Deleted")+" = ?", id, false)
25 return query.FlushExpressions(" ")
26 }
28 func (p *postgres) getProfileByID(id uuid.ID) (Profile, error) {
29 query := p.getProfileByIDSQL(id)
30 rows, err := p.db.Query(query.String(), query.Args...)
31 if err != nil {
32 return Profile{}, err
33 }
34 var profile Profile
35 var found bool
36 for rows.Next() {
37 err := pan.Unmarshal(rows, &profile)
38 if err != nil {
39 return Profile{}, err
40 }
41 found = true
42 }
43 if err := rows.Err(); err != nil {
44 return Profile{}, err
45 }
46 if !found {
47 return profile, ErrProfileNotFound
48 }
49 return profile, nil
50 }
52 func (p *postgres) getProfileByLoginSQL(value string) *pan.Query {
53 var profile Profile
54 var login Login
55 fields, _ := pan.GetUnquotedAbsoluteFields(profile)
56 query := pan.New(pan.POSTGRES, "SELECT "+pan.QueryList(fields)+" FROM "+pan.GetTableName(profile))
57 query.Include("INNER JOIN " + pan.GetTableName(login))
58 query.Include("ON " + pan.GetUnquotedAbsoluteColumn(profile, "ID") + " = " + pan.GetUnquotedAbsoluteColumn(login, "ProfileID"))
59 query.IncludeWhere()
60 query.Include(pan.GetUnquotedAbsoluteColumn(login, "Value")+" = ? AND "+pan.GetUnquotedAbsoluteColumn(profile, "Deleted")+" = ?", value, false)
61 return query.FlushExpressions(" ")
62 }
64 func (p *postgres) getProfileByLogin(value string) (Profile, error) {
65 query := p.getProfileByLoginSQL(value)
66 rows, err := p.db.Query(query.String(), query.Args...)
67 if err != nil {
68 return Profile{}, err
69 }
70 var profile Profile
71 var found bool
72 for rows.Next() {
73 err := pan.Unmarshal(rows, &profile)
74 if err != nil {
75 return Profile{}, err
76 }
77 found = true
78 }
79 if err := rows.Err(); err != nil {
80 return Profile{}, err
81 }
82 if !found {
83 return profile, ErrProfileNotFound
84 }
85 return profile, nil
86 }
88 func (p *postgres) saveProfileSQL(profile Profile) *pan.Query {
89 fields, values := pan.GetFields(profile)
90 query := pan.New(pan.POSTGRES, "INSERT INTO "+pan.GetTableName(profile))
91 query.Include("(" + pan.QueryList(fields) + ")")
92 query.Include("VALUES")
93 query.Include("("+pan.VariableList(len(values))+")", values...)
94 return query.FlushExpressions(" ")
95 }
97 func (p *postgres) saveProfile(profile Profile) error {
98 query := p.saveProfileSQL(profile)
99 _, err := p.db.Exec(query.String(), query.Args...)
100 if e, ok := err.(*pq.Error); ok && e.Constraint == "profiles_pkey" {
101 err = ErrProfileAlreadyExists
102 }
103 return err
104 }
106 func (p *postgres) updateProfileSQL(id uuid.ID, change ProfileChange) *pan.Query {
107 var profile Profile
108 query := pan.New(pan.POSTGRES, "UPDATE "+pan.GetTableName(profile)+" SET ")
109 query.IncludeIfNotNil(pan.GetUnquotedColumn(profile, "Name")+" = ?", change.Name)
110 query.IncludeIfNotNil(pan.GetUnquotedColumn(profile, "Passphrase")+" = ?", change.Passphrase)
111 query.IncludeIfNotNil(pan.GetUnquotedColumn(profile, "Iterations")+" = ?", change.Iterations)
112 query.IncludeIfNotNil(pan.GetUnquotedColumn(profile, "Salt")+" = ?", change.Salt)
113 query.IncludeIfNotNil(pan.GetUnquotedColumn(profile, "PassphraseScheme")+" = ?", change.PassphraseScheme)
114 query.IncludeIfNotNil(pan.GetUnquotedColumn(profile, "Compromised")+" = ?", change.Compromised)
115 query.IncludeIfNotNil(pan.GetUnquotedColumn(profile, "LockedUntil")+" = ?", change.LockedUntil)
116 query.IncludeIfNotNil(pan.GetUnquotedColumn(profile, "PassphraseReset")+" = ?", change.PassphraseReset)
117 query.IncludeIfNotNil(pan.GetUnquotedColumn(profile, "PassphraseResetCreated")+" = ?", change.PassphraseResetCreated)
118 query.IncludeIfNotNil(pan.GetUnquotedColumn(profile, "LastSeen")+" = ?", change.LastSeen)
119 query.IncludeIfNotNil(pan.GetUnquotedColumn(profile, "Deleted")+" = ?", change.Deleted)
120 query.FlushExpressions(", ")
121 query.IncludeWhere()
122 query.Include(pan.GetUnquotedColumn(profile, "ID")+" = ?", id)
123 return query.FlushExpressions(" ")
124 }
126 func (p *postgres) updateProfile(id uuid.ID, change ProfileChange) error {
127 if change.Empty() {
128 return nil
129 }
130 query := p.updateProfileSQL(id, change)
131 _, err := p.db.Exec(query.String(), query.Args...)
132 return err
133 }
135 func (p *postgres) updateProfilesSQL(ids []uuid.ID, change BulkProfileChange) *pan.Query {
136 if change.Empty() {
137 return nil
138 }
139 var profile Profile
140 query := pan.New(pan.POSTGRES, "UPDATE "+pan.GetTableName(profile)+" SET ")
141 query.IncludeIfNotNil(pan.GetUnquotedColumn(profile, "Compromised")+" = ?", change.Compromised)
142 query.FlushExpressions(", ")
143 query.IncludeWhere()
144 intids := make([]interface{}, len(ids))
145 for pos, id := range ids {
146 intids[pos] = id
147 }
148 query.Include(pan.GetUnquotedColumn(profile, "ID")+" IN ("+pan.VariableList(len(ids))+")", intids...)
149 return query.FlushExpressions(" ")
150 }
152 func (p *postgres) updateProfiles(ids []uuid.ID, change BulkProfileChange) error {
153 query := p.updateProfilesSQL(ids, change)
154 _, err := p.db.Exec(query.String(), query.Args...)
155 return err
156 }
158 func (p *postgres) addLoginSQL(login Login) *pan.Query {
159 fields, values := pan.GetFields(login)
160 query := pan.New(pan.POSTGRES, "INSERT INTO "+pan.GetTableName(login))
161 query.Include("(" + pan.QueryList(fields) + ")")
162 query.Include("VALUES")
163 query.Include("("+pan.VariableList(len(values))+")", values...)
164 return query.FlushExpressions(" ")
165 }
167 func (p *postgres) addLogin(login Login) error {
168 query := p.addLoginSQL(login)
169 _, err := p.db.Exec(query.String(), query.Args...)
170 if e, ok := err.(*pq.Error); ok && e.Constraint == "logins_pkey" {
171 return ErrLoginAlreadyExists
172 }
173 return err
174 }
176 func (p *postgres) removeLoginsByProfileSQL(profile uuid.ID) *pan.Query {
177 var login Login
178 query := pan.New(pan.POSTGRES, "DELETE FROM "+pan.GetTableName(login))
179 query.IncludeWhere()
180 query.Include(pan.GetUnquotedColumn(login, "ProfileID")+" = ?", profile)
181 return query.FlushExpressions(" ")
182 }
184 func (p *postgres) removeLoginsByProfile(profile uuid.ID) error {
185 query := p.removeLoginsByProfileSQL(profile)
186 res, err := p.db.Exec(query.String(), query.Args...)
187 if err != nil {
188 return err
189 }
190 rows, err := res.RowsAffected()
191 if err != nil {
192 return err
193 }
194 if rows == 0 {
195 return ErrProfileNotFound
196 }
197 return nil
198 }
200 func (p *postgres) removeLoginSQL(value string, profile uuid.ID) *pan.Query {
201 var login Login
202 query := pan.New(pan.POSTGRES, "DELETE FROM "+pan.GetTableName(login))
203 query.IncludeWhere()
204 query.Include(pan.GetUnquotedColumn(login, "Value")+" = ? AND "+pan.GetUnquotedColumn(login, "ProfileID")+" = ?", value, profile)
205 return query.FlushExpressions(" ")
206 }
208 func (p *postgres) removeLogin(value string, profile uuid.ID) error {
209 query := p.removeLoginSQL(value, profile)
210 res, err := p.db.Exec(query.String(), query.Args...)
211 if err != nil {
212 return err
213 }
214 rows, err := res.RowsAffected()
215 if err != nil {
216 return err
217 }
218 if rows == 0 {
219 return ErrLoginNotFound
220 }
221 return nil
222 }
224 func (p *postgres) recordLoginUseSQL(value string, when time.Time) *pan.Query {
225 var login Login
226 query := pan.New(pan.POSTGRES, "UPDATE "+pan.GetTableName(login)+" SET ")
227 query.Include(pan.GetUnquotedColumn(login, "LastUsed")+" = ?", when)
228 query.IncludeWhere()
229 query.Include(pan.GetUnquotedColumn(login, "Value")+" = ?", value)
230 return query.FlushExpressions(" ")
231 }
233 func (p *postgres) recordLoginUse(value string, when time.Time) error {
234 query := p.recordLoginUseSQL(value, when)
235 _, err := p.db.Exec(query.String(), query.Args...)
236 return err
237 }
239 func (p *postgres) listLoginsSQL(profile uuid.ID, num, offset int) *pan.Query {
240 var login Login
241 fields, _ := pan.GetFields(login)
242 query := pan.New(pan.POSTGRES, "SELECT "+pan.QueryList(fields)+" FROM "+pan.GetTableName(login))
243 query.IncludeWhere()
244 query.Include(pan.GetUnquotedColumn(login, "ProfileID")+" = ?", profile)
245 query.IncludeLimit(int64(num))
246 query.IncludeOffset(int64(offset))
247 return query.FlushExpressions(" ")
248 }
250 func (p *postgres) listLogins(profile uuid.ID, num, offset int) ([]Login, error) {
251 query := p.listLoginsSQL(profile, num, offset)
252 rows, err := p.db.Query(query.String(), query.Args...)
253 if err != nil {
254 return []Login{}, err
255 }
256 var logins []Login
257 for rows.Next() {
258 var login Login
259 err = pan.Unmarshal(rows, &login)
260 if err != nil {
261 return logins, err
262 }
263 logins = append(logins, login)
264 }
265 if err := rows.Err(); err != nil {
266 return logins, err
267 }
268 return logins, nil
269 }