auth
148:06fb735031bb Browse Files
Do a first, naive pass at storing profiles in Postgres. This is untested against an actual database. It's a best-guess attempt at SQL. It _should_ work. I think. Start storing things in Postgres, starting with Profiles and Logins. This necessitates the addition of a Deleted property to the Profile type, because I'm not deleting those in case of accidental deletion. Logins, though, we'll delete. This also necessitates updating the profileStore interface to no longer have a deleteProfile method, because we're tracking that through updates now. Then we need to update our profileStore tests, because they no longer clean up after themselves. Which, come to think of it, may cause some problems later.
context.go postgres.go profile.go profile_postgres.go profile_test.go
1.1 --- a/context.go Fri Mar 20 23:03:21 2015 -0400 1.2 +++ b/context.go Sat Mar 21 01:23:33 2015 -0400 1.3 @@ -256,15 +256,6 @@ 1.4 return c.profiles.updateProfiles(ids, change) 1.5 } 1.6 1.7 -// DeleteProfile removes the Profile specified by the passed ID from the profileStore associated 1.8 -// with the Context. 1.9 -func (c Context) DeleteProfile(id uuid.ID) error { 1.10 - if c.profiles == nil { 1.11 - return ErrNoProfileStore 1.12 - } 1.13 - return c.profiles.deleteProfile(id) 1.14 -} 1.15 - 1.16 // AddLogin stores the passed Login in the profileStore associated with the Context. It also associates 1.17 // the newly-created Login with the Orofile in login.ProfileID. 1.18 func (c Context) AddLogin(login Login) error {
2.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 2.2 +++ b/postgres.go Sat Mar 21 01:23:33 2015 -0400 2.3 @@ -0,0 +1,11 @@ 2.4 +package auth 2.5 + 2.6 +import ( 2.7 + "database/sql" 2.8 + 2.9 + _ "github.com/lib/pq" 2.10 +) 2.11 + 2.12 +type postgres struct { 2.13 + db *sql.DB 2.14 +}
3.1 --- a/profile.go Fri Mar 20 23:03:21 2015 -0400 3.2 +++ b/profile.go Sat Mar 21 01:23:33 2015 -0400 3.3 @@ -82,6 +82,7 @@ 3.4 PassphraseResetCreated time.Time `json:"-"` 3.5 Created time.Time `json:"created,omitempty"` 3.6 LastSeen time.Time `json:"last_seen,omitempty"` 3.7 + Deleted bool `json:"deleted,omitempty"` 3.8 } 3.9 3.10 // ApplyChange applies the properties of the passed ProfileChange 3.11 @@ -117,6 +118,9 @@ 3.12 if change.LastSeen != nil { 3.13 p.LastSeen = *change.LastSeen 3.14 } 3.15 + if change.Deleted != nil { 3.16 + p.Deleted = *change.Deleted 3.17 + } 3.18 } 3.19 3.20 // ApplyBulkChange applies the properties of the passed BulkProfileChange 3.21 @@ -139,6 +143,7 @@ 3.22 PassphraseReset *string 3.23 PassphraseResetCreated *time.Time 3.24 LastSeen *time.Time 3.25 + Deleted *bool 3.26 } 3.27 3.28 // Validate checks the ProfileChange it is called on 3.29 @@ -146,7 +151,7 @@ 3.30 // A descriptive error will be returned in the case of 3.31 // an invalid change. 3.32 func (c ProfileChange) Validate() error { 3.33 - if c.Name == nil && c.Passphrase == nil && c.Iterations == nil && c.Salt == nil && c.PassphraseScheme == nil && c.Compromised == nil && c.LockedUntil == nil && c.PassphraseReset == nil && c.PassphraseResetCreated == nil && c.LastSeen == nil { 3.34 + if c.Name == nil && c.Passphrase == nil && c.Iterations == nil && c.Salt == nil && c.PassphraseScheme == nil && c.Compromised == nil && c.LockedUntil == nil && c.PassphraseReset == nil && c.PassphraseResetCreated == nil && c.LastSeen == nil && c.Deleted == nil { 3.35 return ErrEmptyChange 3.36 } 3.37 if c.PassphraseScheme != nil && c.Passphrase == nil { 3.38 @@ -260,7 +265,6 @@ 3.39 saveProfile(profile Profile) error 3.40 updateProfile(id uuid.ID, change ProfileChange) error 3.41 updateProfiles(ids []uuid.ID, change BulkProfileChange) error 3.42 - deleteProfile(id uuid.ID) error 3.43 3.44 addLogin(login Login) error 3.45 removeLogin(value string, profile uuid.ID) error 3.46 @@ -275,6 +279,9 @@ 3.47 if !ok { 3.48 return Profile{}, ErrProfileNotFound 3.49 } 3.50 + if p.Deleted { 3.51 + return Profile{}, ErrProfileNotFound 3.52 + } 3.53 return p, nil 3.54 } 3.55 3.56 @@ -291,6 +298,9 @@ 3.57 if !ok { 3.58 return Profile{}, ErrProfileNotFound 3.59 } 3.60 + if profile.Deleted { 3.61 + return Profile{}, ErrProfileNotFound 3.62 + } 3.63 return profile, nil 3.64 } 3.65 3.66 @@ -332,17 +342,6 @@ 3.67 return nil 3.68 } 3.69 3.70 -func (m *memstore) deleteProfile(id uuid.ID) error { 3.71 - m.profileLock.Lock() 3.72 - defer m.profileLock.Unlock() 3.73 - _, ok := m.profiles[id.String()] 3.74 - if !ok { 3.75 - return ErrProfileNotFound 3.76 - } 3.77 - delete(m.profiles, id.String()) 3.78 - return nil 3.79 -} 3.80 - 3.81 func (m *memstore) addLogin(login Login) error { 3.82 m.loginLock.Lock() 3.83 defer m.loginLock.Unlock()
4.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 4.2 +++ b/profile_postgres.go Sat Mar 21 01:23:33 2015 -0400 4.3 @@ -0,0 +1,212 @@ 4.4 +package auth 4.5 + 4.6 +import ( 4.7 + "time" 4.8 + 4.9 + "code.secondbit.org/uuid.hg" 4.10 + "github.com/secondbit/pan" 4.11 +) 4.12 + 4.13 +func (p Profile) GetSQLTableName() string { 4.14 + return "profiles" 4.15 +} 4.16 + 4.17 +func (l Login) GetSQLTableName() string { 4.18 + return "logins" 4.19 +} 4.20 + 4.21 +func (p *postgres) getProfileByIDSQL(id uuid.ID) *pan.Query { 4.22 + var profile Profile 4.23 + fields, _ := pan.GetQuotedFields(profile) 4.24 + query := pan.New(pan.POSTGRES, "SELECT "+pan.QueryList(fields)+" FROM "+pan.GetTableName(profile)) 4.25 + query.IncludeWhere() 4.26 + query.Include(pan.GetColumn(profile, "ID")+" = ? AND "+pan.GetColumn(profile, "Deleted")+" = ?", id, false) 4.27 + return query.FlushExpressions(" ") 4.28 +} 4.29 + 4.30 +func (p *postgres) getProfileByID(id uuid.ID) (Profile, error) { 4.31 + query := p.getProfileByIDSQL(id) 4.32 + rows, err := p.db.Query(query.String(), query.Args...) 4.33 + if err != nil { 4.34 + return Profile{}, err 4.35 + } 4.36 + var profile Profile 4.37 + for rows.Next() { 4.38 + err := pan.Unmarshal(rows, &profile) 4.39 + if err != nil { 4.40 + return Profile{}, err 4.41 + } 4.42 + } 4.43 + if err := rows.Err(); err != nil { 4.44 + return Profile{}, err 4.45 + } 4.46 + return profile, nil 4.47 +} 4.48 + 4.49 +func (p *postgres) getProfileByLoginSQL(value string) *pan.Query { 4.50 + var profile Profile 4.51 + var login Login 4.52 + fields, _ := pan.GetAbsoluteFields(profile) 4.53 + query := pan.New(pan.POSTGRES, "SELECT "+pan.QueryList(fields)+" FROM "+pan.GetTableName(profile)) 4.54 + query.Include("INNER JOIN " + pan.GetTableName(login)) 4.55 + query.Include("ON " + pan.GetAbsoluteColumnName(profile, "ID") + " = " + pan.GetAbsoluteColumnName(login, "ProfileID")) 4.56 + query.IncludeWhere() 4.57 + query.Include(pan.GetAbsoluteColumnName(login, "Value")+" = ? AND "+pan.GetAbsoluteColumnName(profile, "Deleted")+" = ?", value, false) 4.58 + return query.FlushExpressions(" ") 4.59 +} 4.60 + 4.61 +func (p *postgres) getProfileByLogin(value string) (Profile, error) { 4.62 + query := p.getProfileByLoginSQL(value) 4.63 + rows, err := p.db.Query(query.String(), query.Args...) 4.64 + if err != nil { 4.65 + return Profile{}, err 4.66 + } 4.67 + var profile Profile 4.68 + for rows.Next() { 4.69 + err := pan.Unmarshal(rows, &profile) 4.70 + if err != nil { 4.71 + return Profile{}, err 4.72 + } 4.73 + } 4.74 + if err := rows.Err(); err != nil { 4.75 + return Profile{}, err 4.76 + } 4.77 + return profile, nil 4.78 +} 4.79 + 4.80 +func (p *postgres) saveProfileSQL(profile Profile) *pan.Query { 4.81 + fields, values := pan.GetQuotedFields(profile) 4.82 + query := pan.New(pan.POSTGRES, "INSERT INTO "+pan.GetTableName(profile)) 4.83 + query.Include("(" + pan.QueryList(fields) + ")") 4.84 + query.Include("VALUES") 4.85 + query.Include("("+pan.VariableList(len(values))+")", values...) 4.86 + return query.FlushExpressions(" ") 4.87 +} 4.88 + 4.89 +func (p *postgres) saveProfile(profile Profile) error { 4.90 + query := p.saveProfileSQL(profile) 4.91 + _, err := p.db.Exec(query.String(), query.Args...) 4.92 + return err 4.93 +} 4.94 + 4.95 +func (p *postgres) updateProfileSQL(id uuid.ID, change ProfileChange) *pan.Query { 4.96 + var profile Profile 4.97 + query := pan.New(pan.POSTGRES, "UPDATE "+pan.GetTableName(profile)+" SET ") 4.98 + query.IncludeIfNotNil(pan.GetColumn(profile, "Name")+" = ?", change.Name) 4.99 + query.IncludeIfNotNil(pan.GetColumn(profile, "Passphrase")+" = ?", change.Passphrase) 4.100 + query.IncludeIfNotNil(pan.GetColumn(profile, "Iterations")+" = ?", change.Iterations) 4.101 + query.IncludeIfNotNil(pan.GetColumn(profile, "Salt")+" = ?", change.Salt) 4.102 + query.IncludeIfNotNil(pan.GetColumn(profile, "PassphraseScheme")+" = ?", change.PassphraseScheme) 4.103 + query.IncludeIfNotNil(pan.GetColumn(profile, "Compromised")+" = ?", change.Compromised) 4.104 + query.IncludeIfNotNil(pan.GetColumn(profile, "LockedUntil")+" = ?", change.LockedUntil) 4.105 + query.IncludeIfNotNil(pan.GetColumn(profile, "PassphraseReset")+" = ?", change.PassphraseReset) 4.106 + query.IncludeIfNotNil(pan.GetColumn(profile, "PassphraseResetCreated")+" = ?", change.PassphraseResetCreated) 4.107 + query.IncludeIfNotNil(pan.GetColumn(profile, "LastSeen")+" = ?", change.LastSeen) 4.108 + query.IncludeIfNotNil(pan.GetColumn(profile, "Deleted")+" = ?", change.Deleted) 4.109 + query.FlushExpressions(", ") 4.110 + query.IncludeWhere() 4.111 + query.Include(pan.GetColumn(profile, "ID")+"= ?", id) 4.112 + return query.FlushExpressions(" ") 4.113 +} 4.114 + 4.115 +func (p *postgres) updateProfile(id uuid.ID, change ProfileChange) error { 4.116 + query := p.updateProfileSQL(id, change) 4.117 + _, err := p.db.Exec(query.String(), query.Args...) 4.118 + return err 4.119 +} 4.120 + 4.121 +func (p *postgres) updateProfilesSQL(ids []uuid.ID, change BulkProfileChange) *pan.Query { 4.122 + var profile Profile 4.123 + query := pan.New(pan.POSTGRES, "UPDATE "+pan.GetTableName(profile)+" SET ") 4.124 + query.IncludeIfNotNil(pan.GetColumn(profile, "Compromised")+" = ?", change.Compromised) 4.125 + query.FlushExpressions(", ") 4.126 + query.IncludeWhere() 4.127 + intids := make([]interface{}, len(ids)) 4.128 + for pos, id := range ids { 4.129 + intids[pos] = id 4.130 + } 4.131 + query.Include(pan.GetColumn(profile, "ID")+" IN ("+pan.VariableList(len(ids))+")", intids...) 4.132 + return query.FlushExpressions(" ") 4.133 +} 4.134 + 4.135 +func (p *postgres) updateProfiles(ids []uuid.ID, change BulkProfileChange) error { 4.136 + query := p.updateProfilesSQL(ids, change) 4.137 + _, err := p.db.Exec(query.String(), query.Args...) 4.138 + return err 4.139 +} 4.140 + 4.141 +func (p *postgres) addLoginSQL(login Login) *pan.Query { 4.142 + fields, values := pan.GetQuotedFields(login) 4.143 + query := pan.New(pan.POSTGRES, "INSERT INTO "+pan.GetTableName(login)) 4.144 + query.Include("(" + pan.QueryList(fields) + ")") 4.145 + query.Include("VALUES") 4.146 + query.Include("("+pan.VariableList(len(values))+")", values...) 4.147 + return query.FlushExpressions(" ") 4.148 +} 4.149 + 4.150 +func (p *postgres) addLogin(login Login) error { 4.151 + query := p.addLoginSQL(login) 4.152 + _, err := p.db.Exec(query.String(), query.Args...) 4.153 + return err 4.154 +} 4.155 + 4.156 +func (p *postgres) removeLoginSQL(value string, profile uuid.ID) *pan.Query { 4.157 + var login Login 4.158 + query := pan.New(pan.POSTGRES, "DELETE FROM "+pan.GetTableName(login)) 4.159 + query.IncludeWhere() 4.160 + query.Include(pan.GetColumn(login, "Value")+"= ? AND "+pan.GetColumn(login, "ProfileID")+"= ?", value, profile) 4.161 + return query.FlushExpressions(" ") 4.162 +} 4.163 + 4.164 +func (p *postgres) removeLogin(value string, profile uuid.ID) error { 4.165 + query := p.removeLoginSQL(value, profile) 4.166 + _, err := p.db.Exec(query.String(), query.Args...) 4.167 + return err 4.168 +} 4.169 + 4.170 +func (p *postgres) recordLoginUseSQL(value string, when time.Time) *pan.Query { 4.171 + var login Login 4.172 + query := pan.New(pan.POSTGRES, "UPDATE "+pan.GetTableName(login)+" SET ") 4.173 + query.Include(pan.GetColumn(login, "LastUsed")+"= ?", when) 4.174 + query.IncludeWhere() 4.175 + query.Include(pan.GetColumn(login, "Value")+"= ?", value) 4.176 + return query.FlushExpressions(" ") 4.177 +} 4.178 + 4.179 +func (p *postgres) recordLoginUse(value string, when time.Time) error { 4.180 + query := p.recordLoginUseSQL(value, when) 4.181 + _, err := p.db.Exec(query.String(), query.Args...) 4.182 + return err 4.183 +} 4.184 + 4.185 +func (p *postgres) listLoginsSQL(profile uuid.ID, num, offset int) *pan.Query { 4.186 + var login Login 4.187 + fields, _ := pan.GetQuotedFields(login) 4.188 + query := pan.New(pan.POSTGRES, "SELECT "+pan.QueryList(fields)+" FROM "+pan.GetTableName(login)) 4.189 + query.IncludeWhere() 4.190 + query.Include(pan.GetColumn(login, "ProfileID")+"= ?", profile) 4.191 + query.IncludeLimit(int64(num)) 4.192 + query.IncludeOffset(int64(offset)) 4.193 + return query.FlushExpressions(" ") 4.194 +} 4.195 + 4.196 +func (p *postgres) listLogins(profile uuid.ID, num, offset int) ([]Login, error) { 4.197 + query := p.listLoginsSQL(profile, num, offset) 4.198 + rows, err := p.db.Query(query.String(), query.Args...) 4.199 + if err != nil { 4.200 + return []Login{}, err 4.201 + } 4.202 + var logins []Login 4.203 + for rows.Next() { 4.204 + var login Login 4.205 + err = pan.Unmarshal(rows, &login) 4.206 + if err != nil { 4.207 + return logins, err 4.208 + } 4.209 + logins = append(logins, login) 4.210 + } 4.211 + if err := rows.Err(); err != nil { 4.212 + return logins, err 4.213 + } 4.214 + return logins, nil 4.215 +}
5.1 --- a/profile_test.go Fri Mar 20 23:03:21 2015 -0400 5.2 +++ b/profile_test.go Sat Mar 21 01:23:33 2015 -0400 5.3 @@ -60,6 +60,9 @@ 5.4 if !profile1.LastSeen.Equal(profile2.LastSeen) { 5.5 return false, "last seen", profile1.LastSeen, profile2.LastSeen 5.6 } 5.7 + if profile1.Deleted != profile2.Deleted { 5.8 + return false, "deleted", profile1.Deleted, profile2.Deleted 5.9 + } 5.10 return true, "", nil, nil 5.11 } 5.12 5.13 @@ -116,7 +119,8 @@ 5.14 if !match { 5.15 t.Errorf("Expected `%v` in the `%s` field of profile retrieved from %T, got `%v`", expectation, field, store, result) 5.16 } 5.17 - err = context.DeleteProfile(profile.ID) 5.18 + deleted := true 5.19 + err = context.UpdateProfile(profile.ID, ProfileChange{Deleted: &deleted}) 5.20 if err != nil { 5.21 t.Errorf("Error removing profile from %T: %s", store, err) 5.22 } 5.23 @@ -124,10 +128,6 @@ 5.24 if err != ErrProfileNotFound { 5.25 t.Errorf("Expected ErrProfileNotFound from %T, got %+v and %+v", store, retrieved, err) 5.26 } 5.27 - err = context.DeleteProfile(profile.ID) 5.28 - if err != ErrProfileNotFound { 5.29 - t.Errorf("Expected ErrProfileNotFound from %T, got %+v", store, err) 5.30 - } 5.31 } 5.32 } 5.33 5.34 @@ -155,6 +155,7 @@ 5.35 var passphraseScheme int 5.36 var compromised bool 5.37 5.38 + profile.ID = uuid.NewID() 5.39 change := ProfileChange{} 5.40 expectation := profile 5.41 result := profile 5.42 @@ -231,14 +232,6 @@ 5.43 if !match { 5.44 t.Errorf("Expected field `%s` to be `%v`, got `%v` from %T", field, expected, got, store) 5.45 } 5.46 - err = context.DeleteProfile(profile.ID) 5.47 - if err != nil { 5.48 - t.Errorf("Error deleting profile from %T: %s", store, err) 5.49 - } 5.50 - err = context.UpdateProfile(profile.ID, change) 5.51 - if err != ErrProfileNotFound { 5.52 - t.Errorf("Expected ErrProfileNotFound, got %v from %T", err, store) 5.53 - } 5.54 } 5.55 } 5.56 } 5.57 @@ -464,3 +457,4 @@ 5.58 5.59 // BUG(paddy): We need to test the validateNewProfileRequest helper. 5.60 // BUG(paddy): We need to test the CreateProfileHandler. 5.61 +// BUG(paddy): We need to test that deleting works as we expect.