aboutsummaryrefslogtreecommitdiff
path: root/userapi
diff options
context:
space:
mode:
Diffstat (limited to 'userapi')
-rw-r--r--userapi/api/api.go7
-rw-r--r--userapi/internal/api.go2
-rw-r--r--userapi/storage/devices/interface.go3
-rw-r--r--userapi/storage/devices/postgres/deltas/20201001204705_last_seen_ts_ip.sql13
-rw-r--r--userapi/storage/devices/postgres/devices_table.go34
-rw-r--r--userapi/storage/devices/postgres/storage.go13
-rw-r--r--userapi/storage/devices/sqlite3/deltas/20201001204705_last_seen_ts_ip.sql44
-rw-r--r--userapi/storage/devices/sqlite3/devices_table.go28
-rw-r--r--userapi/storage/devices/sqlite3/storage.go13
9 files changed, 140 insertions, 17 deletions
diff --git a/userapi/api/api.go b/userapi/api/api.go
index d384c5b1..6c3f3c69 100644
--- a/userapi/api/api.go
+++ b/userapi/api/api.go
@@ -192,6 +192,10 @@ type PerformDeviceCreationRequest struct {
DeviceID *string
// optional: if nil no display name will be associated with this device.
DeviceDisplayName *string
+ // IP address of this device
+ IPAddr string
+ // Useragent for this device
+ UserAgent string
}
// PerformDeviceCreationResponse is the response for PerformDeviceCreation
@@ -222,6 +226,9 @@ type Device struct {
// associated with access tokens.
SessionID int64
DisplayName string
+ LastSeenTS int64
+ LastSeenIP string
+ UserAgent string
}
// Account represents a Matrix account on this home server.
diff --git a/userapi/internal/api.go b/userapi/internal/api.go
index ec828439..81d00241 100644
--- a/userapi/internal/api.go
+++ b/userapi/internal/api.go
@@ -113,7 +113,7 @@ func (a *UserInternalAPI) PerformDeviceCreation(ctx context.Context, req *api.Pe
"device_id": req.DeviceID,
"display_name": req.DeviceDisplayName,
}).Info("PerformDeviceCreation")
- dev, err := a.DeviceDB.CreateDevice(ctx, req.Localpart, req.DeviceID, req.AccessToken, req.DeviceDisplayName)
+ dev, err := a.DeviceDB.CreateDevice(ctx, req.Localpart, req.DeviceID, req.AccessToken, req.DeviceDisplayName, req.IPAddr, req.UserAgent)
if err != nil {
return err
}
diff --git a/userapi/storage/devices/interface.go b/userapi/storage/devices/interface.go
index 168c84c5..9953ba06 100644
--- a/userapi/storage/devices/interface.go
+++ b/userapi/storage/devices/interface.go
@@ -31,10 +31,11 @@ type Database interface {
// an error will be returned.
// If no device ID is given one is generated.
// Returns the device on success.
- CreateDevice(ctx context.Context, localpart string, deviceID *string, accessToken string, displayName *string) (dev *api.Device, returnErr error)
+ CreateDevice(ctx context.Context, localpart string, deviceID *string, accessToken string, displayName *string, ipAddr, userAgent string) (dev *api.Device, returnErr error)
UpdateDevice(ctx context.Context, localpart, deviceID string, displayName *string) error
RemoveDevice(ctx context.Context, deviceID, localpart string) error
RemoveDevices(ctx context.Context, localpart string, devices []string) error
// RemoveAllDevices deleted all devices for this user. Returns the devices deleted.
RemoveAllDevices(ctx context.Context, localpart, exceptDeviceID string) (devices []api.Device, err error)
+ UpdateDeviceLastSeen(ctx context.Context, deviceID, ipAddr string) error
}
diff --git a/userapi/storage/devices/postgres/deltas/20201001204705_last_seen_ts_ip.sql b/userapi/storage/devices/postgres/deltas/20201001204705_last_seen_ts_ip.sql
new file mode 100644
index 00000000..4f5f2b17
--- /dev/null
+++ b/userapi/storage/devices/postgres/deltas/20201001204705_last_seen_ts_ip.sql
@@ -0,0 +1,13 @@
+-- +goose Up
+-- +goose StatementBegin
+ALTER TABLE device_devices ADD COLUMN IF NOT EXISTS last_seen_ts BIGINT NOT NULL;
+ALTER TABLE device_devices ADD COLUMN IF NOT EXISTS ip TEXT;
+ALTER TABLE device_devices ADD COLUMN IF NOT EXISTS user_agent TEXT;
+-- +goose StatementEnd
+
+-- +goose Down
+-- +goose StatementBegin
+ALTER TABLE device_devices DROP COLUMN last_seen_ts;
+ALTER TABLE device_devices DROP COLUMN ip;
+ALTER TABLE device_devices DROP COLUMN user_agent;
+-- +goose StatementEnd
diff --git a/userapi/storage/devices/postgres/devices_table.go b/userapi/storage/devices/postgres/devices_table.go
index c06af754..2a4d337c 100644
--- a/userapi/storage/devices/postgres/devices_table.go
+++ b/userapi/storage/devices/postgres/devices_table.go
@@ -51,8 +51,15 @@ CREATE TABLE IF NOT EXISTS device_devices (
-- When this devices was first recognised on the network, as a unix timestamp (ms resolution).
created_ts BIGINT NOT NULL,
-- The display name, human friendlier than device_id and updatable
- display_name TEXT
- -- TODO: device keys, device display names, last used ts and IP address?, token restrictions (if 3rd-party OAuth app)
+ display_name TEXT,
+ -- The time the device was last used, as a unix timestamp (ms resolution).
+ last_seen_ts BIGINT NOT NULL,
+ -- The last seen IP address of this device
+ ip TEXT,
+ -- User agent of this device
+ user_agent TEXT
+
+ -- TODO: device keys, device display names, token restrictions (if 3rd-party OAuth app)
);
-- Device IDs must be unique for a given user.
@@ -60,7 +67,7 @@ CREATE UNIQUE INDEX IF NOT EXISTS device_localpart_id_idx ON device_devices(loca
`
const insertDeviceSQL = "" +
- "INSERT INTO device_devices(device_id, localpart, access_token, created_ts, display_name) VALUES ($1, $2, $3, $4, $5)" +
+ "INSERT INTO device_devices(device_id, localpart, access_token, created_ts, display_name, last_seen_ts, ip, user_agent) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)" +
" RETURNING session_id"
const selectDeviceByTokenSQL = "" +
@@ -87,6 +94,9 @@ const deleteDevicesSQL = "" +
const selectDevicesByIDSQL = "" +
"SELECT device_id, localpart, display_name FROM device_devices WHERE device_id = ANY($1)"
+const updateDeviceLastSeen = "" +
+ "UPDATE device_devices SET last_seen_ts = $1, ip = $2 WHERE device_id = $3"
+
type devicesStatements struct {
insertDeviceStmt *sql.Stmt
selectDeviceByTokenStmt *sql.Stmt
@@ -94,6 +104,7 @@ type devicesStatements struct {
selectDevicesByLocalpartStmt *sql.Stmt
selectDevicesByIDStmt *sql.Stmt
updateDeviceNameStmt *sql.Stmt
+ updateDeviceLastSeenStmt *sql.Stmt
deleteDeviceStmt *sql.Stmt
deleteDevicesByLocalpartStmt *sql.Stmt
deleteDevicesStmt *sql.Stmt
@@ -132,6 +143,9 @@ func (s *devicesStatements) prepare(db *sql.DB, server gomatrixserverlib.ServerN
if s.selectDevicesByIDStmt, err = db.Prepare(selectDevicesByIDSQL); err != nil {
return
}
+ if s.updateDeviceLastSeenStmt, err = db.Prepare(updateDeviceLastSeen); err != nil {
+ return
+ }
s.serverName = server
return
}
@@ -141,12 +155,12 @@ func (s *devicesStatements) prepare(db *sql.DB, server gomatrixserverlib.ServerN
// Returns the device on success.
func (s *devicesStatements) insertDevice(
ctx context.Context, txn *sql.Tx, id, localpart, accessToken string,
- displayName *string,
+ displayName *string, ipAddr, userAgent string,
) (*api.Device, error) {
createdTimeMS := time.Now().UnixNano() / 1000000
var sessionID int64
stmt := sqlutil.TxStmt(txn, s.insertDeviceStmt)
- if err := stmt.QueryRowContext(ctx, id, localpart, accessToken, createdTimeMS, displayName).Scan(&sessionID); err != nil {
+ if err := stmt.QueryRowContext(ctx, id, localpart, accessToken, createdTimeMS, displayName, createdTimeMS, ipAddr, userAgent).Scan(&sessionID); err != nil {
return nil, err
}
return &api.Device{
@@ -154,6 +168,9 @@ func (s *devicesStatements) insertDevice(
UserID: userutil.MakeUserID(localpart, s.serverName),
AccessToken: accessToken,
SessionID: sessionID,
+ LastSeenTS: createdTimeMS,
+ LastSeenIP: ipAddr,
+ UserAgent: userAgent,
}, nil
}
@@ -280,3 +297,10 @@ func (s *devicesStatements) selectDevicesByLocalpart(
return devices, rows.Err()
}
+
+func (s *devicesStatements) updateDeviceLastSeen(ctx context.Context, txn *sql.Tx, deviceID, ipAddr string) error {
+ lastSeenTs := time.Now().UnixNano() / 1000000
+ stmt := sqlutil.TxStmt(txn, s.updateDeviceLastSeenStmt)
+ _, err := stmt.ExecContext(ctx, lastSeenTs, ipAddr, deviceID)
+ return err
+}
diff --git a/userapi/storage/devices/postgres/storage.go b/userapi/storage/devices/postgres/storage.go
index c5bd5b6c..faa5796b 100644
--- a/userapi/storage/devices/postgres/storage.go
+++ b/userapi/storage/devices/postgres/storage.go
@@ -83,7 +83,7 @@ func (d *Database) GetDevicesByID(ctx context.Context, deviceIDs []string) ([]ap
// Returns the device on success.
func (d *Database) CreateDevice(
ctx context.Context, localpart string, deviceID *string, accessToken string,
- displayName *string,
+ displayName *string, ipAddr, userAgent string,
) (dev *api.Device, returnErr error) {
if deviceID != nil {
returnErr = sqlutil.WithTransaction(d.db, func(txn *sql.Tx) error {
@@ -93,7 +93,7 @@ func (d *Database) CreateDevice(
return err
}
- dev, err = d.devices.insertDevice(ctx, txn, *deviceID, localpart, accessToken, displayName)
+ dev, err = d.devices.insertDevice(ctx, txn, *deviceID, localpart, accessToken, displayName, ipAddr, userAgent)
return err
})
} else {
@@ -108,7 +108,7 @@ func (d *Database) CreateDevice(
returnErr = sqlutil.WithTransaction(d.db, func(txn *sql.Tx) error {
var err error
- dev, err = d.devices.insertDevice(ctx, txn, newDeviceID, localpart, accessToken, displayName)
+ dev, err = d.devices.insertDevice(ctx, txn, newDeviceID, localpart, accessToken, displayName, ipAddr, userAgent)
return err
})
if returnErr == nil {
@@ -189,3 +189,10 @@ func (d *Database) RemoveAllDevices(
})
return
}
+
+// UpdateDeviceLastSeen updates a the last seen timestamp and the ip address
+func (d *Database) UpdateDeviceLastSeen(ctx context.Context, deviceID, ipAddr string) error {
+ return sqlutil.WithTransaction(d.db, func(txn *sql.Tx) error {
+ return d.devices.updateDeviceLastSeen(ctx, txn, deviceID, ipAddr)
+ })
+}
diff --git a/userapi/storage/devices/sqlite3/deltas/20201001204705_last_seen_ts_ip.sql b/userapi/storage/devices/sqlite3/deltas/20201001204705_last_seen_ts_ip.sql
new file mode 100644
index 00000000..887f90e0
--- /dev/null
+++ b/userapi/storage/devices/sqlite3/deltas/20201001204705_last_seen_ts_ip.sql
@@ -0,0 +1,44 @@
+-- +goose Up
+-- +goose StatementBegin
+ALTER TABLE device_devices RENAME TO device_devices_tmp;
+CREATE TABLE device_devices (
+ access_token TEXT PRIMARY KEY,
+ session_id INTEGER,
+ device_id TEXT ,
+ localpart TEXT ,
+ created_ts BIGINT,
+ display_name TEXT,
+ last_seen_ts BIGINT,
+ ip TEXT,
+ user_agent TEXT,
+ UNIQUE (localpart, device_id)
+);
+INSERT
+INTO device_devices (
+ access_token, session_id, device_id, localpart, created_ts, display_name, last_seen_ts, ip, user_agent
+) SELECT
+ access_token, session_id, device_id, localpart, created_ts, display_name, created_ts, '', ''
+FROM device_devices_tmp;
+DROP TABLE device_devices_tmp;
+-- +goose StatementEnd
+
+-- +goose Down
+-- +goose StatementBegin
+ALTER TABLE device_devices RENAME TO device_devices_tmp;
+CREATE TABLE IF NOT EXISTS device_devices (
+ access_token TEXT PRIMARY KEY,
+ session_id INTEGER,
+ device_id TEXT ,
+ localpart TEXT ,
+ created_ts BIGINT,
+ display_name TEXT,
+ UNIQUE (localpart, device_id)
+);
+INSERT
+INTO device_devices (
+ access_token, session_id, device_id, localpart, created_ts, display_name
+) SELECT
+ access_token, session_id, device_id, localpart, created_ts, display_name
+FROM device_devices_tmp;
+DROP TABLE device_devices_tmp;
+-- +goose StatementEnd \ No newline at end of file
diff --git a/userapi/storage/devices/sqlite3/devices_table.go b/userapi/storage/devices/sqlite3/devices_table.go
index c75e1982..6b0de10e 100644
--- a/userapi/storage/devices/sqlite3/devices_table.go
+++ b/userapi/storage/devices/sqlite3/devices_table.go
@@ -40,14 +40,17 @@ CREATE TABLE IF NOT EXISTS device_devices (
localpart TEXT ,
created_ts BIGINT,
display_name TEXT,
+ last_seen_ts BIGINT,
+ ip TEXT,
+ user_agent TEXT,
UNIQUE (localpart, device_id)
);
`
const insertDeviceSQL = "" +
- "INSERT INTO device_devices (device_id, localpart, access_token, created_ts, display_name, session_id)" +
- " VALUES ($1, $2, $3, $4, $5, $6)"
+ "INSERT INTO device_devices (device_id, localpart, access_token, created_ts, display_name, session_id, last_seen_ts, ip, user_agent)" +
+ " VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)"
const selectDevicesCountSQL = "" +
"SELECT COUNT(access_token) FROM device_devices"
@@ -76,6 +79,9 @@ const deleteDevicesSQL = "" +
const selectDevicesByIDSQL = "" +
"SELECT device_id, localpart, display_name FROM device_devices WHERE device_id IN ($1)"
+const updateDeviceLastSeen = "" +
+ "UPDATE device_devices SET last_seen_ts = $1, ip = $2 WHERE device_id = $3"
+
type devicesStatements struct {
db *sql.DB
writer sqlutil.Writer
@@ -86,6 +92,7 @@ type devicesStatements struct {
selectDevicesByIDStmt *sql.Stmt
selectDevicesByLocalpartStmt *sql.Stmt
updateDeviceNameStmt *sql.Stmt
+ updateDeviceLastSeenStmt *sql.Stmt
deleteDeviceStmt *sql.Stmt
deleteDevicesByLocalpartStmt *sql.Stmt
serverName gomatrixserverlib.ServerName
@@ -125,6 +132,9 @@ func (s *devicesStatements) prepare(db *sql.DB, writer sqlutil.Writer, server go
if s.selectDevicesByIDStmt, err = db.Prepare(selectDevicesByIDSQL); err != nil {
return
}
+ if s.updateDeviceLastSeenStmt, err = db.Prepare(updateDeviceLastSeen); err != nil {
+ return
+ }
s.serverName = server
return
}
@@ -134,7 +144,7 @@ func (s *devicesStatements) prepare(db *sql.DB, writer sqlutil.Writer, server go
// Returns the device on success.
func (s *devicesStatements) insertDevice(
ctx context.Context, txn *sql.Tx, id, localpart, accessToken string,
- displayName *string,
+ displayName *string, ipAddr, userAgent string,
) (*api.Device, error) {
createdTimeMS := time.Now().UnixNano() / 1000000
var sessionID int64
@@ -144,7 +154,7 @@ func (s *devicesStatements) insertDevice(
return nil, err
}
sessionID++
- if _, err := insertStmt.ExecContext(ctx, id, localpart, accessToken, createdTimeMS, displayName, sessionID); err != nil {
+ if _, err := insertStmt.ExecContext(ctx, id, localpart, accessToken, createdTimeMS, displayName, sessionID, createdTimeMS, ipAddr, userAgent); err != nil {
return nil, err
}
return &api.Device{
@@ -152,6 +162,9 @@ func (s *devicesStatements) insertDevice(
UserID: userutil.MakeUserID(localpart, s.serverName),
AccessToken: accessToken,
SessionID: sessionID,
+ LastSeenTS: createdTimeMS,
+ LastSeenIP: ipAddr,
+ UserAgent: userAgent,
}, nil
}
@@ -288,3 +301,10 @@ func (s *devicesStatements) selectDevicesByID(ctx context.Context, deviceIDs []s
}
return devices, rows.Err()
}
+
+func (s *devicesStatements) updateDeviceLastSeen(ctx context.Context, txn *sql.Tx, deviceID, ipAddr string) error {
+ lastSeenTs := time.Now().UnixNano() / 1000000
+ stmt := sqlutil.TxStmt(txn, s.updateDeviceLastSeenStmt)
+ _, err := stmt.ExecContext(ctx, lastSeenTs, ipAddr, deviceID)
+ return err
+}
diff --git a/userapi/storage/devices/sqlite3/storage.go b/userapi/storage/devices/sqlite3/storage.go
index 7c6645dd..cfaf4fd9 100644
--- a/userapi/storage/devices/sqlite3/storage.go
+++ b/userapi/storage/devices/sqlite3/storage.go
@@ -87,7 +87,7 @@ func (d *Database) GetDevicesByID(ctx context.Context, deviceIDs []string) ([]ap
// Returns the device on success.
func (d *Database) CreateDevice(
ctx context.Context, localpart string, deviceID *string, accessToken string,
- displayName *string,
+ displayName *string, ipAddr, userAgent string,
) (dev *api.Device, returnErr error) {
if deviceID != nil {
returnErr = d.writer.Do(d.db, nil, func(txn *sql.Tx) error {
@@ -97,7 +97,7 @@ func (d *Database) CreateDevice(
return err
}
- dev, err = d.devices.insertDevice(ctx, txn, *deviceID, localpart, accessToken, displayName)
+ dev, err = d.devices.insertDevice(ctx, txn, *deviceID, localpart, accessToken, displayName, ipAddr, userAgent)
return err
})
} else {
@@ -112,7 +112,7 @@ func (d *Database) CreateDevice(
returnErr = d.writer.Do(d.db, nil, func(txn *sql.Tx) error {
var err error
- dev, err = d.devices.insertDevice(ctx, txn, newDeviceID, localpart, accessToken, displayName)
+ dev, err = d.devices.insertDevice(ctx, txn, newDeviceID, localpart, accessToken, displayName, ipAddr, userAgent)
return err
})
if returnErr == nil {
@@ -193,3 +193,10 @@ func (d *Database) RemoveAllDevices(
})
return
}
+
+// UpdateDeviceLastSeen updates a the last seen timestamp and the ip address
+func (d *Database) UpdateDeviceLastSeen(ctx context.Context, deviceID, ipAddr string) error {
+ return d.writer.Do(d.db, nil, func(txn *sql.Tx) error {
+ return d.devices.updateDeviceLastSeen(ctx, txn, deviceID, ipAddr)
+ })
+}
generated by cgit v1.2.3 (git 2.26.2) at 2024-11-22 07:43:39 +0000