STL_USERLOG

Records details for the following changes to a database user:

• Create user

• Drop user

• Alter user (rename)

• Alter user (alter properties)

This view is visible only to superusers. For more information, see Visibility of data in system tables and views.

Table columns

Column name	Data type	Description
userid	integer	ID of the user affected by the change.
username	character(50)	User name of the user affected by the change.
oldusername	character(50)	For a rename action, the original user name. For any other action, this field is empty.
action	character(10)	Action that occurred. Valid values: Alter Create Drop Rename
usecreatedb	integer	If true (1), indicates that the user has create database privileges.
usesuper	integer	If true (1), indicates that the user is a superuser.
usecatupd	integer	If true (1), indicates that the user can update system catalogs.
valuntil	timestamp	Password expiration date.
pid	integer	Process ID.
xid	bigint	Transaction ID.
recordtime	timestamp	Time in UTC that the query started.

Sample queries

The following example performs four user actions, then queries the STL_USERLOG view.

create user userlog1 password 'Userlog1';
alter user userlog1 createdb createuser;
alter user userlog1 rename  to userlog2;
drop user userlog2;

select userid, username, oldusername, action, usecreatedb, usesuper from stl_userlog order by recordtime desc;
                

 userid |  username | oldusername |  action | usecreatedb | usesuper
--------+-----------+-------------+---------+-------------+----------
    108 | userlog2  |             | drop    |           1 |   1
    108 | userlog2  | userlog1    | rename  |           1 |   1
    108 | userlog1  |             | alter   |           1 |   1
    108 | userlog1  |             | create  |           0 |   0
 (4 rows)