How to who uses Redshift in PostgreSQL

What does “who uses Redshift” mean?

It refers to querying system catalog and log tables to discover which database roles have connected, how often, and from where. This helps admins audit usage, control costs, and tighten security.

Which system tables expose Redshift user activity?

Use pg_user (all roles), stl_connection_log (connection history), and stv_recents (current sessions). These tables are available to the superuser by default; grant SELECT on them for read-only insight.

How do I list every role in the cluster?

Query pg_user to return usernames, creation dates, and default schemas. This is the Redshift-compatible equivalent of PostgreSQL’s pg_catalog.pg_user.

Example

SELECT usename AS username,
usesysid AS user_id,
valuntil AS password_expiry
FROM pg_user
ORDER BY username;

How do I find currently connected users?

stv_recents stores one row per active session. Filter by user, database, or client IP.

SELECT usename,
db_name,
starttime AS login_time,
remotehost
FROM stv_recents
WHERE state = 'Running';

How do I audit historical logins?

stl_connection_log keeps a multi-day record of logins and disconnects. Aggregate it to see login counts per user.

SELECT usename,
COUNT(*) AS login_count,
MIN(event_time) AS first_seen,
MAX(event_time) AS last_seen
FROM stl_connection_log
GROUP BY usename
ORDER BY login_count DESC;

How does this apply to an ecommerce database?

Join usage data with business tables to correlate user sessions with workload. For example, link stv_recents to Orders by user-defined tags or set-based access logic.

-- Flag running sessions that touch high-value orders
WITH vip_orders AS (
SELECT customer_id
FROM Orders
WHERE total_amount > 1000
)
SELECT r.usename,
r.remotehost,
o.customer_id
FROM stv_recents AS r
JOIN vip_orders AS o ON r.usename = ('cust_' || o.customer_id::text);

Best practices for monitoring who uses Redshift

1) Automate daily snapshots of stl_connection_log into a permanent table.
2) Grant least-privilege read access to usage views.
3) Schedule alerts when unknown roles connect or when session counts spike.

Common mistakes

Ignoring permission requirements

Many queries fail because non-superusers lack access to system tables. Fix by granting SELECT on the specific catalogs instead of full superuser rights.

Querying only pg_user

pg_user shows role existence, not activity. Always pair it with stv_recents or stl_connection_log for real usage insight.

Want a faster way to explore users?

Galaxy’s AI copilot auto-writes the above queries, names them, and shares them with your team in one click.