auth

Paddy 2015-04-11 Parent:379702564771 Child:849f3820b164

160:48200d8c4036 Go to Latest

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.

Download raw file
View source Diff to previous Annotate
History
paddy@148 1 package auth
paddy@148 2
paddy@148 3 import (
paddy@148 4 "time"
paddy@148 5
paddy@148 6 "code.secondbit.org/uuid.hg"
paddy@149 7 "github.com/lib/pq"
paddy@148 8 "github.com/secondbit/pan"
paddy@148 9 )
paddy@148 10
paddy@148 11 func (p Profile) GetSQLTableName() string {
paddy@148 12 return "profiles"
paddy@148 13 }
paddy@148 14
paddy@148 15 func (l Login) GetSQLTableName() string {
paddy@148 16 return "logins"
paddy@148 17 }
paddy@148 18
paddy@148 19 func (p *postgres) getProfileByIDSQL(id uuid.ID) *pan.Query {
paddy@148 20 var profile Profile
paddy@149 21 fields, _ := pan.GetFields(profile)
paddy@148 22 query := pan.New(pan.POSTGRES, "SELECT "+pan.QueryList(fields)+" FROM "+pan.GetTableName(profile))
paddy@148 23 query.IncludeWhere()
paddy@149 24 query.Include(pan.GetUnquotedColumn(profile, "ID")+" = ? AND "+pan.GetUnquotedColumn(profile, "Deleted")+" = ?", id, false)
paddy@148 25 return query.FlushExpressions(" ")
paddy@148 26 }
paddy@148 27
paddy@148 28 func (p *postgres) getProfileByID(id uuid.ID) (Profile, error) {
paddy@148 29 query := p.getProfileByIDSQL(id)
paddy@148 30 rows, err := p.db.Query(query.String(), query.Args...)
paddy@148 31 if err != nil {
paddy@148 32 return Profile{}, err
paddy@148 33 }
paddy@148 34 var profile Profile
paddy@149 35 var found bool
paddy@148 36 for rows.Next() {
paddy@148 37 err := pan.Unmarshal(rows, &profile)
paddy@148 38 if err != nil {
paddy@148 39 return Profile{}, err
paddy@148 40 }
paddy@149 41 found = true
paddy@148 42 }
paddy@148 43 if err := rows.Err(); err != nil {
paddy@148 44 return Profile{}, err
paddy@148 45 }
paddy@149 46 if !found {
paddy@149 47 return profile, ErrProfileNotFound
paddy@149 48 }
paddy@148 49 return profile, nil
paddy@148 50 }
paddy@148 51
paddy@148 52 func (p *postgres) getProfileByLoginSQL(value string) *pan.Query {
paddy@148 53 var profile Profile
paddy@148 54 var login Login
paddy@149 55 fields, _ := pan.GetUnquotedAbsoluteFields(profile)
paddy@148 56 query := pan.New(pan.POSTGRES, "SELECT "+pan.QueryList(fields)+" FROM "+pan.GetTableName(profile))
paddy@148 57 query.Include("INNER JOIN " + pan.GetTableName(login))
paddy@149 58 query.Include("ON " + pan.GetUnquotedAbsoluteColumn(profile, "ID") + " = " + pan.GetUnquotedAbsoluteColumn(login, "ProfileID"))
paddy@148 59 query.IncludeWhere()
paddy@149 60 query.Include(pan.GetUnquotedAbsoluteColumn(login, "Value")+" = ? AND "+pan.GetUnquotedAbsoluteColumn(profile, "Deleted")+" = ?", value, false)
paddy@148 61 return query.FlushExpressions(" ")
paddy@148 62 }
paddy@148 63
paddy@148 64 func (p *postgres) getProfileByLogin(value string) (Profile, error) {
paddy@148 65 query := p.getProfileByLoginSQL(value)
paddy@148 66 rows, err := p.db.Query(query.String(), query.Args...)
paddy@148 67 if err != nil {
paddy@148 68 return Profile{}, err
paddy@148 69 }
paddy@148 70 var profile Profile
paddy@149 71 var found bool
paddy@148 72 for rows.Next() {
paddy@148 73 err := pan.Unmarshal(rows, &profile)
paddy@148 74 if err != nil {
paddy@148 75 return Profile{}, err
paddy@148 76 }
paddy@149 77 found = true
paddy@148 78 }
paddy@148 79 if err := rows.Err(); err != nil {
paddy@148 80 return Profile{}, err
paddy@148 81 }
paddy@149 82 if !found {
paddy@149 83 return profile, ErrProfileNotFound
paddy@149 84 }
paddy@148 85 return profile, nil
paddy@148 86 }
paddy@148 87
paddy@148 88 func (p *postgres) saveProfileSQL(profile Profile) *pan.Query {
paddy@149 89 fields, values := pan.GetFields(profile)
paddy@148 90 query := pan.New(pan.POSTGRES, "INSERT INTO "+pan.GetTableName(profile))
paddy@148 91 query.Include("(" + pan.QueryList(fields) + ")")
paddy@148 92 query.Include("VALUES")
paddy@148 93 query.Include("("+pan.VariableList(len(values))+")", values...)
paddy@148 94 return query.FlushExpressions(" ")
paddy@148 95 }
paddy@148 96
paddy@148 97 func (p *postgres) saveProfile(profile Profile) error {
paddy@148 98 query := p.saveProfileSQL(profile)
paddy@148 99 _, err := p.db.Exec(query.String(), query.Args...)
paddy@149 100 if e, ok := err.(*pq.Error); ok && e.Constraint == "profiles_pkey" {
paddy@149 101 err = ErrProfileAlreadyExists
paddy@149 102 }
paddy@148 103 return err
paddy@148 104 }
paddy@148 105
paddy@148 106 func (p *postgres) updateProfileSQL(id uuid.ID, change ProfileChange) *pan.Query {
paddy@148 107 var profile Profile
paddy@148 108 query := pan.New(pan.POSTGRES, "UPDATE "+pan.GetTableName(profile)+" SET ")
paddy@149 109 query.IncludeIfNotNil(pan.GetUnquotedColumn(profile, "Name")+" = ?", change.Name)
paddy@149 110 query.IncludeIfNotNil(pan.GetUnquotedColumn(profile, "Passphrase")+" = ?", change.Passphrase)
paddy@149 111 query.IncludeIfNotNil(pan.GetUnquotedColumn(profile, "Iterations")+" = ?", change.Iterations)
paddy@149 112 query.IncludeIfNotNil(pan.GetUnquotedColumn(profile, "Salt")+" = ?", change.Salt)
paddy@149 113 query.IncludeIfNotNil(pan.GetUnquotedColumn(profile, "PassphraseScheme")+" = ?", change.PassphraseScheme)
paddy@149 114 query.IncludeIfNotNil(pan.GetUnquotedColumn(profile, "Compromised")+" = ?", change.Compromised)
paddy@149 115 query.IncludeIfNotNil(pan.GetUnquotedColumn(profile, "LockedUntil")+" = ?", change.LockedUntil)
paddy@149 116 query.IncludeIfNotNil(pan.GetUnquotedColumn(profile, "PassphraseReset")+" = ?", change.PassphraseReset)
paddy@149 117 query.IncludeIfNotNil(pan.GetUnquotedColumn(profile, "PassphraseResetCreated")+" = ?", change.PassphraseResetCreated)
paddy@149 118 query.IncludeIfNotNil(pan.GetUnquotedColumn(profile, "LastSeen")+" = ?", change.LastSeen)
paddy@149 119 query.IncludeIfNotNil(pan.GetUnquotedColumn(profile, "Deleted")+" = ?", change.Deleted)
paddy@148 120 query.FlushExpressions(", ")
paddy@148 121 query.IncludeWhere()
paddy@150 122 query.Include(pan.GetUnquotedColumn(profile, "ID")+" = ?", id)
paddy@148 123 return query.FlushExpressions(" ")
paddy@148 124 }
paddy@148 125
paddy@148 126 func (p *postgres) updateProfile(id uuid.ID, change ProfileChange) error {
paddy@149 127 if change.Empty() {
paddy@149 128 return nil
paddy@149 129 }
paddy@148 130 query := p.updateProfileSQL(id, change)
paddy@148 131 _, err := p.db.Exec(query.String(), query.Args...)
paddy@148 132 return err
paddy@148 133 }
paddy@148 134
paddy@148 135 func (p *postgres) updateProfilesSQL(ids []uuid.ID, change BulkProfileChange) *pan.Query {
paddy@149 136 if change.Empty() {
paddy@149 137 return nil
paddy@149 138 }
paddy@148 139 var profile Profile
paddy@148 140 query := pan.New(pan.POSTGRES, "UPDATE "+pan.GetTableName(profile)+" SET ")
paddy@149 141 query.IncludeIfNotNil(pan.GetUnquotedColumn(profile, "Compromised")+" = ?", change.Compromised)
paddy@148 142 query.FlushExpressions(", ")
paddy@148 143 query.IncludeWhere()
paddy@148 144 intids := make([]interface{}, len(ids))
paddy@148 145 for pos, id := range ids {
paddy@148 146 intids[pos] = id
paddy@148 147 }
paddy@149 148 query.Include(pan.GetUnquotedColumn(profile, "ID")+" IN ("+pan.VariableList(len(ids))+")", intids...)
paddy@148 149 return query.FlushExpressions(" ")
paddy@148 150 }
paddy@148 151
paddy@148 152 func (p *postgres) updateProfiles(ids []uuid.ID, change BulkProfileChange) error {
paddy@148 153 query := p.updateProfilesSQL(ids, change)
paddy@148 154 _, err := p.db.Exec(query.String(), query.Args...)
paddy@148 155 return err
paddy@148 156 }
paddy@148 157
paddy@148 158 func (p *postgres) addLoginSQL(login Login) *pan.Query {
paddy@149 159 fields, values := pan.GetFields(login)
paddy@148 160 query := pan.New(pan.POSTGRES, "INSERT INTO "+pan.GetTableName(login))
paddy@148 161 query.Include("(" + pan.QueryList(fields) + ")")
paddy@148 162 query.Include("VALUES")
paddy@148 163 query.Include("("+pan.VariableList(len(values))+")", values...)
paddy@148 164 return query.FlushExpressions(" ")
paddy@148 165 }
paddy@148 166
paddy@148 167 func (p *postgres) addLogin(login Login) error {
paddy@148 168 query := p.addLoginSQL(login)
paddy@148 169 _, err := p.db.Exec(query.String(), query.Args...)
paddy@149 170 if e, ok := err.(*pq.Error); ok && e.Constraint == "logins_pkey" {
paddy@149 171 return ErrLoginAlreadyExists
paddy@149 172 }
paddy@148 173 return err
paddy@148 174 }
paddy@148 175
paddy@160 176 func (p *postgres) removeLoginsByProfileSQL(profile uuid.ID) *pan.Query {
paddy@160 177 var login Login
paddy@160 178 query := pan.New(pan.POSTGRES, "DELETE FROM "+pan.GetTableName(login))
paddy@160 179 query.IncludeWhere()
paddy@160 180 query.Include(pan.GetUnquotedColumn(login, "ProfileID")+" = ?", profile)
paddy@160 181 return query.FlushExpressions(" ")
paddy@160 182 }
paddy@160 183
paddy@160 184 func (p *postgres) removeLoginsByProfile(profile uuid.ID) error {
paddy@160 185 query := p.removeLoginsByProfileSQL(profile)
paddy@160 186 res, err := p.db.Exec(query.String(), query.Args...)
paddy@160 187 if err != nil {
paddy@160 188 return err
paddy@160 189 }
paddy@160 190 rows, err := res.RowsAffected()
paddy@160 191 if err != nil {
paddy@160 192 return err
paddy@160 193 }
paddy@160 194 if rows == 0 {
paddy@160 195 return ErrProfileNotFound
paddy@160 196 }
paddy@160 197 return nil
paddy@160 198 }
paddy@160 199
paddy@148 200 func (p *postgres) removeLoginSQL(value string, profile uuid.ID) *pan.Query {
paddy@148 201 var login Login
paddy@148 202 query := pan.New(pan.POSTGRES, "DELETE FROM "+pan.GetTableName(login))
paddy@148 203 query.IncludeWhere()
paddy@150 204 query.Include(pan.GetUnquotedColumn(login, "Value")+" = ? AND "+pan.GetUnquotedColumn(login, "ProfileID")+" = ?", value, profile)
paddy@148 205 return query.FlushExpressions(" ")
paddy@148 206 }
paddy@148 207
paddy@148 208 func (p *postgres) removeLogin(value string, profile uuid.ID) error {
paddy@148 209 query := p.removeLoginSQL(value, profile)
paddy@149 210 res, err := p.db.Exec(query.String(), query.Args...)
paddy@149 211 if err != nil {
paddy@149 212 return err
paddy@149 213 }
paddy@149 214 rows, err := res.RowsAffected()
paddy@149 215 if err != nil {
paddy@149 216 return err
paddy@149 217 }
paddy@149 218 if rows == 0 {
paddy@149 219 return ErrLoginNotFound
paddy@149 220 }
paddy@149 221 return nil
paddy@148 222 }
paddy@148 223
paddy@148 224 func (p *postgres) recordLoginUseSQL(value string, when time.Time) *pan.Query {
paddy@148 225 var login Login
paddy@148 226 query := pan.New(pan.POSTGRES, "UPDATE "+pan.GetTableName(login)+" SET ")
paddy@150 227 query.Include(pan.GetUnquotedColumn(login, "LastUsed")+" = ?", when)
paddy@148 228 query.IncludeWhere()
paddy@150 229 query.Include(pan.GetUnquotedColumn(login, "Value")+" = ?", value)
paddy@148 230 return query.FlushExpressions(" ")
paddy@148 231 }
paddy@148 232
paddy@148 233 func (p *postgres) recordLoginUse(value string, when time.Time) error {
paddy@148 234 query := p.recordLoginUseSQL(value, when)
paddy@148 235 _, err := p.db.Exec(query.String(), query.Args...)
paddy@148 236 return err
paddy@148 237 }
paddy@148 238
paddy@148 239 func (p *postgres) listLoginsSQL(profile uuid.ID, num, offset int) *pan.Query {
paddy@148 240 var login Login
paddy@149 241 fields, _ := pan.GetFields(login)
paddy@148 242 query := pan.New(pan.POSTGRES, "SELECT "+pan.QueryList(fields)+" FROM "+pan.GetTableName(login))
paddy@148 243 query.IncludeWhere()
paddy@150 244 query.Include(pan.GetUnquotedColumn(login, "ProfileID")+" = ?", profile)
paddy@148 245 query.IncludeLimit(int64(num))
paddy@148 246 query.IncludeOffset(int64(offset))
paddy@148 247 return query.FlushExpressions(" ")
paddy@148 248 }
paddy@148 249
paddy@148 250 func (p *postgres) listLogins(profile uuid.ID, num, offset int) ([]Login, error) {
paddy@148 251 query := p.listLoginsSQL(profile, num, offset)
paddy@148 252 rows, err := p.db.Query(query.String(), query.Args...)
paddy@148 253 if err != nil {
paddy@148 254 return []Login{}, err
paddy@148 255 }
paddy@148 256 var logins []Login
paddy@148 257 for rows.Next() {
paddy@148 258 var login Login
paddy@148 259 err = pan.Unmarshal(rows, &login)
paddy@148 260 if err != nil {
paddy@148 261 return logins, err
paddy@148 262 }
paddy@148 263 logins = append(logins, login)
paddy@148 264 }
paddy@148 265 if err := rows.Err(); err != nil {
paddy@148 266 return logins, err
paddy@148 267 }
paddy@148 268 return logins, nil
paddy@148 269 }