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]