Redshift is deprecating SSL v1.0 and 1.1

Question

Hi,
I received an email from AWS that Redshift is deprecating SSL v1.0 and 1.1. To find out where these connections are coming from and which queries are they, the email suggests querying the STL_CONNECTION_LOG table. I can see the TLS v1 connections but I am not able to see the queries being issued. 
I created the below query but it seems that the PID column is not unique and gets reused many times. Can you please share a new query to use?
We use provisioned Redshift.
Thanks

```
select pid, starttime, enddate
from 
SELECT distinct
	q.pid,
    q.query AS query_id,
    LEFT(q.querytxt, 500) AS query_text,  -- Truncate long queries
    q.starttime AS time_stamp,
	s.recordtime, 
    u.usename AS user_name,
    q.userid AS user_id,
    s.sslversion,
    q.endtime,
    DATEDIFF(seconds, q.starttime, q.endtime) AS duration_seconds,
    q.aborted,
    q.database
FROM 
    stl_query q
LEFT JOIN 
    pg_user u ON q.userid = u.usesysid
LEFT JOIN 
    stl_connection_log s ON q.pid = s.pid 
WHERE 
    q.starttime >= DATEADD(day, -1, GETDATE())  -- Last 24 hours
    AND q.userid > 1  -- Exclude superuser queries if desired
	and s.sslversion = 'TLSv1                                             '
ORDER BY 
    q.starttime DESC
LIMIT 500;
```

re:Post Agent · Answer

The issue with your query is that the PID column in Redshift is indeed not unique and gets reused, making it difficult to accurately match queries with specific SSL connections. Here's an improved query that will help you identify queries running over TLSv1 connections:

```sql
SELECT 
q.query AS query_id,
LEFT(q.querytxt, 500) AS query_text,
q.starttime AS query_start_time,
c.recordtime AS connection_time,
u.usename AS user_name,
c.sslversion,
q.database,
DATEDIFF(seconds, q.starttime, q.endtime) AS duration_seconds,
q.aborted
FROM 
stl_connection_log c
JOIN 
stl_query q ON c.pid = q.pid
AND q.starttime >= c.recordtime
AND q.starttime <= DATEADD(minute, 60, c.recordtime)
JOIN 
pg_user u ON q.userid = u.usesysid
WHERE 
c.sslversion LIKE 'TLSv1%'
AND c.recordtime >= DATEADD(day, -7, GETDATE())
AND q.userid > 1
ORDER BY 
c.recordtime DESC, q.starttime
LIMIT 500;
```

This query joins the STL_CONNECTION_LOG and STL_QUERY tables with a time-based condition to ensure that queries are associated with the correct connection. It looks for queries that started after a TLSv1 connection was established and within a reasonable time window (60 minutes) of that connection.

If you need to analyze query wait times or execution details for these TLSv1 queries, you could also join with STL_WLM_QUERY to get information about queue times and execution times, or with SVL_QLOG for a more comprehensive log of query execution.

For more detailed analysis, you might want to adjust the time window or add additional filters based on specific users or databases that you suspect are using the deprecated SSL versions.  
**Sources**  
[Reviewing queue wait times for queries - Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/dg/review-queue-wait-times-for-queries.html)  
[SVL_QLOG - Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/dg/r_SVL_QLOG.html)

Redshift is deprecating SSL v1.0 and 1.1

Add your answer

Relevant content