From 9571ccc4d87907067df98edeaa78f0c167fcff43 Mon Sep 17 00:00:00 2001 From: sanine Date: Sat, 13 May 2023 22:32:24 -0500 Subject: begin total refactor --- db/db.go | 207 --------------------------------------------------------- db/endpoint.go | 96 -------------------------- db/go.mod | 8 --- db/go.sum | 4 -- db/session.go | 139 -------------------------------------- db/user.go | 165 --------------------------------------------- 6 files changed, 619 deletions(-) delete mode 100644 db/db.go delete mode 100644 db/endpoint.go delete mode 100644 db/go.mod delete mode 100644 db/go.sum delete mode 100644 db/session.go delete mode 100644 db/user.go (limited to 'db') diff --git a/db/db.go b/db/db.go deleted file mode 100644 index 55ef81b..0000000 --- a/db/db.go +++ /dev/null @@ -1,207 +0,0 @@ -package db - -import ( - "os" - "time" - "io/fs" - "errors" - "database/sql" - _ "github.com/mattn/go-sqlite3" -) - - -type User struct { - Id int - Name string - PasswordHash []byte - Salt []byte -} - - -type Session struct { - Id string - UserId int - Created time.Time - Modified time.Time -} - -type Endpoint struct { - Id int - Name string - Path string - Address string -} - - -type Model interface { - Create(filename string) error - Open(filename string) error - Close() error - - GetSchemaVersion() (int, error) - SetHostAddress(addr string) error - GetHostAddress() (string, error) - SetLoginPage(path string) error - GetLoginPage() (string, error) - - CreateUser(username, password string) (User, error) - DeleteUser(user User) error - SetPassword(user User, password string) error - AuthenticateUser(username, password string) (User, error) - GetByUsername(username string) (User, error) - GetById(id string) (User, error) - AllUsers() ([]User, error) - - CreateSession(user User) (Session, error) - DeleteSession(session Session) error - TouchSession(session Session) error - CheckSession(session Session) (bool, error) - CheckSessionId(id string) (bool, error) - CleanSessions(maxIdle time.Duration) error - AllSessions() ([]Session, error) - - CreateEndpoint(name, path, address string) (Endpoint, error) - DeleteEndpoint(endpoint Endpoint) error - SetEndpointPath(endpoint Endpoint, path string) error - SetEndpointAddress(endpoint Endpoint, address string) error - GetEndpointByName(name string) (Endpoint, error) - GetEndpointByPath(path string) (Endpoint, error) - GetEndpointByAddress(address string) (Endpoint, error) - AllEndpoints() ([]Endpoint, error) -} - - -// interface to wrap sql.Row and sql.Rows -type Scanner interface { - Scan(dest ...any) error -} - - -type Phlox struct { - db *sql.DB -} - - -func fileExists(filename string) (bool, error) { - _, err := os.Stat(filename) - if err == nil { - return true, nil - } else if errors.Is(err, fs.ErrNotExist) { - return false, nil - } else { - // unknown error - return false, err - } -} - - -func (p *Phlox) Create(filename string) error { - exist, err := fileExists(filename) - if err != nil { - return err - } - - if exist { - // file already exists - return fs.ErrExist - } - - p.db, err = sql.Open("sqlite3", filename) - if err != nil { - return err - } - - _, err = p.db.Exec(` - create table schema(version integer); - insert into schema values(0); - - create table config(key string primary key, value string); - insert into config values("hostaddress", "localhost:3333"); - insert into config values("loginpage", ""); - - create table users (userid integer not null primary key, username string unique, passwordhash string, salt string); - create table sessions (sessionid string not null primary key, userid integer, created string, modified string, foreign key(userid) references users(userid)); - create table endpoints (endpointid integer not null primary key, name string, path string, address string); - `) - if err != nil { - return err - } - - return nil -} - - -func (p *Phlox) Open(filename string) error { - exist, err := fileExists(filename) - if err != nil { - return err - } - - if !exist { - // no such file! - return fs.ErrNotExist - } - - p.db, err = sql.Open("sqlite3", filename) - if err != nil { - return err - } - - return nil -} - - -func (p *Phlox) Close() error { - err := p.db.Close() - return err -} - - -func (p *Phlox) GetSchemaVersion() (int, error) { - row := p.db.QueryRow("select max(version) from schema;") - if row.Err() != nil { - return -1, row.Err() - } - - var version int - err := row.Scan(&version) - if err != nil { - return -1, err - } - - return version, nil -} - - -func (p *Phlox) SetHostAddress(addr string) error { - _, err := p.db.Exec("update config set value=? where key='hostaddress';", addr) - return err -} - - -func (p *Phlox) GetHostAddress() (string, error) { - row := p.db.QueryRow("select value from config where key='hostaddress';") - var addr string - err := row.Scan(&addr) - if err != nil { - return "", err - } - return addr, nil -} - - -func (p *Phlox) SetLoginPage(path string) error { - _, err := p.db.Exec("update config set value=? where key='loginpage';", path) - return err -} - - -func (p *Phlox) GetLoginPage() (string, error) { - row := p.db.QueryRow("select value from config where key='loginpage';") - var path string - err := row.Scan(&path) - if err != nil { - return "", err - } - return path, nil -} diff --git a/db/endpoint.go b/db/endpoint.go deleted file mode 100644 index d880510..0000000 --- a/db/endpoint.go +++ /dev/null @@ -1,96 +0,0 @@ -package db - -func (p *Phlox) CreateEndpoint(name, path, address string) (Endpoint, error) { - var id int - row := p.db.QueryRow("select coalesce(max(endpointid), 0) from endpoints;") - err := row.Scan(&id) - if err != nil { - return Endpoint{}, err - } - id += 1 - - _, err = p.db.Exec( - "insert into endpoints values (?, ?, ?, ?)", - id, name, path, address, - ) - if err != nil { - return Endpoint{}, err - } - - endpoint := Endpoint{ - Id: id, - Name: name, - Path: path, - Address: address, - } - - return endpoint, nil -} - - -func (p *Phlox) DeleteEndpoint(endpoint Endpoint) error { - _, err := p.db.Exec("delete from endpoints where endpointid = ?;", endpoint.Id) - return err -} - - -func extractEndpoint(s Scanner) (Endpoint, error) { - var endpoint Endpoint - err := s.Scan( - &endpoint.Id, - &endpoint.Name, - &endpoint.Path, - &endpoint.Address, - ) - return endpoint, err -} - - -func (p *Phlox) SetEndpointPath(endpoint Endpoint, path string) error { - _, err := p.db.Exec("update endpoints set path=? where endpointid=?", path, endpoint.Id) - return err -} - -func (p *Phlox) SetEndpointAddress(endpoint Endpoint, address string) error { - _, err := p.db.Exec("update endpoints set address=? where endpointid=?", address, endpoint.Id) - return err -} - - -func queryEndpoint(p *Phlox, query, param string) (Endpoint, error) { - row := p.db.QueryRow(query, param) - endpoint, err := extractEndpoint(row) - return endpoint, err -} - -func (p *Phlox) GetEndpointByName(name string) (Endpoint, error) { - return queryEndpoint(p, "select * from endpoints where name = ?;", name) -} - -func (p *Phlox) GetEndpointByPath(path string) (Endpoint, error) { - return queryEndpoint(p, "select * from endpoints where path = ?;", path) -} - -func (p *Phlox) GetEndpointByAddress(address string) (Endpoint, error) { - return queryEndpoint(p, "select * from endpoints where address = ?;", address) -} - - -func (p *Phlox) AllEndpoints() ([]Endpoint, error) { - endpoints := make([]Endpoint, 0) - rows, err := p.db.Query("select * from endpoints;") - if err != nil { - return endpoints, err - } - defer rows.Close() - - for rows.Next() { - endpoint, err := extractEndpoint(rows) - if err != nil { - return endpoints, err - } - endpoints = append(endpoints, endpoint) - } - - return endpoints, nil -} diff --git a/db/go.mod b/db/go.mod deleted file mode 100644 index 7931102..0000000 --- a/db/go.mod +++ /dev/null @@ -1,8 +0,0 @@ -module sanine.net/git/phlox/db - -go 1.19 - -require ( - github.com/mattn/go-sqlite3 v1.14.16 - golang.org/x/crypto v0.8.0 -) diff --git a/db/go.sum b/db/go.sum deleted file mode 100644 index 48de3bc..0000000 --- a/db/go.sum +++ /dev/null @@ -1,4 +0,0 @@ -github.com/mattn/go-sqlite3 v1.14.16 h1:yOQRA0RpS5PFz/oikGwBEqvAWhWg5ufRz4ETLjwpU1Y= -github.com/mattn/go-sqlite3 v1.14.16/go.mod h1:2eHXhiwb8IkHr+BDWZGa96P6+rkvnG63S2DGjv9HUNg= -golang.org/x/crypto v0.8.0 h1:pd9TJtTueMTVQXzk8E2XESSMQDj/U7OUu0PqJqPXQjQ= -golang.org/x/crypto v0.8.0/go.mod h1:mRqEX+O9/h5TFCrQhkgjo2yKi0yYA+9ecGkdQoHrywE= diff --git a/db/session.go b/db/session.go deleted file mode 100644 index 7ebb6df..0000000 --- a/db/session.go +++ /dev/null @@ -1,139 +0,0 @@ -package db - -import ( - "database/sql" - "crypto/rand" - "encoding/base64" - "errors" - "time" -) - - -func (p *Phlox) CreateSession(user User) (Session, error) { - bytes := make([]byte, 32) - _, err := rand.Read(bytes) - if err != nil { - return Session{}, err - } - - sessionid := base64.StdEncoding.EncodeToString(bytes) - userid := user.Id - now := time.Now().UTC() - nowStr := now.Format(time.RFC3339) - - _, err = p.db.Exec( - "insert into sessions (sessionid, userid, created, modified) values (?, ?, ?, ?);", - sessionid, - userid, - nowStr, nowStr, - ) - if err != nil { - return Session{}, err - } - - return Session{ - Id: sessionid, - UserId: userid, - Created: now, - Modified: now, - }, nil -} - - -func (p *Phlox) DeleteSession(session Session) error { - _, err := p.db.Exec("delete from sessions where sessionid = ?;", session.Id) - return err -} - - -func extractSession(s Scanner) (Session, error) { - var ( - session Session - createdStr string - modifiedStr string - ) - - // scan - err := s.Scan(&session.Id, &session.UserId, &createdStr, &modifiedStr) - if err != nil { - return Session{}, err - } - - // parse times - session.Created, err = time.Parse(time.RFC3339, createdStr) - if err != nil { - return Session{}, err - } - session.Modified, err = time.Parse(time.RFC3339, modifiedStr) - if err != nil { - return Session{}, err - } - - return session, nil -} - - -func (p *Phlox) CheckSession(session Session) (bool, error) { - row := p.db.QueryRow("select * from sessions where sessionid = ?", session.Id) - session, err := extractSession(row) - if err != nil { - if errors.Is(err, sql.ErrNoRows) { - // no row returned, so invalid session - return false, nil - } else { - // some other error - return false, err - } - } - - return true, nil -} - - - -func (p *Phlox) CheckSessionId(id string) (bool, error) { - session := Session{ Id: id } - return p.CheckSession(session) -} - - -func (p *Phlox) TouchSession(session Session) error { - now := time.Now().UTC().Format(time.RFC3339) - _, err := p.db.Exec( - "update sessions set modified = ? where sessionid = ?;", - now, session.Id, - ) - return err -} - - -func (p *Phlox) TouchSessionId(id string) error { - return p.TouchSession(Session{ Id: id }) -} - - -func (p *Phlox) CleanSessions(maxIdle time.Duration) error { - expire := time.Now().UTC().Add(-maxIdle).Format(time.RFC3339) - _, err := p.db.Exec("delete from sessions where modified < ?;", expire) - return err -} - - -func (p *Phlox) AllSessions() ([]Session, error) { - sessions := make([]Session, 0) - rows, err := p.db.Query("select * from sessions;") - if err != nil { - return sessions, err - } - defer rows.Close() - - for rows.Next() { - session, err := extractSession(rows) - if err != nil { - return sessions, err - } - sessions = append(sessions, session) - } - - return sessions, nil -} diff --git a/db/user.go b/db/user.go deleted file mode 100644 index 1aff73f..0000000 --- a/db/user.go +++ /dev/null @@ -1,165 +0,0 @@ -package db - -import ( - "golang.org/x/crypto/argon2" - "crypto/rand" - "encoding/base64" - "database/sql" -) - - -func getNextUserId(db *sql.DB) (int, error) { - row := db.QueryRow("select coalesce(max(userid), 0) from users;") - if row.Err() != nil { - return -1, row.Err() - } - - var id int - err := row.Scan(&id) - if err != nil { - return -1, err - } - return id+1, nil -} - - -func hashPassword(password string, salt []byte) []byte { - return argon2.IDKey([]byte(password), salt, 1, 64*1024, 4, 32) -} - - -func (p *Phlox) CreateUser(username, password string) (User, error) { - user := User{} - - userId, err := getNextUserId(p.db) - if err != nil { - return user, err - } - - salt := make([]byte, 10) - _, err = rand.Read(salt) - if err != nil { - return user, err - } - - hash := hashPassword(password, salt) - - hash64 := base64.StdEncoding.EncodeToString(hash) - salt64 := base64.StdEncoding.EncodeToString(salt) - - _, err = p.db.Exec("insert into users (userid, username, passwordhash, salt) values (?, ?, ?, ?)", userId, username, hash64, salt64) - if err != nil { - return user, err - } - - user.Id = userId - user.Name = username - user.PasswordHash = hash - user.Salt = salt - - return user, nil -} - - - -func (p *Phlox) DeleteUser(user User) error { - _, err := p.db.Exec("delete from users where userid=?;", user.Id) - return err -} - - - -func (p *Phlox) SetPassword(user User, password string) error { - hash := hashPassword(password, user.Salt) - hash64 := base64.StdEncoding.EncodeToString(hash) - - _, err := p.db.Exec("update users set passwordhash=? where userid=?;", hash64, user.Id) - return err -} - - - -func extractUser(s Scanner) (User, error) { - var userid int - var username string - var hash64 string - var salt64 string - err := s.Scan(&userid, &username, &hash64, &salt64) - if err != nil { - return User{}, err - } - - hash, err := base64.StdEncoding.DecodeString(hash64) - if err != nil { - return User{}, err - } - salt, err := base64.StdEncoding.DecodeString(salt64) - if err != nil { - return User{}, err - } - - user := User{ - Id: userid, - Name: username, - PasswordHash: hash, - Salt: salt, - } - - return user, nil -} - - -func (p *Phlox) AuthenticateUser(username, password string) (bool, User, error) { - row := p.db.QueryRow("select * from users where username = ?;", username) - user, err := extractUser(row) - if err != nil { - return false, User{}, err - } - - hash := hashPassword(password, user.Salt) - for i, v := range user.PasswordHash { - if v != hash[i] { return false, user, nil; } - } - return true, user, nil -} - - -func (p *Phlox) GetByUsername(username string) (User, error) { - row := p.db.QueryRow("select * from users where username = ?;", username) - user, err := extractUser(row) - if err != nil { - return User{}, err - } - return user, nil -} - - -func (p *Phlox) GetById(id int) (User, error) { - row := p.db.QueryRow("select * from users where userid = ?;", id) - user, err := extractUser(row) - if err != nil { - return User{}, err - } - - return user, nil -} - - -func (p *Phlox) AllUsers() ([]User, error) { - users := make([]User, 0) - rows, err := p.db.Query("select * from users;") - if err != nil { - return users, err - } - defer rows.Close() - - for rows.Next() { - user, err := extractUser(rows) - if err != nil { - return users, err - } - users = append(users, user) - } - - return users, nil -} -- cgit v1.2.1