- commit
- bd8d606
- parent
- 91a3cc1
- author
- Eric Bower
- date
- 2025-12-17 15:15:23 -0500 EST
feat: access logs 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.
11 files changed,
+173,
-14
M
Makefile
+2,
-1
1@@ -142,10 +142,11 @@ migrate:
2 $(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250320_add_tunnel_id_to_tuns_event_logs_table.sql
3 $(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250410_add_index_analytics_visits_host_list.sql
4 $(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250418_add_project_post_idx_analytics.sql
5+ $(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20251217_add_access_logs_table.sql
6 .PHONY: migrate
7
8 latest:
9- $(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250418_add_project_post_idx_analytics.sql
10+ $(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20251217_add_access_logs_table.sql
11 .PHONY: latest
12
13 psql:
+12,
-2
1@@ -262,14 +262,14 @@ func keyHandler(apiConfig *shared.ApiConfig) http.HandlerFunc {
2 return
3 }
4
5- pubkey, err := shared.PubkeyCertVerify(key, space)
6+ authed, err := shared.PubkeyCertVerify(key, space)
7 if err != nil {
8 log.Error("pubkey cert verify", "err", err)
9 http.Error(w, err.Error(), http.StatusBadRequest)
10 return
11 }
12
13- user, err := apiConfig.Dbpool.FindUserForKey(data.Username, pubkey)
14+ user, err := apiConfig.Dbpool.FindUserForKey(data.Username, authed.Pubkey)
15 if err != nil {
16 log.Error("find user for key", "err", err)
17 w.WriteHeader(http.StatusUnauthorized)
18@@ -282,6 +282,16 @@ func keyHandler(apiConfig *shared.ApiConfig) http.HandlerFunc {
19 return
20 }
21
22+ err = apiConfig.Dbpool.InsertAccessLog(&db.AccessLog{
23+ UserID: user.ID,
24+ Service: space,
25+ Identity: authed.Identity,
26+ Pubkey: authed.OrigPubkey,
27+ })
28+ if err != nil {
29+ log.Error("cannot insert access log", "err", err)
30+ }
31+
32 if !apiConfig.HasPrivilegedAccess(shared.GetApiToken(r)) {
33 w.WriteHeader(http.StatusOK)
34 return
+1,
-0
1@@ -9,6 +9,7 @@ type PgsDB interface {
2 FindUsers() ([]*db.User, error)
3
4 FindFeature(userID string, name string) (*db.FeatureFlag, error)
5+ InsertAccessLog(*db.AccessLog) error
6
7 InsertProject(userID, name, projectDir string) (string, error)
8 UpdateProject(userID, name string) error
+4,
-0
1@@ -191,3 +191,7 @@ func (me *MemoryDB) UpdateProjectAcl(userID, name string, acl db.ProjectAcl) err
2 func (me *MemoryDB) RegisterAdmin(username, pubkey, pubkeyName string) error {
3 return errNotImpl
4 }
5+
6+func (me *MemoryDB) InsertAccessLog(*db.AccessLog) error {
7+ return errNotImpl
8+}
+11,
-0
1@@ -82,6 +82,17 @@ func (me *PgsPsqlDB) FindFeature(userID, name string) (*db.FeatureFlag, error) {
2 return &ff, err
3 }
4
5+func (me *PgsPsqlDB) InsertAccessLog(log *db.AccessLog) error {
6+ _, err := me.Db.Exec(
7+ `INSERT INTO access_logs (user_id, service, pubkey, identity) VALUES ($1, $2, $3, $4);`,
8+ log.UserID,
9+ log.Service,
10+ log.Pubkey,
11+ log.Identity,
12+ )
13+ return err
14+}
15+
16 func (me *PgsPsqlDB) InsertProject(userID, name, projectDir string) (string, error) {
17 if !utils.IsValidSubdomain(name) {
18 return "", fmt.Errorf("'%s' is not a valid project name, must match /^[a-z0-9-]+$/", name)
+13,
-0
1@@ -205,6 +205,15 @@ type AnalyticsVisits struct {
2 ContentType string `json:"content_type"`
3 }
4
5+type AccessLog struct {
6+ ID string `json:"id"`
7+ UserID string `json:"user_id"`
8+ Service string `json:"service"`
9+ Pubkey string `json:"pubkey"`
10+ Identity string `json:"identity"`
11+ CreatedAt *time.Time `json:"created_at"`
12+}
13+
14 type Pager struct {
15 Num int
16 Page int
17@@ -454,5 +463,9 @@ type DB interface {
18 FindTunsEventLogs(userID string) ([]*TunsEventLog, error)
19 FindTunsEventLogsByAddr(userID, addr string) ([]*TunsEventLog, error)
20
21+ InsertAccessLog(log *AccessLog) error
22+ FindAccessLogs(userID string, fromDate *time.Time) ([]*AccessLog, error)
23+ FindPubkeysInAccessLogs(userID string) ([]string, error)
24+
25 Close() error
26 }
+62,
-0
1@@ -1940,3 +1940,65 @@ func (me *PsqlDB) FindUserStats(userID string) (*db.UserStats, error) {
2 stats.Pages = *pgs
3 return &stats, err
4 }
5+
6+func (me *PsqlDB) FindAccessLogs(userID string, fromDate *time.Time) ([]*db.AccessLog, error) {
7+ logs := []*db.AccessLog{}
8+ rs, err := me.Db.Query(
9+ `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)
10+ if err != nil {
11+ return nil, err
12+ }
13+
14+ for rs.Next() {
15+ log := db.AccessLog{}
16+ err := rs.Scan(
17+ &log.ID, &log.UserID, &log.Service, &log.Pubkey, &log.Identity, &log.CreatedAt,
18+ )
19+ if err != nil {
20+ return nil, err
21+ }
22+ logs = append(logs, &log)
23+ }
24+
25+ if rs.Err() != nil {
26+ return nil, rs.Err()
27+ }
28+
29+ return logs, nil
30+}
31+
32+func (me *PsqlDB) FindPubkeysInAccessLogs(userID string) ([]string, error) {
33+ pubkeys := []string{}
34+ rs, err := me.Db.Query(
35+ `SELECT DISTINCT(pubkey) FROM access_logs WHERE user_id=$1`, userID,
36+ )
37+ if err != nil {
38+ return nil, err
39+ }
40+
41+ for rs.Next() {
42+ pubkey := ""
43+ err := rs.Scan(&pubkey)
44+ if err != nil {
45+ return nil, err
46+ }
47+ pubkeys = append(pubkeys, pubkey)
48+ }
49+
50+ if rs.Err() != nil {
51+ return nil, rs.Err()
52+ }
53+
54+ return pubkeys, nil
55+}
56+
57+func (me *PsqlDB) InsertAccessLog(log *db.AccessLog) error {
58+ _, err := me.Db.Exec(
59+ `INSERT INTO access_logs (user_id, service, pubkey, identity) VALUES ($1, $2, $3, $4);`,
60+ log.UserID,
61+ log.Service,
62+ log.Pubkey,
63+ log.Identity,
64+ )
65+ return err
66+}
+12,
-0
1@@ -288,3 +288,15 @@ func (me *StubDB) VisitUrlNotFound(opts *db.SummaryOpts) ([]*db.VisitUrl, error)
2 func (me *StubDB) FindUsersWithPost(space string) ([]*db.User, error) {
3 return nil, errNotImpl
4 }
5+
6+func (me *StubDB) FindAccessLogs(userID string, fromDate *time.Time) ([]*db.AccessLog, error) {
7+ return nil, errNotImpl
8+}
9+
10+func (me *StubDB) FindPubkeysInAccessLogs(userID string) ([]string, error) {
11+ return []string{}, errNotImpl
12+}
13+
14+func (me *StubDB) InsertAccessLog(log *db.AccessLog) error {
15+ return errNotImpl
16+}
+3,
-0
1@@ -51,11 +51,14 @@ func LogMiddleware(getLogger GetLoggerInterface, database FindUserInterface) SSH
2 }
3
4 if found {
5+ // identity provided by ssh-cert
6+ identity := s.Permissions().Extensions["identity"]
7 if err == nil && user != nil {
8 logger = logger.With(
9 "user", user.Name,
10 "userId", user.ID,
11 "ip", s.RemoteAddr().String(),
12+ "identity", identity,
13 )
14
15 SetUser(s, user)
1@@ -23,6 +23,7 @@ type AuthFindUser interface {
2 FindUserByPubkey(key string) (*db.User, error)
3 FindUserByName(name string) (*db.User, error)
4 FindFeature(userID, name string) (*db.FeatureFlag, error)
5+ InsertAccessLog(log *db.AccessLog) error
6 }
7
8 func NewSshAuthHandler(dbh AuthFindUser, logger *slog.Logger, principal string) *SshAuthHandler {
9@@ -33,11 +34,24 @@ func NewSshAuthHandler(dbh AuthFindUser, logger *slog.Logger, principal string)
10 }
11 }
12
13-func PubkeyCertVerify(key ssh.PublicKey, srcPrincipal string) (string, error) {
14+type AuthedPubkey struct {
15+ OrigPubkey string
16+ Pubkey string
17+ Identity string
18+}
19+
20+func PubkeyCertVerify(key ssh.PublicKey, srcPrincipal string) (*AuthedPubkey, error) {
21+ origPubkey := utils.KeyForKeyText(key)
22+ authed := &AuthedPubkey{
23+ OrigPubkey: origPubkey,
24+ Pubkey: origPubkey,
25+ Identity: "pubkey",
26+ }
27+
28 cert, ok := key.(*ssh.Certificate)
29 if ok {
30 if cert.CertType != ssh.UserCert {
31- return "", fmt.Errorf("ssh-cert has type %d", cert.CertType)
32+ return nil, fmt.Errorf("ssh-cert has type %d", cert.CertType)
33 }
34
35 found := false
36@@ -48,34 +62,36 @@ func PubkeyCertVerify(key ssh.PublicKey, srcPrincipal string) (string, error) {
37 }
38 }
39 if !found {
40- return "", fmt.Errorf("ssh-cert principals not valid")
41+ return nil, fmt.Errorf("ssh-cert principals not valid")
42 }
43
44 clock := time.Now
45 unixNow := clock().Unix()
46 if after := int64(cert.ValidAfter); after < 0 || unixNow < int64(cert.ValidAfter) {
47- return "", fmt.Errorf("ssh-cert is not yet valid")
48+ return nil, fmt.Errorf("ssh-cert is not yet valid")
49 }
50 if before := int64(cert.ValidBefore); cert.ValidBefore != uint64(ssh.CertTimeInfinity) && (unixNow >= before || before < 0) {
51- return "", fmt.Errorf("ssh-cert has expired")
52+ return nil, fmt.Errorf("ssh-cert has expired")
53 }
54
55- return utils.KeyForKeyText(cert.SignatureKey), nil
56+ authed.Pubkey = utils.KeyForKeyText(cert.SignatureKey)
57+ authed.Identity = cert.KeyId
58+ return authed, nil
59 }
60
61- return utils.KeyForKeyText(key), nil
62+ return authed, nil
63 }
64
65 func (r *SshAuthHandler) PubkeyAuthHandler(conn ssh.ConnMetadata, key ssh.PublicKey) (*ssh.Permissions, error) {
66 log := r.Logger
67 var user *db.User
68 var err error
69- pubkey, err := PubkeyCertVerify(key, r.Principal)
70+ authed, err := PubkeyCertVerify(key, r.Principal)
71 if err != nil {
72 return nil, err
73 }
74
75- user, err = r.DB.FindUserByPubkey(pubkey)
76+ user, err = r.DB.FindUserByPubkey(authed.Pubkey)
77 if err != nil {
78 log.Error(
79 "could not find user for key",
80@@ -91,6 +107,16 @@ func (r *SshAuthHandler) PubkeyAuthHandler(conn ssh.ConnMetadata, key ssh.Public
81 return nil, fmt.Errorf("username is not set")
82 }
83
84+ err = r.DB.InsertAccessLog(&db.AccessLog{
85+ UserID: user.ID,
86+ Service: r.Principal,
87+ Identity: authed.Identity,
88+ Pubkey: authed.OrigPubkey,
89+ })
90+ if err != nil {
91+ log.Error("cannot insert access log", "err", err)
92+ }
93+
94 // impersonation
95 var impID string
96 usr := conn.User()
97@@ -108,8 +134,9 @@ func (r *SshAuthHandler) PubkeyAuthHandler(conn ssh.ConnMetadata, key ssh.Public
98
99 perms := &ssh.Permissions{
100 Extensions: map[string]string{
101- "user_id": user.ID,
102- "pubkey": pubkey,
103+ "user_id": user.ID,
104+ "pubkey": authed.Pubkey,
105+ "identity": authed.Identity,
106 },
107 }
108
1@@ -0,0 +1,15 @@
2+CREATE TABLE IF NOT EXISTS access_logs (
3+ id uuid NOT NULL DEFAULT uuid_generate_v4(),
4+ user_id uuid NOT NULL,
5+ service character varying(255) NOT NULL,
6+ pubkey text NOT NULL DEFAULT '',
7+ identity text NOT NULL DEFAULT '',
8+ data jsonb NOT NULL DEFAULT '{}'::jsonb,
9+ created_at timestamp without time zone NOT NULL DEFAULT NOW(),
10+ CONSTRAINT access_logs_pkey PRIMARY KEY (id),
11+ CONSTRAINT fk_access_logs_app_users
12+ FOREIGN KEY(user_id)
13+ REFERENCES app_users(id)
14+ ON DELETE CASCADE
15+ ON UPDATE CASCADE
16+);