README

RFC: Modernizing System Authentication Logs (lastlog, btmp, utmp, wtmp) with SQLite

Status:                    Individual Submission (Draft)
Author:                    Roman Bakshansky
Date:                      March 12, 2026
Discussion:                <https://github.com/bakshansky/linux-auth-logs>



1.  Introduction

   The system logs lastlog (last login time), btmp (failed login
   attempts), utmp (current sessions), and wtmp (login/logout history)
   are fundamental components of security auditing and monitoring in
   Linux.  Their formats were defined in the 1980s and have remained
   virtually unchanged since then.  All of them use fixed-structure
   records that include a 32-bit time field (time_t in lastlog, tv_sec
   in utmpx).  This creates a number of serious problems that become
   critical as the year 2038 approaches.

   The key words "MUST", "MUST NOT", "REQUIRED", "SHALL", "SHALL NOT",
   "SHOULD", "SHOULD NOT", "RECOMMENDED", "NOT RECOMMENDED", "MAY", and
   "OPTIONAL" in this document are to be interpreted as described in
   BCP 14 [RFC2119] [RFC8174] when, and only when, they appear in all
   capitals, as shown here.

1.1.  The Year 2038 Problem (Y2038)

   On January 19, 2038, the 32-bit seconds counter will overflow.  Even
   on 64-bit systems, due to ABI compatibility requirements, the time
   fields remain 32-bit.  Therefore, all Linux systems, regardless of
   architecture, are affected by this problem.

1.2.  Other Systemic Limitations

   The existing formats suffer from additional limitations that make
   them unsuitable for modern requirements:

   - Lack of extensibility.  Adding any new field (e.g., container ID,
     service name, source IP) requires changing the structure and
     recompiling all programs that work with these files.  In practice,
     this makes format evolution impossible.

   - Poor query performance.  Utilities (last, lastb, who, lastlog) are
     forced to scan files linearly.  When logs grow to hundreds of
     megabytes or gigabytes, this leads to unacceptable delays and
     excessive disk I/O load.

   - No atomicity or integrity.  Writing to a binary file is not an
     atomic operation.  If a writing process crashes in the middle of
     an operation, the file can become corrupted.

   - Concurrency problems.  Multiple processes may try to write to the
     same file simultaneously (e.g., sshd and login during concurrent
     logins).  Traditional file locking (flock) addresses contention
     but does not guarantee atomicity and can lead to deadlocks.

1.3.  Scope

   This document proposes a complete replacement of the legacy binary
   logs with specialized libraries that use SQLite as the embedded
   storage engine.  The proposed solution covers all four types of logs
   and forms a unified ecosystem for managing authentication data.  It
   is intended for discussion within the Linux community and does not
   currently represent any stream's official position.



Bakshansky                                                      [Page 1]






2.  Current State and Its Limitations

2.1.  Data Formats

   - lastlog (usually /var/log/lastlog): a file with fixed-size records,
     each corresponding to one UID.  The struct lastlog (from
     <lastlog.h>) contains:

        struct lastlog {
            time_t ll_time;           /* last login time, 32 bits */
            char   ll_line[UT_LINESIZE];
            char   ll_host[UT_HOSTSIZE];
        };

   - btmp (/var/log/btmp): the log of failed login attempts.  It uses
     the same structure as utmp/wtmp.

   - utmp (/var/run/utmp): current sessions.

   - wtmp (/var/log/wtmp): login/logout history.

   For utmp/wtmp/btmp, the struct utmpx defined in <utmpx.h> is used.
   According to POSIX, it must contain at least:

        struct utmpx {
            char   ut_user[];   /* user login name */
            char   ut_id[];     /* unspecified initialization process
                                    identifier */
            char   ut_line[];   /* device name (terminal) */
            pid_t  ut_pid;      /* process ID */
            short  ut_type;     /* type of entry */
            struct timeval ut_tv; /* time entry was made */
        };

   The ut_tv field is of type struct timeval, where tv_sec is int32_t.
   Most implementations also include fields like ut_host and others,
   but these are not standardized.

2.2.  Lack of Extensibility

   A fixed record size means that adding any new field (e.g.,
   container_id, service_name, source_ip) requires changing the
   structure, which immediately breaks backward compatibility.  Old
   programs reading the file would misinterpret the new data, leading
   to errors or crashes.  As a result, the format has remained frozen
   for decades, unable to reflect system evolution.

2.3.  Query Performance

   Utilities that work with these files MUST read them sequentially.
   For example:

   - last MUST traverse the entire wtmp file from end to beginning to
     obtain a list of recent logins for a user.  With millions of
     records, this takes seconds and creates significant disk I/O load.

   - lastb MUST scan the whole btmp file to count failed attempts.

   - lastlog reads the record at offset UID * sizeof(struct lastlog),
     which is efficient for single queries but does not support complex
     queries like "show all users who logged in after a given date"
     (that would require a full file scan).



Bakshansky                                                      [Page 2]






2.4.  Integrity and Atomicity

   Writing to a binary file is not atomic.  If a process crashes in the
   middle of a write, the file MAY end up partially written – a portion
   of the record is written, the rest is not.  For security auditing,
   losing or corrupting even a single record is unacceptable.  File
   locking mechanisms (flock) do not solve atomicity; they only prevent
   concurrent writes.

2.5.  Concurrency

   Multiple processes MAY try to write to the same file concurrently
   (e.g., sshd and login during simultaneous logins).  Traditional file
   locking addresses contention but does not guarantee record-level
   atomicity and can lead to deadlocks.



3.  Requirements for a New Solution

   A new system for storing session and authentication data MUST meet
   the following requirements:

   REQ1: Time scale.  It MUST support time beyond 2038 using a 64-bit
         representation.

   REQ2: Extensibility.  It MUST allow adding new fields without
         breaking existing software.

   REQ3: Query performance.  It SHOULD provide indexes for common
         queries (by user, time range, event type).  Queries of the
         form "last N records for user X since time Y" MUST execute in
         better than linear time.

   REQ4: Atomicity and integrity.  It MUST guarantee that each record
         is either fully stored or not stored at all, even in the event
         of a system crash.  This implies ACID compliance.

   REQ5: Concurrency.  It MUST support simultaneous writes by multiple
         processes without data loss and with minimal locking.

   REQ6: Portability.  It MUST work on all Linux systems, including
         embedded systems and musl-based containers, regardless of the
         presence of systemd.

   REQ7: Backward compatibility.  It MUST allow a gradual transition
         without breaking existing tools and scripts.

   REQ8: Uniformity.  All logs SHOULD be managed through similar
         interfaces to ease learning and maintenance.



4.  Proposed Solution: SQLite-Based Libraries

   We propose using SQLite – an embedded relational database management
   system that is widely used, in the public domain, and has no
   external dependencies.  SQLite is ideal for system programming due
   to its small footprint, reliability, and rich feature set.

   For each of the four log types, a separate public shared library
   with a corresponding C interface is created:

   - liblastlog2 – for storing the last login time (replaces
     /var/log/lastlog).



Bakshansky                                                      [Page 3]






   - libbtmp2 – for the failed login attempts log (replaces
     /var/log/btmp).

   - libutmp2 – for current sessions (replaces /var/run/utmp).

   - libwtmp2 – for login/logout history (replaces /var/log/wtmp).

   All libraries follow a common approach but MAY have different table
   schemas optimized for their specific tasks.  They MAY be implemented
   as separate shared libraries or combined into one with different
   entry points – this is open for discussion.

   The libraries provide functions for initialization, adding records,
   executing parameterized queries, and database maintenance (purging
   old records, optimization).

4.1.  Preliminary Database Schema (Subject to Discussion)

   Below is a draft of the table structures.  This is not a final
   version – it is open for discussion and MAY be changed based on
   community input.

   For event logs (btmp, utmp, wtmp) a common table events is proposed
   with the following fields:

        Field         SQLite Type     Semantics
        ============  ==============  ================================
        id            INTEGER PRIMARY Unique record identifier
                      KEY AUTOINCREMENT
        timestamp     INTEGER         Event time in microseconds since
                                      epoch (64-bit)
        type          INTEGER         Record type (login, logout,
                                      reboot, etc.)
        pid           INTEGER         Process ID
        user          TEXT            Username
        line          TEXT            Terminal (e.g., "tty1", "pts/0")
        host          TEXT            Remote host (may be empty)
        service       TEXT            Name of the service that created
                                      the record (e.g., "sshd", "login")
                                      – new field
        source_ip     TEXT            Source IP address (if applicable)
                                      – new field
        container     TEXT            Container identifier (if
                                      applicable) – new field

   For lastlog2 – a lastlog table with the user as the primary key:

        Field           SQLite Type     Semantics
        ==============  ==============  ==============================
        user            TEXT PRIMARY    Username
                        KEY
        last_timestamp  INTEGER         Last login time (microseconds)
        last_line       TEXT            Last terminal
        last_host       TEXT            Last remote host
        last_service    TEXT            Last service
        last_source_ip  TEXT            Last source IP





Bakshansky                                                      [Page 4]






   Indexes SHOULD be created for the most frequent queries.  Example:

        CREATE INDEX idx_user_time ON events(user, timestamp);
        CREATE INDEX idx_time ON events(timestamp);
        CREATE INDEX idx_service ON events(service);

   These indexes will enable queries like "last 10 records for user X"
   to execute in logarithmic time.

4.2.  Preliminary API (Subject to Discussion)

   Below is a draft of the API for libwtmp2 (similar for other
   libraries).  This is not a final version – only a starting point for
   discussion.  All details (function names, parameter types, error
   handling) MAY be changed.

        /* Open/create the database.
           path – full path to the database file (e.g.,
                  "/var/lib/wtmp/wtmp.db").
           flags – combination of flags: O_RDONLY, O_RDWR, O_CREAT.
           Returns 0 on success, -1 on error (errno is set). */
        int wtmp_open(const char *path, int flags);

        /* Close the database. */
        void wtmp_close(void);

        /* Add an event record.  Parameters correspond to table fields.
           Optional parameters (e.g., container) MAY be NULL.
           Returns 0 on success, -1 on error. */
        int wtmp_add(int type, pid_t pid, const char *user,
                     const char *line, const char *host,
                     const char *service, const char *source_ip,
                     const char *container);

        /* Retrieve the last N records for a user starting from a given
           time.  The result is returned as an array of structures that
           MUST be freed by the caller using wtmp_free_entries().
           Returns the number of records or -1 on error. */
        ssize_t wtmp_get_recent(const char *user,
                                uint64_t since_timestamp,
                                int limit,
                                struct wtmp_entry **entries);

        /* Get the number of failed login attempts for a user after a
           given time.  (Useful for PAM modules similar to
           pam_lastlog2.) */
        int wtmp_get_failed_count(const char *user,
                                  uint64_t since_timestamp,
                                  uint64_t *count);

        /* Delete records older than a given timestamp (for log
           rotation). */
        int wtmp_prune(uint64_t before_timestamp);

        /* Optimize the database (VACUUM, rebuild indexes). */
        int wtmp_maintenance(void);

        /* Free memory allocated for entries. */
        void wtmp_free_entries(struct wtmp_entry *entries,
                               size_t count);




Bakshansky                                                      [Page 5]






   For liblastlog2 the API might look like:

        /* Open/create the lastlog database.
           path – full path to the database file (e.g.,
                  "/var/lib/lastlog/lastlog.db").
           flags – combination of flags: O_RDONLY, O_RDWR, O_CREAT.
           Returns 0 on success, -1 on error (errno is set). */
        int lastlog_open(const char *path, int flags);

        /* Close the lastlog database. */
        void lastlog_close(void);

        /* Update the last login information for a user.
           If the user does not exist, a new record is created.
           Returns 0 on success, -1 on error. */
        int lastlog_update(const char *user, uint64_t timestamp,
                           const char *line, const char *host,
                           const char *service, const char *source_ip);

        /* Query the last login information for a user.
           The result is returned in the provided structure.
           Returns 0 on success, -1 if the user is not found or on error. */
        int lastlog_query(const char *user,
                          struct lastlog_entry *entry);

        /* Get a list of all users who have logged in since a given time.
           The result is returned as an array of strings that MUST be
           freed by the caller using lastlog_free_users().
           Returns the number of users or -1 on error. */
        int lastlog_get_all_users_since(uint64_t since_timestamp,
                                        char ***users, size_t *count);

        /* Free memory allocated for the user list. */
        void lastlog_free_users(char **users, size_t count);

   All functions SHOULD be designed with multithreading in mind:
   except for open/close, they SHOULD be reentrant and MAY be called
   concurrently from different threads, because SQLite in WAL mode
   supports concurrent reads and one write transaction.

4.3.  Advantages of Using SQLite

   - 64-bit time.  The INTEGER type in SQLite is stored as a signed
     64-bit value, permanently solving the Y2038 problem.

   - Indexes.  Enable complex queries in logarithmic time, radically
     reducing disk I/O load.

   - Extensibility.  New columns can be added with ALTER TABLE; old
     records remain readable, and old programs simply ignore unknown
     columns.

   - ACID.  SQLite guarantees atomicity, consistency, isolation, and
     durability.  Even in the event of a system crash, a record is
     never lost or partially written.

   - Concurrency (WAL mode).  Write-Ahead Logging allows multiple
     simultaneous reads and one write, perfectly matching the scenario
     with several writing processes.

   - Portability.  SQLite runs on any platform with a C compiler,
     including embedded systems and musl environments.

   - Proven.  SQLite is used by billions of devices and applications;
     its reliability is well known.



Bakshansky                                                      [Page 6]






5.  Comparison with Alternative Approaches

5.1.  Simple Extension of the Binary Format

   One could define new structures with 64-bit time and enlarged
   fields.  However, such an approach:

   - does not solve the extensibility problem;
   - provides no indexes;
   - does not guarantee atomicity;
   - requires synchronizing changes across dozens of projects, which is
     practically impossible.

5.2.  Using systemd-journald

   journald collects all events, but:

   - it is tied to systemd, leaving systems without systemd out of the
     solution;
   - it requires libsystemd, complicating tools;
   - it lacks a simple file-based interface;
   - it does not guarantee ACID at the individual record level.

5.3.  Keeping the Status Quo

   Doing nothing guarantees a Y2038 disaster and retains all the
   existing shortcomings.  This option is unacceptable.

5.4.  Why SQLite Is the Optimal Solution

   SQLite simultaneously solves all identified problems: Y2038,
   performance, extensibility, integrity, concurrency, and portability.
   No other approach offers the same set of advantages in a single
   solution.



6.  Migration Plan

   A dual-write strategy ensures a smooth transition.

   1.  Adapting writing programs.  Programs that currently write to
       lastlog, btmp, utmp, wtmp (e.g., login, sshd, su, sudo, cron)
       SHOULD be modified to write both to the old binary file (using
       existing mechanisms) and to the new SQLite database via the
       appropriate library.  The changes are minimal – a call to
       *_add() after the traditional write.

   2.  Developing new reading utilities.  New versions of utilities
       (last2, lastb2, who2, lastlog2) SHOULD be created that read from
       the SQLite databases and utilize indexes.  Old utilities
       continue to work with the old files.

   3.  Distribution switchover.  Distributions MAY ship the new
       utilities alongside the old ones and include patches for dual
       writes.  After 2–3 years, most systems will have migrated to the
       new libraries.

   4.  Deprecating the old formats.  When the fraction of systems
       relying on the old files becomes negligible, writing to them MAY
       be disabled, and support for reading them MAY eventually be
       removed from standard utilities.

   Backward compatibility is maintained at all stages.



Bakshansky                                                      [Page 7]






7.  Open Questions for Discussion

   1.  Separation or unification of libraries.  Should we create
       separate libraries or one common library (e.g., libsession2)?

   2.  Naming.  What names should the new libraries and utilities have?
       Keep historical names (lastlog2, btmp2) or choose more generic
       ones?

   3.  Database location.  Store in /var/lib/ (as application state) or
       in /var/log/?  Given that databases are updated, not merely
       appended, /var/lib/ seems more appropriate.

   4.  Versioning scheme.  Use PRAGMA user_version to track schema
       versions and apply migration scripts?

   5.  Internationalization.  Store strings in UTF-8, converting legacy
       8-bit data if necessary?

   6.  Performance under high load.  Which SQLite settings (synchronous,
       cache_size, journal_mode) are optimal for servers and embedded
       systems?

   7.  Security.  Should encryption or integrity checks be considered?
       Access permissions (group adm)?

   8.  Integration with PAM and NSS.  Are new PAM modules needed?  Can
       data be exposed via NSS?

   9.  Fallback to a binary format for systems without SQLite.  There
       are embedded systems with tight memory or code size constraints
       where SQLite might be too heavy.  Should the libraries provide a
       simplified binary backend (with 64-bit time and fixed records)
       as a fallback?  If so, how to keep the API uniform?  With a
       binary backend, indexes and some functionality would be lost,
       but basic operations (add, read recent records) could remain.
       This would require a more complex library implementation
       (backend choice at compile time or via an environment variable).



8.  Security Considerations

   The proposed solution significantly improves the security posture of
   system authentication logs.  ACID compliance ensures that logs
   cannot be partially corrupted during system crashes.  Indexes and
   structured queries allow for faster forensic analysis.

   However, the introduction of SQLite also introduces new
   considerations:

   - File permissions MUST be set appropriately (e.g., group adm) to
     prevent unauthorized reading or modification.
   - The SQLite library SHOULD be kept up-to-date to address any
     security vulnerabilities.
   - If encryption is not implemented, the database files remain
     readable by anyone with file system access.  Implementations MAY
     consider using encrypted database formats or file-system-level
     encryption where required.

   The fallback binary option (if adopted) MUST maintain the same
   security properties as the SQLite backend to the greatest extent
   possible.



9.  IANA Considerations

   This document has no IANA actions.



Bakshansky                                                      [Page 8]






10.  Acknowledgements

   The author would like to thank the Linux community for their
   continued work on system auditing and for providing feedback on this
   proposal.



11.  References

11.1.  Normative References

   [RFC2119]  Bradner, S., "Key words for use in RFCs to Indicate
              Requirement Levels", BCP 14, RFC 2119,
              DOI 10.17487/RFC2119, March 1997,
              <https://www.rfc-editor.org/info/rfc2119>.

   [RFC8174]  Leiba, B., "Ambiguity of Uppercase vs Lowercase in
              RFC 2119 Key Words", BCP 14, RFC 8174,
              DOI 10.17487/RFC8174, May 2017,
              <https://www.rfc-editor.org/info/rfc8174>.

   [SQLite]   "SQLite", <https://www.sqlite.org/>.



Author's Address

   Roman Bakshansky

   Email: bakshansky@protonmail.com
   Email (for mailing lists): bakshansky.lists@gmail.com
   GitHub: https://github.com/bakshansky
   Discussion: https://github.com/bakshansky/linux-auth-logs


















Bakshansky                                                      [Page 9]