auth
154:5f670aba87b4 Browse Files
Implement a session store in postgres. Write the postgres implementation of our sessionStore type. Write the SQL statements to initialize the database for us. Include the postgres implementation of our sessionStore type in our sessionStore tests when the appropriate environment variable is passed.
session_postgres.go session_test.go sql/postgres_init.sql
1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 1.2 +++ b/session_postgres.go Mon Apr 06 07:58:10 2015 -0400 1.3 @@ -0,0 +1,126 @@ 1.4 +package auth 1.5 + 1.6 +import ( 1.7 + "time" 1.8 + 1.9 + "code.secondbit.org/uuid.hg" 1.10 + 1.11 + "github.com/lib/pq" 1.12 + "github.com/secondbit/pan" 1.13 +) 1.14 + 1.15 +func (s Session) GetSQLTableName() string { 1.16 + return "sessions" 1.17 +} 1.18 + 1.19 +func (p *postgres) createSessionSQL(session Session) *pan.Query { 1.20 + fields, values := pan.GetFields(session) 1.21 + query := pan.New(pan.POSTGRES, "INSERT INTO "+pan.GetTableName(session)) 1.22 + query.Include("(" + pan.QueryList(fields) + ")") 1.23 + query.Include("VALUES") 1.24 + query.Include("("+pan.VariableList(len(values))+")", values...) 1.25 + return query.FlushExpressions(" ") 1.26 +} 1.27 + 1.28 +func (p *postgres) createSession(session Session) error { 1.29 + query := p.createSessionSQL(session) 1.30 + _, err := p.db.Exec(query.String(), query.Args...) 1.31 + if e, ok := err.(*pq.Error); ok && e.Constraint == "sessions_pkey" { 1.32 + err = ErrSessionAlreadyExists 1.33 + } 1.34 + return err 1.35 +} 1.36 + 1.37 +func (p *postgres) getSessionSQL(id string) *pan.Query { 1.38 + var session Session 1.39 + fields, _ := pan.GetFields(session) 1.40 + query := pan.New(pan.POSTGRES, "SELECT "+pan.QueryList(fields)+" FROM "+pan.GetTableName(session)) 1.41 + query.IncludeWhere() 1.42 + query.Include(pan.GetUnquotedColumn(session, "ID")+" = ?", id) 1.43 + return query.FlushExpressions(" ") 1.44 +} 1.45 + 1.46 +func (p *postgres) getSession(id string) (Session, error) { 1.47 + query := p.getSessionSQL(id) 1.48 + rows, err := p.db.Query(query.String(), query.Args...) 1.49 + if err != nil { 1.50 + return Session{}, err 1.51 + } 1.52 + var session Session 1.53 + var found bool 1.54 + for rows.Next() { 1.55 + err := pan.Unmarshal(rows, &session) 1.56 + if err != nil { 1.57 + return session, err 1.58 + } 1.59 + found = true 1.60 + } 1.61 + if err = rows.Err(); err != nil { 1.62 + return session, err 1.63 + } 1.64 + if !found { 1.65 + return session, ErrSessionNotFound 1.66 + } 1.67 + return session, nil 1.68 +} 1.69 + 1.70 +func (p *postgres) removeSessionSQL(id string) *pan.Query { 1.71 + var session Session 1.72 + query := pan.New(pan.POSTGRES, "DELETE FROM "+pan.GetTableName(session)) 1.73 + query.IncludeWhere() 1.74 + query.Include(pan.GetUnquotedColumn(session, "ID")+" = ?", id) 1.75 + return query.FlushExpressions(" ") 1.76 +} 1.77 + 1.78 +func (p *postgres) removeSession(id string) error { 1.79 + query := p.removeSessionSQL(id) 1.80 + res, err := p.db.Exec(query.String(), query.Args...) 1.81 + if err != nil { 1.82 + return err 1.83 + } 1.84 + rows, err := res.RowsAffected() 1.85 + if err != nil { 1.86 + return err 1.87 + } 1.88 + if rows < 1 { 1.89 + return ErrSessionNotFound 1.90 + } 1.91 + return nil 1.92 +} 1.93 + 1.94 +func (p *postgres) listSessionsSQL(profile uuid.ID, before time.Time, num int64) *pan.Query { 1.95 + var session Session 1.96 + fields, _ := pan.GetFields(session) 1.97 + query := pan.New(pan.POSTGRES, "SELECT "+pan.QueryList(fields)+" FROM "+pan.GetTableName(session)) 1.98 + query.IncludeWhere() 1.99 + query.Include(pan.GetUnquotedColumn(session, "ProfileID")+" = ?", profile) 1.100 + if !before.IsZero() { 1.101 + query.Include(pan.GetUnquotedColumn(session, "Created")+" < ?", before) 1.102 + } 1.103 + query.FlushExpressions(" AND ") 1.104 + if num > 0 { 1.105 + query.IncludeLimit(num) 1.106 + } 1.107 + return query.FlushExpressions(" ") 1.108 +} 1.109 + 1.110 +func (p *postgres) listSessions(profile uuid.ID, before time.Time, num int64) ([]Session, error) { 1.111 + query := p.listSessionsSQL(profile, before, num) 1.112 + rows, err := p.db.Query(query.String(), query.Args...) 1.113 + if err != nil { 1.114 + return []Session{}, err 1.115 + } 1.116 + var sessions []Session 1.117 + for rows.Next() { 1.118 + var session Session 1.119 + err := pan.Unmarshal(rows, &session) 1.120 + if err != nil { 1.121 + return sessions, err 1.122 + } 1.123 + sessions = append(sessions, session) 1.124 + } 1.125 + if err = rows.Err(); err != nil { 1.126 + return sessions, err 1.127 + } 1.128 + return sessions, nil 1.129 +}
2.1 --- a/session_test.go Tue Mar 24 21:50:42 2015 -0400 2.2 +++ b/session_test.go Mon Apr 06 07:58:10 2015 -0400 2.3 @@ -1,12 +1,23 @@ 2.4 package auth 2.5 2.6 import ( 2.7 + "os" 2.8 "testing" 2.9 "time" 2.10 2.11 "code.secondbit.org/uuid.hg" 2.12 ) 2.13 2.14 +func init() { 2.15 + if os.Getenv("PG_TEST_DB") != "" { 2.16 + p, err := NewPostgres(os.Getenv("PG_TEST_DB")) 2.17 + if err != nil { 2.18 + panic(err) 2.19 + } 2.20 + sessionStores = append(sessionStores, &p) 2.21 + } 2.22 +} 2.23 + 2.24 var sessionStores = []sessionStore{NewMemstore()} 2.25 2.26 func compareSessions(session1, session2 Session) (success bool, field string, val1, val2 interface{}) {
3.1 --- a/sql/postgres_init.sql Tue Mar 24 21:50:42 2015 -0400 3.2 +++ b/sql/postgres_init.sql Mon Apr 06 07:58:10 2015 -0400 3.3 @@ -46,3 +46,15 @@ 3.4 name VARCHAR(64) NOT NULL, 3.5 description TEXT NOT NULL 3.6 ); 3.7 + 3.8 +CREATE TABLE IF NOT EXISTS sessions ( 3.9 + id VARCHAR(72) PRIMARY KEY, 3.10 + ip VARCHAR(32) NOT NULL, 3.11 + user_agent TEXT NOT NULL, 3.12 + profile_id VARCHAR(36) NOT NULL, 3.13 + login VARCHAR(64) NOT NULL, 3.14 + created TIMESTAMPTZ NOT NULL, 3.15 + expires TIMESTAMPTZ NOT NULL, 3.16 + active BOOLEAN NOT NULL, 3.17 + csrftoken VARCHAR(72) NOT NULL 3.18 +);