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:
|
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)