pico

feat: access logs rss
created pr with 96.1 on 2025-12-17T20:18:54Z · by c8ef7d19
added 96.2 on 2025-12-18T00:34:52Z · by c8ef7d19
1: bd8d606 = 1: bd8d606 feat: access logs
-: ------- > 2: d247cbd feat: find access logs by pubkey
cmds
checkout latest patchset: 
ssh pr.pico.sh print 96 | git am -3
checkout any patchset in a patch request: 
ssh pr.pico.sh print 96.[rev] | git am -3
add changes to patch request: 
git format-patch main --stdout | ssh pr.pico.sh pr add 96

Patchset 96.1 on 2025-12-17T20:18:54Z · commit bd8d606

→ feat: access logs
Eric Bower 2025-12-17T20:15:23Z
+2 -1 Makefile #
......@@ -142,10 +142,11 @@ migrate:
142142 $(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250320_add_tunnel_id_to_tuns_event_logs_table.sql
143143 $(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250410_add_index_analytics_visits_host_list.sql
144144 $(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250418_add_project_post_idx_analytics.sql
145+ $(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20251217_add_access_logs_table.sql
145146 .PHONY: migrate
146147
147148 latest:
148- $(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250418_add_project_post_idx_analytics.sql
149+ $(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20251217_add_access_logs_table.sql
149150 .PHONY: latest
150151
151152 psql:
+12 -2 pkg/apps/auth/api.go #
......@@ -262,14 +262,14 @@ func keyHandler(apiConfig *shared.ApiConfig) http.HandlerFunc {
262262 return
263263 }
264264
265- pubkey, err := shared.PubkeyCertVerify(key, space)
265+ authed, err := shared.PubkeyCertVerify(key, space)
266266 if err != nil {
267267 log.Error("pubkey cert verify", "err", err)
268268 http.Error(w, err.Error(), http.StatusBadRequest)
269269 return
270270 }
271271
272- user, err := apiConfig.Dbpool.FindUserForKey(data.Username, pubkey)
272+ user, err := apiConfig.Dbpool.FindUserForKey(data.Username, authed.Pubkey)
273273 if err != nil {
274274 log.Error("find user for key", "err", err)
275275 w.WriteHeader(http.StatusUnauthorized)
......@@ -282,6 +282,16 @@ func keyHandler(apiConfig *shared.ApiConfig) http.HandlerFunc {
282282 return
283283 }
284284
285+ err = apiConfig.Dbpool.InsertAccessLog(&db.AccessLog{
286+ UserID: user.ID,
287+ Service: space,
288+ Identity: authed.Identity,
289+ Pubkey: authed.OrigPubkey,
290+ })
291+ if err != nil {
292+ log.Error("cannot insert access log", "err", err)
293+ }
294+
285295 if !apiConfig.HasPrivilegedAccess(shared.GetApiToken(r)) {
286296 w.WriteHeader(http.StatusOK)
287297 return
+1 -0 pkg/apps/pgs/db/db.go #
......@@ -9,6 +9,7 @@ type PgsDB interface {
99 FindUsers() ([]*db.User, error)
1010
1111 FindFeature(userID string, name string) (*db.FeatureFlag, error)
12+ InsertAccessLog(*db.AccessLog) error
1213
1314 InsertProject(userID, name, projectDir string) (string, error)
1415 UpdateProject(userID, name string) error
+4 -0 pkg/apps/pgs/db/memory.go #
......@@ -191,3 +191,7 @@ func (me *MemoryDB) UpdateProjectAcl(userID, name string, acl db.ProjectAcl) err
191191 func (me *MemoryDB) RegisterAdmin(username, pubkey, pubkeyName string) error {
192192 return errNotImpl
193193 }
194+
195+func (me *MemoryDB) InsertAccessLog(*db.AccessLog) error {
196+ return errNotImpl
197+}
+11 -0 pkg/apps/pgs/db/postgres.go #
......@@ -82,6 +82,17 @@ func (me *PgsPsqlDB) FindFeature(userID, name string) (*db.FeatureFlag, error) {
8282 return &ff, err
8383 }
8484
85+func (me *PgsPsqlDB) InsertAccessLog(log *db.AccessLog) error {
86+ _, err := me.Db.Exec(
87+ `INSERT INTO access_logs (user_id, service, pubkey, identity) VALUES ($1, $2, $3, $4);`,
88+ log.UserID,
89+ log.Service,
90+ log.Pubkey,
91+ log.Identity,
92+ )
93+ return err
94+}
95+
8596 func (me *PgsPsqlDB) InsertProject(userID, name, projectDir string) (string, error) {
8697 if !utils.IsValidSubdomain(name) {
8798 return "", fmt.Errorf("'%s' is not a valid project name, must match /^[a-z0-9-]+$/", name)
+13 -0 pkg/db/db.go #
......@@ -205,6 +205,15 @@ type AnalyticsVisits struct {
205205 ContentType string `json:"content_type"`
206206 }
207207
208+type AccessLog struct {
209+ ID string `json:"id"`
210+ UserID string `json:"user_id"`
211+ Service string `json:"service"`
212+ Pubkey string `json:"pubkey"`
213+ Identity string `json:"identity"`
214+ CreatedAt *time.Time `json:"created_at"`
215+}
216+
208217 type Pager struct {
209218 Num int
210219 Page int
......@@ -454,5 +463,9 @@ type DB interface {
454463 FindTunsEventLogs(userID string) ([]*TunsEventLog, error)
455464 FindTunsEventLogsByAddr(userID, addr string) ([]*TunsEventLog, error)
456465
466+ InsertAccessLog(log *AccessLog) error
467+ FindAccessLogs(userID string, fromDate *time.Time) ([]*AccessLog, error)
468+ FindPubkeysInAccessLogs(userID string) ([]string, error)
469+
457470 Close() error
458471 }
+62 -0 pkg/db/postgres/storage.go #
......@@ -1940,3 +1940,65 @@ func (me *PsqlDB) FindUserStats(userID string) (*db.UserStats, error) {
19401940 stats.Pages = *pgs
19411941 return &stats, err
19421942 }
1943+
1944+func (me *PsqlDB) FindAccessLogs(userID string, fromDate *time.Time) ([]*db.AccessLog, error) {
1945+ logs := []*db.AccessLog{}
1946+ rs, err := me.Db.Query(
1947+ `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)
1948+ if err != nil {
1949+ return nil, err
1950+ }
1951+
1952+ for rs.Next() {
1953+ log := db.AccessLog{}
1954+ err := rs.Scan(
1955+ &log.ID, &log.UserID, &log.Service, &log.Pubkey, &log.Identity, &log.CreatedAt,
1956+ )
1957+ if err != nil {
1958+ return nil, err
1959+ }
1960+ logs = append(logs, &log)
1961+ }
1962+
1963+ if rs.Err() != nil {
1964+ return nil, rs.Err()
1965+ }
1966+
1967+ return logs, nil
1968+}
1969+
1970+func (me *PsqlDB) FindPubkeysInAccessLogs(userID string) ([]string, error) {
1971+ pubkeys := []string{}
1972+ rs, err := me.Db.Query(
1973+ `SELECT DISTINCT(pubkey) FROM access_logs WHERE user_id=$1`, userID,
1974+ )
1975+ if err != nil {
1976+ return nil, err
1977+ }
1978+
1979+ for rs.Next() {
1980+ pubkey := ""
1981+ err := rs.Scan(&pubkey)
1982+ if err != nil {
1983+ return nil, err
1984+ }
1985+ pubkeys = append(pubkeys, pubkey)
1986+ }
1987+
1988+ if rs.Err() != nil {
1989+ return nil, rs.Err()
1990+ }
1991+
1992+ return pubkeys, nil
1993+}
1994+
1995+func (me *PsqlDB) InsertAccessLog(log *db.AccessLog) error {
1996+ _, err := me.Db.Exec(
1997+ `INSERT INTO access_logs (user_id, service, pubkey, identity) VALUES ($1, $2, $3, $4);`,
1998+ log.UserID,
1999+ log.Service,
2000+ log.Pubkey,
2001+ log.Identity,
2002+ )
2003+ return err
2004+}
+12 -0 pkg/db/stub/stub.go #
......@@ -288,3 +288,15 @@ func (me *StubDB) VisitUrlNotFound(opts *db.SummaryOpts) ([]*db.VisitUrl, error)
288288 func (me *StubDB) FindUsersWithPost(space string) ([]*db.User, error) {
289289 return nil, errNotImpl
290290 }
291+
292+func (me *StubDB) FindAccessLogs(userID string, fromDate *time.Time) ([]*db.AccessLog, error) {
293+ return nil, errNotImpl
294+}
295+
296+func (me *StubDB) FindPubkeysInAccessLogs(userID string) ([]string, error) {
297+ return []string{}, errNotImpl
298+}
299+
300+func (me *StubDB) InsertAccessLog(log *db.AccessLog) error {
301+ return errNotImpl
302+}
+3 -0 pkg/pssh/logger.go #
......@@ -51,11 +51,14 @@ func LogMiddleware(getLogger GetLoggerInterface, database FindUserInterface) SSH
5151 }
5252
5353 if found {
54+ // identity provided by ssh-cert
55+ identity := s.Permissions().Extensions["identity"]
5456 if err == nil && user != nil {
5557 logger = logger.With(
5658 "user", user.Name,
5759 "userId", user.ID,
5860 "ip", s.RemoteAddr().String(),
61+ "identity", identity,
5962 )
6063
6164 SetUser(s, user)
+38 -11 pkg/shared/ssh.go #
......@@ -23,6 +23,7 @@ type AuthFindUser interface {
2323 FindUserByPubkey(key string) (*db.User, error)
2424 FindUserByName(name string) (*db.User, error)
2525 FindFeature(userID, name string) (*db.FeatureFlag, error)
26+ InsertAccessLog(log *db.AccessLog) error
2627 }
2728
2829 func NewSshAuthHandler(dbh AuthFindUser, logger *slog.Logger, principal string) *SshAuthHandler {
......@@ -33,11 +34,24 @@ func NewSshAuthHandler(dbh AuthFindUser, logger *slog.Logger, principal string)
3334 }
3435 }
3536
36-func PubkeyCertVerify(key ssh.PublicKey, srcPrincipal string) (string, error) {
37+type AuthedPubkey struct {
38+ OrigPubkey string
39+ Pubkey string
40+ Identity string
41+}
42+
43+func PubkeyCertVerify(key ssh.PublicKey, srcPrincipal string) (*AuthedPubkey, error) {
44+ origPubkey := utils.KeyForKeyText(key)
45+ authed := &AuthedPubkey{
46+ OrigPubkey: origPubkey,
47+ Pubkey: origPubkey,
48+ Identity: "pubkey",
49+ }
50+
3751 cert, ok := key.(*ssh.Certificate)
3852 if ok {
3953 if cert.CertType != ssh.UserCert {
40- return "", fmt.Errorf("ssh-cert has type %d", cert.CertType)
54+ return nil, fmt.Errorf("ssh-cert has type %d", cert.CertType)
4155 }
4256
4357 found := false
......@@ -48,34 +62,36 @@ func PubkeyCertVerify(key ssh.PublicKey, srcPrincipal string) (string, error) {
4862 }
4963 }
5064 if !found {
51- return "", fmt.Errorf("ssh-cert principals not valid")
65+ return nil, fmt.Errorf("ssh-cert principals not valid")
5266 }
5367
5468 clock := time.Now
5569 unixNow := clock().Unix()
5670 if after := int64(cert.ValidAfter); after < 0 || unixNow < int64(cert.ValidAfter) {
57- return "", fmt.Errorf("ssh-cert is not yet valid")
71+ return nil, fmt.Errorf("ssh-cert is not yet valid")
5872 }
5973 if before := int64(cert.ValidBefore); cert.ValidBefore != uint64(ssh.CertTimeInfinity) && (unixNow >= before || before < 0) {
60- return "", fmt.Errorf("ssh-cert has expired")
74+ return nil, fmt.Errorf("ssh-cert has expired")
6175 }
6276
63- return utils.KeyForKeyText(cert.SignatureKey), nil
77+ authed.Pubkey = utils.KeyForKeyText(cert.SignatureKey)
78+ authed.Identity = cert.KeyId
79+ return authed, nil
6480 }
6581
66- return utils.KeyForKeyText(key), nil
82+ return authed, nil
6783 }
6884
6985 func (r *SshAuthHandler) PubkeyAuthHandler(conn ssh.ConnMetadata, key ssh.PublicKey) (*ssh.Permissions, error) {
7086 log := r.Logger
7187 var user *db.User
7288 var err error
73- pubkey, err := PubkeyCertVerify(key, r.Principal)
89+ authed, err := PubkeyCertVerify(key, r.Principal)
7490 if err != nil {
7591 return nil, err
7692 }
7793
78- user, err = r.DB.FindUserByPubkey(pubkey)
94+ user, err = r.DB.FindUserByPubkey(authed.Pubkey)
7995 if err != nil {
8096 log.Error(
8197 "could not find user for key",
......@@ -91,6 +107,16 @@ func (r *SshAuthHandler) PubkeyAuthHandler(conn ssh.ConnMetadata, key ssh.Public
91107 return nil, fmt.Errorf("username is not set")
92108 }
93109
110+ err = r.DB.InsertAccessLog(&db.AccessLog{
111+ UserID: user.ID,
112+ Service: r.Principal,
113+ Identity: authed.Identity,
114+ Pubkey: authed.OrigPubkey,
115+ })
116+ if err != nil {
117+ log.Error("cannot insert access log", "err", err)
118+ }
119+
94120 // impersonation
95121 var impID string
96122 usr := conn.User()
......@@ -108,8 +134,9 @@ func (r *SshAuthHandler) PubkeyAuthHandler(conn ssh.ConnMetadata, key ssh.Public
108134
109135 perms := &ssh.Permissions{
110136 Extensions: map[string]string{
111- "user_id": user.ID,
112- "pubkey": pubkey,
137+ "user_id": user.ID,
138+ "pubkey": authed.Pubkey,
139+ "identity": authed.Identity,
113140 },
114141 }
115142
+15 -0 sql/migrations/20251217_add_access_logs_table.sql #
......@@ -0,0 +1,15 @@
1+CREATE TABLE IF NOT EXISTS access_logs (
2+ id uuid NOT NULL DEFAULT uuid_generate_v4(),
3+ user_id uuid NOT NULL,
4+ service character varying(255) NOT NULL,
5+ pubkey text NOT NULL DEFAULT '',
6+ identity text NOT NULL DEFAULT '',
7+ data jsonb NOT NULL DEFAULT '{}'::jsonb,
8+ created_at timestamp without time zone NOT NULL DEFAULT NOW(),
9+ CONSTRAINT access_logs_pkey PRIMARY KEY (id),
10+ CONSTRAINT fk_access_logs_app_users
11+ FOREIGN KEY(user_id)
12+ REFERENCES app_users(id)
13+ ON DELETE CASCADE
14+ ON UPDATE CASCADE
15+);
Back to top