auth

Paddy 2015-04-07 Parent:379702564771 Child:48200d8c4036

155:762953f6a7f2 Go to Latest

auth/profile_postgres.go

Implement postgres version of the tokenStore. Create a postgres implementation for the tokenStore. Note that because pq doesn't support Postgres' array types (see https://github.com/lib/pq/issues/49), we couldn't just store the token.Scopes field as a Postgres array of varchars, which would have been the ideal. Instead, we need a many-to-many table that maps tokens to scopes. This meant we needed a special tokenScope type for our database mapping, and we needed to complicate the token storage/retrieval functions a little bit. It's kind of ugly, I'm not a huge fan of it, and I'd much rather be using the Postgres array types, but... well, here we are. We also added the postgres tokenStore to our slice of tokenStores to test when the correct environment variables are present. We wrote initialization SQL for the tables required by the postgres tokenStore. Also, added a helper script for emptying the test database, because I got tired of doing it by hand. We should be doing that in an automated fashion in the tests themselves, but that would mean extending the *Store interfaces.

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@148 176 func (p *postgres) removeLoginSQL(value string, profile uuid.ID) *pan.Query {
paddy@148 177 var login Login
paddy@148 178 query := pan.New(pan.POSTGRES, "DELETE FROM "+pan.GetTableName(login))
paddy@148 179 query.IncludeWhere()
paddy@150 180 query.Include(pan.GetUnquotedColumn(login, "Value")+" = ? AND "+pan.GetUnquotedColumn(login, "ProfileID")+" = ?", value, profile)
paddy@148 181 return query.FlushExpressions(" ")
paddy@148 182 }
paddy@148 183
paddy@148 184 func (p *postgres) removeLogin(value string, profile uuid.ID) error {
paddy@148 185 query := p.removeLoginSQL(value, profile)
paddy@149 186 res, err := p.db.Exec(query.String(), query.Args...)
paddy@149 187 if err != nil {
paddy@149 188 return err
paddy@149 189 }
paddy@149 190 rows, err := res.RowsAffected()
paddy@149 191 if err != nil {
paddy@149 192 return err
paddy@149 193 }
paddy@149 194 if rows == 0 {
paddy@149 195 return ErrLoginNotFound
paddy@149 196 }
paddy@149 197 return nil
paddy@148 198 }
paddy@148 199
paddy@148 200 func (p *postgres) recordLoginUseSQL(value string, when time.Time) *pan.Query {
paddy@148 201 var login Login
paddy@148 202 query := pan.New(pan.POSTGRES, "UPDATE "+pan.GetTableName(login)+" SET ")
paddy@150 203 query.Include(pan.GetUnquotedColumn(login, "LastUsed")+" = ?", when)
paddy@148 204 query.IncludeWhere()
paddy@150 205 query.Include(pan.GetUnquotedColumn(login, "Value")+" = ?", value)
paddy@148 206 return query.FlushExpressions(" ")
paddy@148 207 }
paddy@148 208
paddy@148 209 func (p *postgres) recordLoginUse(value string, when time.Time) error {
paddy@148 210 query := p.recordLoginUseSQL(value, when)
paddy@148 211 _, err := p.db.Exec(query.String(), query.Args...)
paddy@148 212 return err
paddy@148 213 }
paddy@148 214
paddy@148 215 func (p *postgres) listLoginsSQL(profile uuid.ID, num, offset int) *pan.Query {
paddy@148 216 var login Login
paddy@149 217 fields, _ := pan.GetFields(login)
paddy@148 218 query := pan.New(pan.POSTGRES, "SELECT "+pan.QueryList(fields)+" FROM "+pan.GetTableName(login))
paddy@148 219 query.IncludeWhere()
paddy@150 220 query.Include(pan.GetUnquotedColumn(login, "ProfileID")+" = ?", profile)
paddy@148 221 query.IncludeLimit(int64(num))
paddy@148 222 query.IncludeOffset(int64(offset))
paddy@148 223 return query.FlushExpressions(" ")
paddy@148 224 }
paddy@148 225
paddy@148 226 func (p *postgres) listLogins(profile uuid.ID, num, offset int) ([]Login, error) {
paddy@148 227 query := p.listLoginsSQL(profile, num, offset)
paddy@148 228 rows, err := p.db.Query(query.String(), query.Args...)
paddy@148 229 if err != nil {
paddy@148 230 return []Login{}, err
paddy@148 231 }
paddy@148 232 var logins []Login
paddy@148 233 for rows.Next() {
paddy@148 234 var login Login
paddy@148 235 err = pan.Unmarshal(rows, &login)
paddy@148 236 if err != nil {
paddy@148 237 return logins, err
paddy@148 238 }
paddy@148 239 logins = append(logins, login)
paddy@148 240 }
paddy@148 241 if err := rows.Err(); err != nil {
paddy@148 242 return logins, err
paddy@148 243 }
paddy@148 244 return logins, nil
paddy@148 245 }