dashboard / erock/pico / feat: access logs #96 rss

accepted · opened on 2025-12-17T20:18:54Z by erock
Help
checkout latest patchset:
ssh pr.pico.sh print pr-96 | git am -3
checkout any patchset in a patch request:
ssh pr.pico.sh print ps-X | git am -3
add changes to patch request:
git format-patch main --stdout | ssh pr.pico.sh pr add 96
add review to patch request:
git format-patch main --stdout | ssh pr.pico.sh pr add --review 96
accept PR:
ssh pr.pico.sh pr accept 96
close PR:
ssh pr.pico.sh pr close 96
Timeline Patchsets
This adds a new table to pico to track access logs.  This helps pico
admins understand what pubkeys are access their services and what their
identity is in the case of certified public keys.
+2 -1 Makefile link
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
diff --git a/Makefile b/Makefile
index f0b596c..5f0793c 100644
--- a/Makefile
+++ b/Makefile
@@ -142,10 +142,11 @@ migrate:
 	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250320_add_tunnel_id_to_tuns_event_logs_table.sql
 	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250410_add_index_analytics_visits_host_list.sql
 	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250418_add_project_post_idx_analytics.sql
+	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20251217_add_access_logs_table.sql
 .PHONY: migrate
 
 latest:
-	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250418_add_project_post_idx_analytics.sql
+	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20251217_add_access_logs_table.sql
 .PHONY: latest
 
 psql:
+12 -2 pkg/apps/auth/api.go link
 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
diff --git a/pkg/apps/auth/api.go b/pkg/apps/auth/api.go
index b055990..577de8b 100644
--- a/pkg/apps/auth/api.go
+++ b/pkg/apps/auth/api.go
@@ -262,14 +262,14 @@ func keyHandler(apiConfig *shared.ApiConfig) http.HandlerFunc {
 			return
 		}
 
-		pubkey, err := shared.PubkeyCertVerify(key, space)
+		authed, err := shared.PubkeyCertVerify(key, space)
 		if err != nil {
 			log.Error("pubkey cert verify", "err", err)
 			http.Error(w, err.Error(), http.StatusBadRequest)
 			return
 		}
 
-		user, err := apiConfig.Dbpool.FindUserForKey(data.Username, pubkey)
+		user, err := apiConfig.Dbpool.FindUserForKey(data.Username, authed.Pubkey)
 		if err != nil {
 			log.Error("find user for key", "err", err)
 			w.WriteHeader(http.StatusUnauthorized)
@@ -282,6 +282,16 @@ func keyHandler(apiConfig *shared.ApiConfig) http.HandlerFunc {
 			return
 		}
 
+		err = apiConfig.Dbpool.InsertAccessLog(&db.AccessLog{
+			UserID:   user.ID,
+			Service:  space,
+			Identity: authed.Identity,
+			Pubkey:   authed.OrigPubkey,
+		})
+		if err != nil {
+			log.Error("cannot insert access log", "err", err)
+		}
+
 		if !apiConfig.HasPrivilegedAccess(shared.GetApiToken(r)) {
 			w.WriteHeader(http.StatusOK)
 			return
+1 -0 pkg/apps/pgs/db/db.go link
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
diff --git a/pkg/apps/pgs/db/db.go b/pkg/apps/pgs/db/db.go
index a462eeb..a89806e 100644
--- a/pkg/apps/pgs/db/db.go
+++ b/pkg/apps/pgs/db/db.go
@@ -9,6 +9,7 @@ type PgsDB interface {
 	FindUsers() ([]*db.User, error)
 
 	FindFeature(userID string, name string) (*db.FeatureFlag, error)
+	InsertAccessLog(*db.AccessLog) error
 
 	InsertProject(userID, name, projectDir string) (string, error)
 	UpdateProject(userID, name string) error
+4 -0 pkg/apps/pgs/db/memory.go link
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
diff --git a/pkg/apps/pgs/db/memory.go b/pkg/apps/pgs/db/memory.go
index eb0600f..c6bf183 100644
--- a/pkg/apps/pgs/db/memory.go
+++ b/pkg/apps/pgs/db/memory.go
@@ -191,3 +191,7 @@ func (me *MemoryDB) UpdateProjectAcl(userID, name string, acl db.ProjectAcl) err
 func (me *MemoryDB) RegisterAdmin(username, pubkey, pubkeyName string) error {
 	return errNotImpl
 }
+
+func (me *MemoryDB) InsertAccessLog(*db.AccessLog) error {
+	return errNotImpl
+}
+11 -0 pkg/apps/pgs/db/postgres.go link
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
diff --git a/pkg/apps/pgs/db/postgres.go b/pkg/apps/pgs/db/postgres.go
index 3ad4927..817663d 100644
--- a/pkg/apps/pgs/db/postgres.go
+++ b/pkg/apps/pgs/db/postgres.go
@@ -82,6 +82,17 @@ func (me *PgsPsqlDB) FindFeature(userID, name string) (*db.FeatureFlag, error) {
 	return &ff, err
 }
 
+func (me *PgsPsqlDB) InsertAccessLog(log *db.AccessLog) error {
+	_, err := me.Db.Exec(
+		`INSERT INTO access_logs (user_id, service, pubkey, identity) VALUES ($1, $2, $3, $4);`,
+		log.UserID,
+		log.Service,
+		log.Pubkey,
+		log.Identity,
+	)
+	return err
+}
+
 func (me *PgsPsqlDB) InsertProject(userID, name, projectDir string) (string, error) {
 	if !utils.IsValidSubdomain(name) {
 		return "", fmt.Errorf("'%s' is not a valid project name, must match /^[a-z0-9-]+$/", name)
+13 -0 pkg/db/db.go link
 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
diff --git a/pkg/db/db.go b/pkg/db/db.go
index 8176c21..f29c654 100644
--- a/pkg/db/db.go
+++ b/pkg/db/db.go
@@ -205,6 +205,15 @@ type AnalyticsVisits struct {
 	ContentType string `json:"content_type"`
 }
 
+type AccessLog struct {
+	ID        string     `json:"id"`
+	UserID    string     `json:"user_id"`
+	Service   string     `json:"service"`
+	Pubkey    string     `json:"pubkey"`
+	Identity  string     `json:"identity"`
+	CreatedAt *time.Time `json:"created_at"`
+}
+
 type Pager struct {
 	Num  int
 	Page int
@@ -454,5 +463,9 @@ type DB interface {
 	FindTunsEventLogs(userID string) ([]*TunsEventLog, error)
 	FindTunsEventLogsByAddr(userID, addr string) ([]*TunsEventLog, error)
 
+	InsertAccessLog(log *AccessLog) error
+	FindAccessLogs(userID string, fromDate *time.Time) ([]*AccessLog, error)
+	FindPubkeysInAccessLogs(userID string) ([]string, error)
+
 	Close() error
 }
+62 -0 pkg/db/postgres/storage.go link
 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
diff --git a/pkg/db/postgres/storage.go b/pkg/db/postgres/storage.go
index 7f6c404..ca36c82 100644
--- a/pkg/db/postgres/storage.go
+++ b/pkg/db/postgres/storage.go
@@ -1940,3 +1940,65 @@ func (me *PsqlDB) FindUserStats(userID string) (*db.UserStats, error) {
 	stats.Pages = *pgs
 	return &stats, err
 }
+
+func (me *PsqlDB) FindAccessLogs(userID string, fromDate *time.Time) ([]*db.AccessLog, error) {
+	logs := []*db.AccessLog{}
+	rs, err := me.Db.Query(
+		`SELECT id, user_id, service, pubkey, identity, created_at FROM access_logs WHERE user_id=$1 AND created_at >= $2 ORDER BY created_at DESC`, userID, fromDate)
+	if err != nil {
+		return nil, err
+	}
+
+	for rs.Next() {
+		log := db.AccessLog{}
+		err := rs.Scan(
+			&log.ID, &log.UserID, &log.Service, &log.Pubkey, &log.Identity, &log.CreatedAt,
+		)
+		if err != nil {
+			return nil, err
+		}
+		logs = append(logs, &log)
+	}
+
+	if rs.Err() != nil {
+		return nil, rs.Err()
+	}
+
+	return logs, nil
+}
+
+func (me *PsqlDB) FindPubkeysInAccessLogs(userID string) ([]string, error) {
+	pubkeys := []string{}
+	rs, err := me.Db.Query(
+		`SELECT DISTINCT(pubkey) FROM access_logs WHERE user_id=$1`, userID,
+	)
+	if err != nil {
+		return nil, err
+	}
+
+	for rs.Next() {
+		pubkey := ""
+		err := rs.Scan(&pubkey)
+		if err != nil {
+			return nil, err
+		}
+		pubkeys = append(pubkeys, pubkey)
+	}
+
+	if rs.Err() != nil {
+		return nil, rs.Err()
+	}
+
+	return pubkeys, nil
+}
+
+func (me *PsqlDB) InsertAccessLog(log *db.AccessLog) error {
+	_, err := me.Db.Exec(
+		`INSERT INTO access_logs (user_id, service, pubkey, identity) VALUES ($1, $2, $3, $4);`,
+		log.UserID,
+		log.Service,
+		log.Pubkey,
+		log.Identity,
+	)
+	return err
+}
+12 -0 pkg/db/stub/stub.go link
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
diff --git a/pkg/db/stub/stub.go b/pkg/db/stub/stub.go
index 17824d8..85dcaf4 100644
--- a/pkg/db/stub/stub.go
+++ b/pkg/db/stub/stub.go
@@ -288,3 +288,15 @@ func (me *StubDB) VisitUrlNotFound(opts *db.SummaryOpts) ([]*db.VisitUrl, error)
 func (me *StubDB) FindUsersWithPost(space string) ([]*db.User, error) {
 	return nil, errNotImpl
 }
+
+func (me *StubDB) FindAccessLogs(userID string, fromDate *time.Time) ([]*db.AccessLog, error) {
+	return nil, errNotImpl
+}
+
+func (me *StubDB) FindPubkeysInAccessLogs(userID string) ([]string, error) {
+	return []string{}, errNotImpl
+}
+
+func (me *StubDB) InsertAccessLog(log *db.AccessLog) error {
+	return errNotImpl
+}
+3 -0 pkg/pssh/logger.go link
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
diff --git a/pkg/pssh/logger.go b/pkg/pssh/logger.go
index b2959a6..a579f1a 100644
--- a/pkg/pssh/logger.go
+++ b/pkg/pssh/logger.go
@@ -51,11 +51,14 @@ func LogMiddleware(getLogger GetLoggerInterface, database FindUserInterface) SSH
 					}
 
 					if found {
+						// identity provided by ssh-cert
+						identity := s.Permissions().Extensions["identity"]
 						if err == nil && user != nil {
 							logger = logger.With(
 								"user", user.Name,
 								"userId", user.ID,
 								"ip", s.RemoteAddr().String(),
+								"identity", identity,
 							)
 
 							SetUser(s, user)
+38 -11 pkg/shared/ssh.go link
  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
106
107
108
109
110
111
112
diff --git a/pkg/shared/ssh.go b/pkg/shared/ssh.go
index 83273ca..632d02a 100644
--- a/pkg/shared/ssh.go
+++ b/pkg/shared/ssh.go
@@ -23,6 +23,7 @@ type AuthFindUser interface {
 	FindUserByPubkey(key string) (*db.User, error)
 	FindUserByName(name string) (*db.User, error)
 	FindFeature(userID, name string) (*db.FeatureFlag, error)
+	InsertAccessLog(log *db.AccessLog) error
 }
 
 func NewSshAuthHandler(dbh AuthFindUser, logger *slog.Logger, principal string) *SshAuthHandler {
@@ -33,11 +34,24 @@ func NewSshAuthHandler(dbh AuthFindUser, logger *slog.Logger, principal string)
 	}
 }
 
-func PubkeyCertVerify(key ssh.PublicKey, srcPrincipal string) (string, error) {
+type AuthedPubkey struct {
+	OrigPubkey string
+	Pubkey     string
+	Identity   string
+}
+
+func PubkeyCertVerify(key ssh.PublicKey, srcPrincipal string) (*AuthedPubkey, error) {
+	origPubkey := utils.KeyForKeyText(key)
+	authed := &AuthedPubkey{
+		OrigPubkey: origPubkey,
+		Pubkey:     origPubkey,
+		Identity:   "pubkey",
+	}
+
 	cert, ok := key.(*ssh.Certificate)
 	if ok {
 		if cert.CertType != ssh.UserCert {
-			return "", fmt.Errorf("ssh-cert has type %d", cert.CertType)
+			return nil, fmt.Errorf("ssh-cert has type %d", cert.CertType)
 		}
 
 		found := false
@@ -48,34 +62,36 @@ func PubkeyCertVerify(key ssh.PublicKey, srcPrincipal string) (string, error) {
 			}
 		}
 		if !found {
-			return "", fmt.Errorf("ssh-cert principals not valid")
+			return nil, fmt.Errorf("ssh-cert principals not valid")
 		}
 
 		clock := time.Now
 		unixNow := clock().Unix()
 		if after := int64(cert.ValidAfter); after < 0 || unixNow < int64(cert.ValidAfter) {
-			return "", fmt.Errorf("ssh-cert is not yet valid")
+			return nil, fmt.Errorf("ssh-cert is not yet valid")
 		}
 		if before := int64(cert.ValidBefore); cert.ValidBefore != uint64(ssh.CertTimeInfinity) && (unixNow >= before || before < 0) {
-			return "", fmt.Errorf("ssh-cert has expired")
+			return nil, fmt.Errorf("ssh-cert has expired")
 		}
 
-		return utils.KeyForKeyText(cert.SignatureKey), nil
+		authed.Pubkey = utils.KeyForKeyText(cert.SignatureKey)
+		authed.Identity = cert.KeyId
+		return authed, nil
 	}
 
-	return utils.KeyForKeyText(key), nil
+	return authed, nil
 }
 
 func (r *SshAuthHandler) PubkeyAuthHandler(conn ssh.ConnMetadata, key ssh.PublicKey) (*ssh.Permissions, error) {
 	log := r.Logger
 	var user *db.User
 	var err error
-	pubkey, err := PubkeyCertVerify(key, r.Principal)
+	authed, err := PubkeyCertVerify(key, r.Principal)
 	if err != nil {
 		return nil, err
 	}
 
-	user, err = r.DB.FindUserByPubkey(pubkey)
+	user, err = r.DB.FindUserByPubkey(authed.Pubkey)
 	if err != nil {
 		log.Error(
 			"could not find user for key",
@@ -91,6 +107,16 @@ func (r *SshAuthHandler) PubkeyAuthHandler(conn ssh.ConnMetadata, key ssh.Public
 		return nil, fmt.Errorf("username is not set")
 	}
 
+	err = r.DB.InsertAccessLog(&db.AccessLog{
+		UserID:   user.ID,
+		Service:  r.Principal,
+		Identity: authed.Identity,
+		Pubkey:   authed.OrigPubkey,
+	})
+	if err != nil {
+		log.Error("cannot insert access log", "err", err)
+	}
+
 	// impersonation
 	var impID string
 	usr := conn.User()
@@ -108,8 +134,9 @@ func (r *SshAuthHandler) PubkeyAuthHandler(conn ssh.ConnMetadata, key ssh.Public
 
 	perms := &ssh.Permissions{
 		Extensions: map[string]string{
-			"user_id": user.ID,
-			"pubkey":  pubkey,
+			"user_id":  user.ID,
+			"pubkey":   authed.Pubkey,
+			"identity": authed.Identity,
 		},
 	}
 
+15 -0 sql/migrations/20251217_add_access_logs_table.sql link
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
diff --git a/sql/migrations/20251217_add_access_logs_table.sql b/sql/migrations/20251217_add_access_logs_table.sql
new file mode 100644
index 0000000..fb9e00a
--- /dev/null
+++ b/sql/migrations/20251217_add_access_logs_table.sql
@@ -0,0 +1,15 @@
+CREATE TABLE IF NOT EXISTS access_logs (
+  id uuid NOT NULL DEFAULT uuid_generate_v4(),
+  user_id uuid NOT NULL,
+  service character varying(255) NOT NULL,
+  pubkey text NOT NULL DEFAULT '',
+  identity text NOT NULL DEFAULT '',
+  data jsonb NOT NULL DEFAULT '{}'::jsonb,
+  created_at timestamp without time zone NOT NULL DEFAULT NOW(),
+  CONSTRAINT access_logs_pkey PRIMARY KEY (id),
+  CONSTRAINT fk_access_logs_app_users
+    FOREIGN KEY(user_id)
+  REFERENCES app_users(id)
+  ON DELETE CASCADE
+  ON UPDATE CASCADE
+);

+1 -0 pkg/db/db.go link
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
diff --git a/pkg/db/db.go b/pkg/db/db.go
index f29c654..daae96c 100644
--- a/pkg/db/db.go
+++ b/pkg/db/db.go
@@ -466,6 +466,7 @@ type DB interface {
 	InsertAccessLog(log *AccessLog) error
 	FindAccessLogs(userID string, fromDate *time.Time) ([]*AccessLog, error)
 	FindPubkeysInAccessLogs(userID string) ([]string, error)
+	FindAccessLogsByPubkey(pubkey string, fromDate *time.Time) ([]*AccessLog, error)
 
 	Close() error
 }
+26 -0 pkg/db/postgres/storage.go link
 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
diff --git a/pkg/db/postgres/storage.go b/pkg/db/postgres/storage.go
index ca36c82..e3ae3fc 100644
--- a/pkg/db/postgres/storage.go
+++ b/pkg/db/postgres/storage.go
@@ -1967,6 +1967,32 @@ func (me *PsqlDB) FindAccessLogs(userID string, fromDate *time.Time) ([]*db.Acce
 	return logs, nil
 }
 
+func (me *PsqlDB) FindAccessLogsByPubkey(pubkey string, fromDate *time.Time) ([]*db.AccessLog, error) {
+	logs := []*db.AccessLog{}
+	rs, err := me.Db.Query(
+		`SELECT id, user_id, service, pubkey, identity, created_at FROM access_logs WHERE pubkey=$1 AND created_at >= $2 ORDER BY created_at DESC`, pubkey, fromDate)
+	if err != nil {
+		return nil, err
+	}
+
+	for rs.Next() {
+		log := db.AccessLog{}
+		err := rs.Scan(
+			&log.ID, &log.UserID, &log.Service, &log.Pubkey, &log.Identity, &log.CreatedAt,
+		)
+		if err != nil {
+			return nil, err
+		}
+		logs = append(logs, &log)
+	}
+
+	if rs.Err() != nil {
+		return nil, rs.Err()
+	}
+
+	return logs, nil
+}
+
 func (me *PsqlDB) FindPubkeysInAccessLogs(userID string) ([]string, error) {
 	pubkeys := []string{}
 	rs, err := me.Db.Query(
+4 -0 pkg/db/stub/stub.go link
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
diff --git a/pkg/db/stub/stub.go b/pkg/db/stub/stub.go
index 85dcaf4..2b81890 100644
--- a/pkg/db/stub/stub.go
+++ b/pkg/db/stub/stub.go
@@ -293,6 +293,10 @@ func (me *StubDB) FindAccessLogs(userID string, fromDate *time.Time) ([]*db.Acce
 	return nil, errNotImpl
 }
 
+func (me *StubDB) FindAccessLogsByPubkey(pubkey string, fromDate *time.Time) ([]*db.AccessLog, error) {
+	return nil, errNotImpl
+}
+
 func (me *StubDB) FindPubkeysInAccessLogs(userID string) ([]string, error) {
 	return []string{}, errNotImpl
 }