auth
155:762953f6a7f2 Browse Files
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.
sql/postgres_empty.sql sql/postgres_init.sql token.go token_postgres.go token_test.go
1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 1.2 +++ b/sql/postgres_empty.sql Tue Apr 07 01:00:26 2015 -0400 1.3 @@ -0,0 +1,8 @@ 1.4 +TRUNCATE profiles; 1.5 +TRUNCATE logins; 1.6 +TRUNCATE clients; 1.7 +TRUNCATE endpoints; 1.8 +TRUNCATE scopes; 1.9 +TRUNCATE sessions; 1.10 +TRUNCATE tokens; 1.11 +TRUNCATE scopes_tokens;
2.1 --- a/sql/postgres_init.sql Mon Apr 06 07:58:10 2015 -0400 2.2 +++ b/sql/postgres_init.sql Tue Apr 07 01:00:26 2015 -0400 2.3 @@ -58,3 +58,22 @@ 2.4 active BOOLEAN NOT NULL, 2.5 csrftoken VARCHAR(72) NOT NULL 2.6 ); 2.7 + 2.8 +CREATE TABLE IF NOT EXISTS tokens ( 2.9 + access_token VARCHAR(36) PRIMARY KEY, 2.10 + refresh_token VARCHAR(36) UNIQUE NOT NULL, 2.11 + created TIMESTAMPTZ NOT NULL, 2.12 + created_from VARCHAR(128) NOT NULL, 2.13 + expires_in INTEGER NOT NULL, 2.14 + token_type VARCHAR(64) NOT NULL, 2.15 + profile_id VARCHAR(36) NOT NULL, 2.16 + client_id VARCHAR(36) NOT NULL, 2.17 + revoked BOOLEAN NOT NULL, 2.18 + refresh_revoked BOOLEAN NOT NULL 2.19 +); 2.20 + 2.21 +CREATE TABLE IF NOT EXISTS scopes_tokens ( 2.22 + token VARCHAR(36) NOT NULL, 2.23 + scope VARCHAR(64) NOT NULL, 2.24 + PRIMARY KEY(token, scope) 2.25 +);
3.1 --- a/token.go Mon Apr 06 07:58:10 2015 -0400 3.2 +++ b/token.go Tue Apr 07 01:00:26 2015 -0400 3.3 @@ -43,7 +43,7 @@ 3.4 CreatedFrom string 3.5 ExpiresIn int32 3.6 TokenType string 3.7 - Scopes []string 3.8 + Scopes []string `sql_column:"-"` 3.9 ProfileID uuid.ID 3.10 ClientID uuid.ID 3.11 Revoked bool
4.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 4.2 +++ b/token_postgres.go Tue Apr 07 01:00:26 2015 -0400 4.3 @@ -0,0 +1,214 @@ 4.4 +package auth 4.5 + 4.6 +import ( 4.7 + "code.secondbit.org/uuid.hg" 4.8 + 4.9 + "github.com/lib/pq" 4.10 + "github.com/secondbit/pan" 4.11 +) 4.12 + 4.13 +type tokenScope struct { 4.14 + Token string 4.15 + Scope string 4.16 +} 4.17 + 4.18 +func (t tokenScope) GetSQLTableName() string { 4.19 + return "scopes_tokens" 4.20 +} 4.21 + 4.22 +func (t Token) GetSQLTableName() string { 4.23 + return "tokens" 4.24 +} 4.25 + 4.26 +func (p *postgres) getTokenSQL(token string, refresh bool) *pan.Query { 4.27 + var t Token 4.28 + fields, _ := pan.GetFields(t) 4.29 + query := pan.New(pan.POSTGRES, "SELECT "+pan.QueryList(fields)+" FROM "+pan.GetTableName(t)) 4.30 + query.IncludeWhere() 4.31 + if !refresh { 4.32 + query.Include(pan.GetUnquotedColumn(t, "AccessToken")+" = ?", token) 4.33 + } else { 4.34 + query.Include(pan.GetUnquotedColumn(t, "RefreshToken")+" = ?", token) 4.35 + } 4.36 + return query.FlushExpressions(" ") 4.37 +} 4.38 + 4.39 +func (p *postgres) getToken(token string, refresh bool) (Token, error) { 4.40 + query := p.getTokenSQL(token, refresh) 4.41 + rows, err := p.db.Query(query.String(), query.Args...) 4.42 + if err != nil { 4.43 + return Token{}, err 4.44 + } 4.45 + var t Token 4.46 + var found bool 4.47 + for rows.Next() { 4.48 + err := pan.Unmarshal(rows, &t) 4.49 + if err != nil { 4.50 + return t, err 4.51 + } 4.52 + found = true 4.53 + } 4.54 + if err = rows.Err(); err != nil { 4.55 + return t, err 4.56 + } 4.57 + if !found { 4.58 + return t, ErrTokenNotFound 4.59 + } 4.60 + query = p.getTokenScopesSQL([]string{t.AccessToken}) 4.61 + rows, err = p.db.Query(query.String(), query.Args...) 4.62 + if err != nil { 4.63 + return t, err 4.64 + } 4.65 + for rows.Next() { 4.66 + var ts tokenScope 4.67 + err = pan.Unmarshal(rows, &ts) 4.68 + if err != nil { 4.69 + return t, err 4.70 + } 4.71 + t.Scopes = append(t.Scopes, ts.Scope) 4.72 + } 4.73 + if err = rows.Err(); err != nil { 4.74 + return t, err 4.75 + } 4.76 + return t, nil 4.77 +} 4.78 + 4.79 +func (p *postgres) saveTokenSQL(token Token) *pan.Query { 4.80 + fields, values := pan.GetFields(token) 4.81 + query := pan.New(pan.POSTGRES, "INSERT INTO "+pan.GetTableName(token)) 4.82 + query.Include("(" + pan.QueryList(fields) + ")") 4.83 + query.Include("VALUES") 4.84 + query.Include("("+pan.VariableList(len(values))+")", values...) 4.85 + return query.FlushExpressions(" ") 4.86 +} 4.87 + 4.88 +func (p *postgres) saveTokenScopesSQL(ts []tokenScope) *pan.Query { 4.89 + fields, _ := pan.GetFields(ts[0]) 4.90 + query := pan.New(pan.POSTGRES, "INSERT INTO "+pan.GetTableName(ts[0])) 4.91 + query.Include("(" + pan.QueryList(fields) + ")") 4.92 + query.Include("VALUES") 4.93 + query.FlushExpressions(" ") 4.94 + for _, t := range ts { 4.95 + _, values := pan.GetFields(t) 4.96 + query.Include("("+pan.VariableList(len(values))+")", values...) 4.97 + } 4.98 + return query.FlushExpressions(", ") 4.99 +} 4.100 + 4.101 +func (p *postgres) saveToken(token Token) error { 4.102 + query := p.saveTokenSQL(token) 4.103 + _, err := p.db.Exec(query.String(), query.Args...) 4.104 + if e, ok := err.(*pq.Error); ok && e.Constraint == "tokens_pkey" { 4.105 + err = ErrTokenAlreadyExists 4.106 + } 4.107 + if err != nil || len(token.Scopes) < 1 { 4.108 + return err 4.109 + } 4.110 + var ts []tokenScope 4.111 + for _, scope := range token.Scopes { 4.112 + ts = append(ts, tokenScope{Token: token.AccessToken, Scope: scope}) 4.113 + } 4.114 + query = p.saveTokenScopesSQL(ts) 4.115 + _, err = p.db.Exec(query.String(), query.Args...) 4.116 + return err 4.117 +} 4.118 + 4.119 +func (p *postgres) revokeTokenSQL(token string, refresh bool) *pan.Query { 4.120 + var t Token 4.121 + query := pan.New(pan.POSTGRES, "UPDATE "+pan.GetTableName(t)+" SET ") 4.122 + query.Include(pan.GetUnquotedColumn(t, "Revoked")+" = ?", true) 4.123 + query.IncludeWhere() 4.124 + if !refresh { 4.125 + query.Include(pan.GetUnquotedColumn(t, "AccessToken")+" = ?", token) 4.126 + } else { 4.127 + query.Include(pan.GetUnquotedColumn(t, "RefreshToken")+" = ?", token) 4.128 + } 4.129 + return query.FlushExpressions(" ") 4.130 +} 4.131 + 4.132 +func (p *postgres) revokeToken(token string, refresh bool) error { 4.133 + query := p.revokeTokenSQL(token, refresh) 4.134 + res, err := p.db.Exec(query.String(), query.Args...) 4.135 + if err != nil { 4.136 + return err 4.137 + } 4.138 + rows, err := res.RowsAffected() 4.139 + if err != nil { 4.140 + return err 4.141 + } 4.142 + if rows == 0 { 4.143 + return ErrTokenNotFound 4.144 + } 4.145 + return nil 4.146 +} 4.147 + 4.148 +func (p *postgres) getTokensByProfileIDSQL(profileID uuid.ID, num, offset int) *pan.Query { 4.149 + var token Token 4.150 + fields, _ := pan.GetFields(token) 4.151 + query := pan.New(pan.POSTGRES, "SELECT "+pan.QueryList(fields)+" FROM "+pan.GetTableName(token)) 4.152 + query.IncludeWhere() 4.153 + query.Include(pan.GetUnquotedColumn(token, "ProfileID")+" = ?", profileID) 4.154 + query.IncludeLimit(int64(num)) 4.155 + query.IncludeOffset(int64(offset)) 4.156 + return query.FlushExpressions(" ") 4.157 +} 4.158 + 4.159 +func (p *postgres) getTokenScopesSQL(tokens []string) *pan.Query { 4.160 + var t tokenScope 4.161 + fields, _ := pan.GetFields(t) 4.162 + tokensI := make([]interface{}, len(tokens)) 4.163 + for pos, token := range tokens { 4.164 + tokensI[pos] = token 4.165 + } 4.166 + query := pan.New(pan.POSTGRES, "SELECT "+pan.QueryList(fields)+" FROM "+pan.GetTableName(t)) 4.167 + query.IncludeWhere() 4.168 + query.Include(pan.GetUnquotedColumn(t, "Token")+" IN ("+pan.VariableList(len(tokensI))+")", tokensI...) 4.169 + return query.FlushExpressions(" ") 4.170 +} 4.171 + 4.172 +func (p *postgres) getTokensByProfileID(profileID uuid.ID, num, offset int) ([]Token, error) { 4.173 + query := p.getTokensByProfileIDSQL(profileID, num, offset) 4.174 + rows, err := p.db.Query(query.String(), query.Args...) 4.175 + if err != nil { 4.176 + return []Token{}, err 4.177 + } 4.178 + var tokens []Token 4.179 + var tokenIDs []string 4.180 + for rows.Next() { 4.181 + var token Token 4.182 + err = pan.Unmarshal(rows, &token) 4.183 + if err != nil { 4.184 + return tokens, err 4.185 + } 4.186 + tokens = append(tokens, token) 4.187 + tokenIDs = append(tokenIDs, token.AccessToken) 4.188 + } 4.189 + if err = rows.Err(); err != nil { 4.190 + return tokens, err 4.191 + } 4.192 + if len(tokenIDs) < 1 { 4.193 + return tokens, nil 4.194 + } 4.195 + scopes := map[string][]string{} 4.196 + query = p.getTokenScopesSQL(tokenIDs) 4.197 + rows, err = p.db.Query(query.String(), query.Args...) 4.198 + if err != nil { 4.199 + return tokens, err 4.200 + } 4.201 + for rows.Next() { 4.202 + var t tokenScope 4.203 + err = pan.Unmarshal(rows, &t) 4.204 + if err != nil { 4.205 + return tokens, err 4.206 + } 4.207 + scopes[t.Token] = append(scopes[t.Token], t.Scope) 4.208 + } 4.209 + if err = rows.Err(); err != nil { 4.210 + return tokens, err 4.211 + } 4.212 + for pos, token := range tokens { 4.213 + token.Scopes = scopes[token.AccessToken] 4.214 + tokens[pos] = token 4.215 + } 4.216 + return tokens, nil 4.217 +}
5.1 --- a/token_test.go Mon Apr 06 07:58:10 2015 -0400 5.2 +++ b/token_test.go Tue Apr 07 01:00:26 2015 -0400 5.3 @@ -1,12 +1,23 @@ 5.4 package auth 5.5 5.6 import ( 5.7 + "os" 5.8 "testing" 5.9 "time" 5.10 5.11 "code.secondbit.org/uuid.hg" 5.12 ) 5.13 5.14 +func init() { 5.15 + if os.Getenv("PG_TEST_DB") != "" { 5.16 + p, err := NewPostgres(os.Getenv("PG_TEST_DB")) 5.17 + if err != nil { 5.18 + panic(err) 5.19 + } 5.20 + tokenStores = append(tokenStores, &p) 5.21 + } 5.22 +} 5.23 + 5.24 var tokenStores = []tokenStore{NewMemstore()} 5.25 5.26 func compareTokens(token1, token2 Token) (success bool, field string, val1, val2 interface{}) {