Shift Left Join: Find secrets everywhere

There are many ways to scan source code repositories for secrets, but they can also be hiding in all sorts of infrastructure nooks and crannies. Steampipe's code plugin provides a novel way to find them. Its code_secret table doesn't map a particular API, as other plugins do. Instead it searches columns of other tables for secrets. Join code_secret with any other table in the Steampipe ecosystem to look for secrets in those tables' columns.

Consider, for example, the user_data column of the aws_ec2_instance table. It contains scripts that configure an instance at launch. If secrets are lurking there, this query will find them.

select
  instance_id,
  secret_type,
  secret
from
  code_secret,
  aws_ec2_instance
where src = user_data

+---------------------+------------------------------+-----------------------------------------------
| instance_id         | secret_type                  | secret                                        
+---------------------+------------------------------+-----------------------------------------------
| i-02a4257fe2f08496f | basic_auth                   | https://joe:passwd123                         
| i-02a4257fe2f08496f | azure_storage_account_key    | mllhBNrG467B7Q5iT+ePFr6eLCE24ij9vT/fCeckOunfqz
| i-02a4257fe2f08496f | github_personal_access_token | 45ab6f911111f9f376a5b52c25d22113f2b45fa1      
| i-02a4257fe2f08496f | okta_token                   | 00Am7B2M_U-63q_Ppd6tDzAbBOkvcCht-kDG-baM7t    
| i-02a4257fe2f08496f | stripe_api_key               | sk_live_tR3PYbcVNZZ796tH88S4VQ2u              
| i-02a4257fe2f08496f | slack_api_token              | xoxp-5228148520-5228148525-1323104836872-10674
| i-02a4257fe2f08496f | aws_access_key_id            | AKIA4YFAKFKFYXTDS353                          
+---------------------+------------------------------+-----------------------------------------------
    

That's obviously a cooked example, but imagine that these kinds of secrets are stashed in several EC2 instances running in many regions across many AWS accounts. If you've configured Steampipe to look in all those places, using region wildcards and a connection aggregator, that 8-line snippet of SQL will search everywhere for secrets in EC2 user_data.

Where else might secrets be hiding?

Secrets in Elastic Container Service task definitions

Similar to user_data, task definitions govern the deployment of containers on a managed cluster of EC2 instances. Those definitions can set environment variables and run scripts, and might include secrets. To find them, join aws_ecs_task_definition with code_secret.

with cdefs as (
  select
    task_definition_arn,
    container_definitions::text as cdef
  from
    aws_ecs_task_definition
  order by
    task_definition_arn
)
select
  s.secret_type,
  c.*
from 
  cdefs c,
  code_secret s
where
  s.src = c.cdef

+------------------------------+--------------------------------------------------------------------------------+
| secret_type                  | task_definition_arn                                                            |
+------------------------------+--------------------------------------------------------------------------------+
| github_personal_access_token | arn:aws:ecs:us-west-1:605491513981:task-definition/first-run-task-definition:2 |
| okta_token                   | arn:aws:ecs:us-west-1:605491513981:task-definition/first-run-task-definition:2 |
| aws_access_key_id            | arn:aws:ecs:us-west-1:605491513981:task-definition/first-run-task-definition:2 |
+------------------------------+--------------------------------------------------------------------------------+
    

Secrets in CodeBuild projects

An AWS CodeBuild project uses a buildspec that contains commands and settings. That's another place where secrets shouldn't be. Join aws_codebuild_project with code_secret to find them.

with code_build as (
  select
    name,
    source::text
  from
    aws_codebuild_project
  order by
    name
)
select
  s.secret_type,
  c.*
from 
  code_build c,
  code_secret s
where
  s.src = c.source

+------------------------------+--------+
| secret_type                  | name   |
+------------------------------+--------+
| github_personal_access_token | cb-01  |
+------------------------------+--------+
    

Secrets sent to CloudWatch logs by AWS Lambda functions

Are your Lambda functions inadvertantly logging secrets? Join aws_cloudwatch_log_event with code_secret to find out.

with data as (
  select 
    timestamp,
    message
  from 
    aws_cloudwatch_log_event a
  where 
    a.log_group_name = '/aws/lambda/aws-lambda-01'
)
select 
  d.*,
  s.*
from 
  data d,
  code_secret s
where 
  s.src = d.message

+----------------------+--------------------+
| timestamp            | secret_type        |
+----------------------+--------------------+
| 2021-11-09T15:52:29Z | aws_access_key_id  |   
+----------------------+--------------------+
  

Secrets in DNS TXT records

It's nice that we can put anything at all into our DNS TXT records, but what if we accidentally put secrets there? Here's a 3-way join to find them, involving the net_dns_record table, the CSV plugin, and code_secret. The setup here: a file called domains.csv has a list of your domains. Join that with net_dns_record to find the TXT records for your domains, then join with code_secret to check for secrets.

with dns_data as (
  select
    *
  from 
    net_dns_record n
  join
    csv.domains d
  using
    (domain)
  where
    type = 'TXT'
    and value is not null
)
select 
  *  
from 
  dns_data,
  code_secret
where
  src = value

Secrets elsewhere

As our cloud infrastructure grows ever more complex, there will be more nooks and crannies in which secrets can hide. Queries like these can flush them out. And as we showed in Using SQL to check spreadsheet integrity, it's straightforward to embed such queries in custom controls that run automated checks and tabulate results.

Take a look at the Steampipe ecosystem of plugins, think about which tables can usefully join with code_secret, share your ideas with the Steampipe community, and maybe contribute a new secret: it's easy and fun!