1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
|
package database
import (
"database/sql"
"errors"
"time"
_ "github.com/mattn/go-sqlite3"
)
// connectionFreshness is how long a device counts as present after we last saw
// it in the ARP table. It smooths over the kernel briefly aging an entry out.
const connectionFreshness = 60 * time.Second
type Connection struct {
IP string
Mac string
UpdatedAt time.Time
}
// RefreshConnections upserts the current ARP snapshot, bumping updated_at for
// devices still present, and prunes ones not seen within the freshness window.
func RefreshConnections(db *sql.DB, ipToMac map[string]string) error {
tx, err := db.Begin()
if err != nil {
return err
}
defer tx.Rollback()
stmt, err := tx.Prepare(`INSERT INTO connections (ip, mac, updated_at) VALUES (?, ?, ?)
ON CONFLICT(ip) DO UPDATE SET mac = excluded.mac, updated_at = excluded.updated_at`)
if err != nil {
return err
}
defer stmt.Close()
now := time.Now()
for ip, mac := range ipToMac {
if _, err := stmt.Exec(ip, mac, now); err != nil {
return err
}
}
if _, err := tx.Exec("DELETE FROM connections WHERE updated_at < ?", now.Add(-connectionFreshness)); err != nil {
return err
}
return tx.Commit()
}
func MacForIP(db *sql.DB, ip string) (string, error) {
var mac string
err := db.QueryRow(
"SELECT mac FROM connections WHERE ip = ? AND updated_at > ?",
ip, time.Now().Add(-connectionFreshness),
).Scan(&mac)
if errors.Is(err, sql.ErrNoRows) {
return "", nil
}
return mac, err
}
// NetworkUserIDs returns the set of users whose MAC was seen on the LAN within
// the freshness window, whether or not they're on the chat.
func NetworkUserIDs(db *sql.DB) (map[string]bool, error) {
rows, err := db.Query(
"SELECT DISTINCT m.user_id FROM macs m JOIN connections c ON c.mac = m.mac WHERE c.updated_at > ?",
time.Now().Add(-connectionFreshness),
)
if err != nil {
return nil, err
}
defer rows.Close()
ids := map[string]bool{}
for rows.Next() {
var id string
if err := rows.Scan(&id); err != nil {
return nil, err
}
ids[id] = true
}
return ids, rows.Err()
}
func ListAliveConnections(db *sql.DB) ([]Connection, error) {
rows, err := db.Query(
"SELECT ip, mac, updated_at FROM connections WHERE updated_at > ? ORDER BY ip",
time.Now().Add(-connectionFreshness),
)
if err != nil {
return nil, err
}
defer rows.Close()
conns := []Connection{}
for rows.Next() {
var c Connection
if err := rows.Scan(&c.IP, &c.Mac, &c.UpdatedAt); err != nil {
return nil, err
}
conns = append(conns, c)
}
return conns, rows.Err()
}
|
