Tracking Last Login Time in YSQL

The log_connections parameter in YugabyteDB is a logging configuration option that records every connection attempt to the server to the PostgreSQL log file. It logs both successful and failed connection attempts, including client authentication (if required) and authorization completion.

It can be enabled at the cluster level via the ysql_pg_conf_csv gFlag, or directly at the database, user or session level.

Example:

For this test, I enabled log_connections at the universe level using the ysql_pg_conf_csv gFlag. We can verify this using a curl command and by checking the current value of the GUC:

yugabyte=# \! curl -s http://$(hostname -I | awk {'print $1'}):9000/varz?raw | grep ysql_pg_conf_csv
--ysql_pg_conf_csv=log_connections=on,shared_preload_libraries=pg_cron,cron.database_name=yugabyte

yugabyte=# SHOW log_connections;
 log_connections
-----------------
 on
(1 row)

The log_directory parameter reveals where the PostgreSQL log is stored:

yugabyte=# SHOW log_directory;
            log_directory
-------------------------------------
 /root/var/data/yb-data/tserver/logs
(1 row)

A grep command on the PostgreSQL log can be used to list user logins:

yugabyte=# \! grep "LOG:  connection authorized:" /root/var/data/yb-data/tserver/logs/post*.log
2025-03-19 15:41:57.235 UTC [3613760] LOG:  connection authorized: user=yugabyte database=yugabyte application_name=pg_isready
2025-03-19 15:47:27.533 UTC [3614364] LOG:  connection authorized: user=yugabyte database=yugabyte application_name=ysqlsh

If we create a new user and log in as that user, the login will be recorded in the PostgreSQL log:

yugabyte=# CREATE USER gus WITH LOGIN;
CREATE ROLE

yugabyte=# CREATE DATABASE some_db;
CREATE DATABASE

yugabyte=# \c some_db gus
You are now connected to database "some_db" as user "gus".

some_db=> \! grep "connection authorized" /root/var/data/yb-data/tserver/logs/post*.log
2025-03-19 15:41:57.235 UTC [3613760] LOG:  connection authorized: user=yugabyte database=yugabyte application_name=pg_isready
2025-03-19 15:47:27.533 UTC [3614364] LOG:  connection authorized: user=yugabyte database=yugabyte application_name=ysqlsh
2025-03-19 15:48:41.745 UTC [3614513] LOG:  connection authorized: user=gus database=some_db application_name=ysqlsh

In general, to check who last logged in, we can use a OS command like this:

some_db=> \! grep "connection authorized" /root/var/data/yb-data/tserver/logs/post*.log | sort -r | head -n 1
2025-03-19 15:48:41.745 UTC [3614513] LOG:  connection authorized: user=gus database=some_db application_name=ysqlsh

Instead of running OS commands to search the PostgreSQL logs, we can use a Foreign Table instead. That way, we can run a SQL query which is much easier.

yugabyte=# CREATE EXTENSION file_fdw;
CREATE EXTENSION

yugabyte=# CREATE SERVER postgres_log_server FOREIGN DATA WRAPPER file_fdw;
CREATE SERVER

yugabyte=# \! ls /root/var/data/yb-data/tserver/logs/postgresql*
/root/var/data/yb-data/tserver/logs/postgresql-2025-03-19_154156.log

yugabyte=# CREATE FOREIGN TABLE postgres_log (
yugabyte(#     raw_log TEXT
yugabyte(# ) SERVER postgres_log_server OPTIONS (
yugabyte(#     program 'grep "LOG:  connection authorized:" /root/var/data/yb-data/tserver/logs/postgresql-2025-03-19_154156.log',
yugabyte(#     format 'text'
yugabyte(# );
CREATE FOREIGN TABLE

yugabyte=# SELECT * FROM postgres_log;
                                                            raw_log
--------------------------------------------------------------------------------------------------------------------------------
 2025-03-19 15:41:57.235 UTC [3613760] LOG:  connection authorized: user=yugabyte database=yugabyte application_name=pg_isready
 2025-03-19 15:47:27.533 UTC [3614364] LOG:  connection authorized: user=yugabyte database=yugabyte application_name=ysqlsh
 2025-03-19 15:48:41.745 UTC [3614513] LOG:  connection authorized: user=gus database=some_db application_name=ysqlsh
 2025-03-19 15:50:29.597 UTC [3614727] LOG:  connection authorized: user=yugabyte database=yugabyte application_name=ysqlsh
(4 rows)

There is an issue with the above approach. The file specified in the Foreign Table definition does not support wildcards. By default, YugabyteDB rotates PostgreSQL log files, appending a date and time to the filename—meaning the log file name is constantly changing.

We need to create a Linux symlink that always points to the latest PostgreSQL log file:

yugabyte=# \! ln -sf $(ls -t /root/var/data/yb-data/tserver/logs/postgresql-*.log | head -1) /root/var/data/yb-data/tserver/logs/postgresql.log

yugabyte=# \! ls /root/var/data/yb-data/tserver/logs/postgresql.log
/root/var/data/yb-data/tserver/logs/postgresql.log

If you are running YugabyteDB on more than one node, which you probably are, you will need to create the symlink on all of the cluster nodes.

Let’s drop the Foreign Table and recreate it, this time pointing to the symlink.

yugabyte=# DROP FOREIGN TABLE postgres_log;
DROP FOREIGN TABLE

yugabyte=# CREATE FOREIGN TABLE postgres_log (
yugabyte(#     raw_log TEXT
yugabyte(# ) SERVER postgres_log_server OPTIONS (
yugabyte(#     program 'grep "LOG:  connection authorized:" /root/var/data/yb-data/tserver/logs/postgresql.log',
yugabyte(#     format 'text'
yugabyte(# );
CREATE FOREIGN TABLE

yugabyte=# SELECT * FROM postgres_log;
                                                            raw_log
--------------------------------------------------------------------------------------------------------------------------------
 2025-03-19 15:41:57.235 UTC [3613760] LOG:  connection authorized: user=yugabyte database=yugabyte application_name=pg_isready
 2025-03-19 15:47:27.533 UTC [3614364] LOG:  connection authorized: user=yugabyte database=yugabyte application_name=ysqlsh
 2025-03-19 15:48:41.745 UTC [3614513] LOG:  connection authorized: user=gus database=some_db application_name=ysqlsh
 2025-03-19 15:50:29.597 UTC [3614727] LOG:  connection authorized: user=yugabyte database=yugabyte application_name=ysqlsh
(4 rows)

We can create a database view that extracts key components like node_ip, login time, user name, and database name as separate columns for easy querying:

yugabyte=# CREATE VIEW postgres_log_vw AS
yugabyte-# SELECT inet_server_addr() node_ip,
yugabyte-#        substring(raw_log FROM '(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d{3} UTC)')::TIMESTAMPTZ AS login_time,
yugabyte-#        substring(raw_log FROM 'user=(\S+)') AS user_name,
yugabyte-#        substring(raw_log FROM 'database=(\S+)') AS database_name
yugabyte-#   FROM postgres_log
yugabyte-#  WHERE substring(raw_log FROM 'database=(\S+)') <> 'postgres'
yugabyte-#  ORDER BY 2;
CREATE VIEW

yugabyte=# SELECT * FROM postgres_log_vw;
    node_ip    |         login_time         | user_name | database_name
---------------+----------------------------+-----------+---------------
 74.208.XXX.XX | 2025-03-19 15:41:57.235+00 | yugabyte  | yugabyte
 74.208.XXX.XX | 2025-03-19 15:47:27.533+00 | yugabyte  | yugabyte
 74.208.XXX.XX | 2025-03-19 15:48:41.745+00 | gus       | some_db
 74.208.XXX.XX | 2025-03-19 15:50:29.597+00 | yugabyte  | yugabyte
(4 rows)

I can log into a different node and query the postgres_log_vw to identify who logged in there.

yugabyte=# SELECT * FROM postgres_log_vw;
    node_ip    |         login_time         | user_name | database_name
---------------+----------------------------+-----------+---------------
 198.71.XX.XXX | 2025-03-19 15:42:07.773+00 | yugabyte  | yugabyte
 198.71.XX.XXX | 2025-03-19 16:00:11.684+00 | yugabyte  | yugabyte
(2 rows)

To consolidate data from the postgres_log_vw view on each node, we can use the PostgreSQL Foreign Data Wrapper. To keep it simple, we can designate one node as the aggregator node, where all FDWs are defined—one for each additional node.

For a basic example, my cluster has only two nodes. From the node I chose as the aggregator, I can set up an FDW to the view on the second node in the cluster.

yugabyte=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION

yugabyte=# CREATE SERVER server2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '198.71.48.248', dbname 'yugabyte', port '5433');
CREATE SERVER

yugabyte=# CREATE USER MAPPING FOR yugabyte SERVER server2 OPTIONS (user 'yugabyte', password 'password');
CREATE USER MAPPING

yugabyte=# CREATE FOREIGN TABLE postgres_log_vw_server2 ( node_ip inet, login_time TIMESTAMP WITH TIME ZONE, user_name TEXT, database_name TEXT ) SERVER server2 OPTIONS (schema_name 'public', table_name 'postgres_log_vw');
CREATE FOREIGN TABLE

yugabyte=# SELECT * FROM postgres_log_vw_server2;
    node_ip    |         login_time         | user_name | database_name
---------------+----------------------------+-----------+---------------
 198.71.XX.XXX | 2025-03-19 15:42:07.773+00 | yugabyte  | yugabyte
 198.71.XX.XXX | 2025-03-19 16:00:11.684+00 | yugabyte  | yugabyte
 198.71.XX.XXX | 2025-03-19 17:24:46.961+00 | yugabyte  | yugabyte
(3 rows)

Finally, we can create another view where this one will consildate the data from the postgres_log_vw view on each node:

yugabyte=# CREATE VIEW postgres_log_consolidated_vw AS
yugabyte-# SELECT * FROM postgres_log_vw
yugabyte-# UNION ALL
yugabyte-# SELECT * FROM postgres_log_vw_server2
yugabyte-# ORDER BY login_time;
CREATE VIEW

yugabyte=# SELECT * FROM postgres_log_consolidated_vw;
    node_ip    |         login_time         | user_name | database_name
---------------+----------------------------+-----------+---------------
 74.208.XXX.XX | 2025-03-19 15:41:57.235+00 | yugabyte  | yugabyte
 198.71.XX.XXX | 2025-03-19 15:42:07.773+00 | yugabyte  | yugabyte
 74.208.XXX.XX | 2025-03-19 15:47:27.533+00 | yugabyte  | yugabyte
 74.208.XXX.XX | 2025-03-19 15:48:41.745+00 | gus       | some_db
 74.208.XXX.XX | 2025-03-19 15:50:29.597+00 | yugabyte  | yugabyte
 198.71.XX.XXX | 2025-03-19 16:00:11.684+00 | yugabyte  | yugabyte
 198.71.XX.XXX | 2025-03-19 17:24:46.961+00 | yugabyte  | yugabyte
(7 rows)

With our consolidated view, we can now see the last login (i.e., last access) of this YugabyteDB cluster. In my case, I want to identify the most recent non-admin user (e.g., yugabyte) who logged in…

yugabyte=# SELECT *
yugabyte-#   FROM postgres_log_consolidated_vw
yugabyte-#  WHERE user_name <> 'yugabyte'
yugabyte-#  ORDER BY login_time DESC
yugabyte-#  LIMIT 1;
    node_ip    |         login_time         | user_name | database_name
---------------+----------------------------+-----------+---------------
 74.208.XXX.XX | 2025-03-19 15:48:41.745+00 | gus       | some_db
(1 row)

Have Fun!