ERROR: prepared statement does not exist (SQLSTATE 26000)

[Joeturbot]

Describe the bug I'm trying to build a mod where I can specify which CSV file I want to pull data from. If I statically set the CSV "table", Steampipe works just. I try to put in a variable, parameter and ${var.account}, I get an error that says:

ERROR: prepared statement "migration_checks_iam_turbot_grants_vs_aws_c30be" does not exist (SQLSTATE 26000)

I was unable to find anything meaningful in the Steampipe logs for today. They are attached.

Steampipe version (steampipe -v) ❯ steampipe -v steampipe version 0.13.0 ❯ steampipe plugin list +--------------------------------------------------+---------+----------------------------------------------+ | Name | Version | Connections | +--------------------------------------------------+---------+----------------------------------------------+ | hub.steampipe.io/plugins/turbot/aws@latest | 0.50.1 | sso_aab,test_aab,sso_aaa,sso_sandbox,sso_aac | | hub.steampipe.io/plugins/turbot/csv@latest | 0.2.0 | csv | | hub.steampipe.io/plugins/turbot/steampipe@latest | 0.2.0 | steampipe | +--------------------------------------------------+---------+----------------------------------------------+

To reproduce Reproduction Steps using the attached ZIP file. mod.sp, *.sql files are in the zip.

1. Setup the aws.spc to point to a valid AWS profile.
2. Setup the csv.spc file to point at the data directory. We need to be able to see aab in the CSV connections.
3. Run the steampipe check all. There is presently only one control, so this should go quickly.
4. Verify correct results.
5. Uncomment lines 20-22 and lines 25-27 in mod.sp. The param and variable blocks should be uncommented.
6. Uncomment line 5 from iam_turbot_grants_vs_aws.sql. Comment line 6. We want to switch from the staticly set table to whatever is specified in the account parameter.
7. Run steampipe check all.

Expected behavior The query will "compile" properly and execute based on the account variable I specify.

Additional context hcl_variable_interpolation.zip plugin-2022-03-16.log

[kaidaguerre]

thanks @Joeturbot I'll look into it

[Joeturbot]

Thanks @kaidaguerre !

[Joeturbot]

@kaidaguerre I found some more examples that blow up quite nicely and don't include any variable interpolation.

❯ steampipe check all

Pre, Intra and Post Checks for v3 to v5 Migrations .......... 5 / 5 [==========]
| 
+ Pre-Migration Health Checks ............................... 5 / 5 [==========]
  | 
  + IAM - Turbot Users with two AWS Access Keys ............. 1 / 1 [==        ]
  | 
  | ERROR: prepared statement "migration_checks_iam_users_two_keys_c5953" does …
  | 
  + IAM - Service Quotas for role counts .................... 1 / 1 [==        ]
  | 
  | ERROR: prepared statement "migration_checks_iam_service_quotas_roles_cfc69"…
  | 
  + IAM - Service Quota policies per user ................... 1 / 1 [==        ]
  | 
  | ERROR: prepared statement "migration_checks_iam_service_quotas_policies_per…
  | 
  + IAM - Service Quota policies per role ................... 1 / 1 [==        ]
  | 
  | ERROR: prepared statement "migration_checks_iam_service_quotas_policies_per…
  | 
  + IAM - Service Quota policies per group .................. 1 / 1 [==        ]

    ERROR: prepared statement "migration_checks_iam_service_quotas_policies_per…

Summary

OK .............................................................. 0 [          ]
SKIP ............................................................ 0 [          ]
INFO ............................................................ 0 [          ]
ALARM ........................................................... 0 [          ]
ERROR ........................................................... 5 [==========]

❯ steampipe -v
steampipe version 0.13.0
❯ steampipe plugin list
+--------------------------------------------------+---------+--------------------------------------------------------------------+
| Name                                             | Version | Connections                                                        |
+--------------------------------------------------+---------+--------------------------------------------------------------------+
| hub.steampipe.io/plugins/turbot/aws@latest       | 0.50.1  |  ... |
| hub.steampipe.io/plugins/turbot/azure@latest     | 0.23.2  |  ... |
| hub.steampipe.io/plugins/turbot/csv@latest       | 0.2.0   | csv  |
| hub.steampipe.io/plugins/turbot/gcp@latest       | 0.20.0  | ... |
| hub.steampipe.io/plugins/turbot/github@latest    | 0.12.0  | github                                                             |
| hub.steampipe.io/plugins/turbot/slack@latest     | 0.3.0   | slack                                                              |
| hub.steampipe.io/plugins/turbot/steampipe@latest | 0.2.0   | steampipe                                                          |
| hub.steampipe.io/plugins/turbot/terraform@latest | 0.0.5   | ... |
| hub.steampipe.io/plugins/turbot/turbot@latest    | 0.4.0   | ... |
+--------------------------------------------------+---------+--------------------------------------------------------------------+

more_examples.zip

[johnsmyth]

@Joeturbot I think it treats the sql file as a literal string. It works for me if I put the sql inline in the HCL:

control "iam_turbot_grants_vs_aws" {
    title = "IAM - Compare Turbot Grants exports to AWS"
    description = "Compare Turbot Grants to AWS.  Read CSV and AWS then compare"
#    sql = query.iam_turbot_grants_vs_aws.sql
#    param "account" {
#        default = var.account
#    }
  sql = <<-EOQ
-- Compare a CSV of Grants for a given account to the users and group membership in AWS.
with iam_export as (
    select group_name,
           user_name
    from ${var.account}
    --:from aab
),
     aws_groups as (
         select g.name                  as group_name,
                iam_user ->> 'UserName' as user_name
         from aws_iam_group g
                  cross join jsonb_array_elements(users) as iam_user
         where g.path like '%/turbot/%'
           and iam_user ->> 'Path' like '%/turbot/%'
     ),
     aws_users as (
         select iam_group ->> 'GroupName' as group_name,
                u.name                    as user_name
         from aws_iam_user u
                  cross join jsonb_array_elements(groups) as iam_group
         where u.path like '%/turbot/%'
           and iam_group ->> 'Path' like '%/turbot/%'
     )
select ie.group_name                                                  as exp_group_name,
       ie.user_name                                                   as exp_user_name,
       g.group_name                                                   as g_group_name,
       g.user_name                                                    as g_user_name,
       u.group_name                                                   as u_group_name,
       u.user_name                                                    as u_user_name,
       ie.group_name                                                  as resource,
       'info'                                                        as status,
       'there should be no null fields' as reason
from iam_export ie
         left join aws_groups g on ie.group_name = g.group_name and ie.user_name = g.user_name
         left join aws_users u on ie.group_name = u.group_name and ie.user_name = u.user_name

EOQ
}

[judell]

@joeturbot here are a few different ways to do it from a dashboards perspective.

I love how dashboards give us a way to create shareable/executable artifacts that document such things.

locals {
  target_table = "aab"
}

query "users_from_csv" {
  sql = <<EOQ
    select
      *
    from
      ${local.target_table}
  EOQ
}

dashboard var_interp {

  table {
    title = "no interpolation"
    sql = <<EOQ
      select
        *
      from
        csv.aab
    EOQ
  }

 chart {
    type = "table"
    title = "inline sql with interpolation of target_table ${local.target_table}"
    sql = <<EOQ
      select
        *
      from
        $local.target_table
    EOQ
  }

 chart {
    type = "table"
    title = "named query with interpolation of target_table ${local.target_table}"
    query = query.users_from_csv
  }

}

[Joeturbot]

@judell If you'd like to see the broader selection of queries for this migration, you can find it https://github.com/turbot/tdk/tree/master/migration/steampipe-mod-turbot-migration

Thanks for the pointers on dashboards!

[judell]

I've simplified the above a bit, to reflect my still-evolving understanding of the relationship among args / param / named query / inline query / HCL resource.

broader selection of queries

:eyes:

[johnsmyth]

@judell you should remove args = [ local.target_table ] since it doesnt do anything

[judell]

And here is a repro of the error you saw.

@kaidaguerre we do a great job, in general, of not punting to ERROR: prepared statement does not exist (SQLSTATE 26000), this is one case where we still do. It happens when the inline SQL contains $local.target_table} instead of "$local.target_table}". If we aspire to never show that error, and always show the underlying one, this belongs on the list of cases to address. Which is a list I should, but haven't yet, made explicit.

[judell]

@johnsmyth indeed, done, thanks, even simpler!

Like I said: evolving understanding of the various moving parts :-)

[judell]

And doing it that way takes us back to the happy place w/respect to the underlying error.

[Joeturbot]

@judell Am I to understand that ERROR: prepared statement does not exist (SQLSTATE 26000) is just a wrapper for an underlying problem? Is there anywhere that I can go see the underlying error?

[judell]

@Joeturbot yes, I think so. A prior example: https://github.com/turbot/steampipe/issues/1207.

You can look in the current database or plugin log in /.steampipe/logs but I suspect you won't find anything. I expect there will be a dashboard log too, where such a thing might appear.

@kaidaguerre would you say that, in general, ERROR: prepared statement does not exist (SQLSTATE 26000)] masks an underlying problem that we would ideally surface to the user?

[Joeturbot]

@judell with a switch in perspective on 'prepared statement not found' from "the statement is missing" to "there's a bug that prevents compilation", I've had much more success with getting my queries/controls to run. My most common problem has been missing columns in GROUP BY clauses.

[judell]

✔️

[kaidaguerre]

@kaidaguerre would you say that, in general, ERROR: prepared statement does not exist (SQLSTATE 26000)] masks an underlying problem that we would ideally surface to the user?

possibly - the problem is there are a wide number of. issues that could lead to that error - ranging from a typo in the reference to the prepared statement, through to subtle SQL issues in the prepared statement. We certainly need to continue to surface as many underlying errors as possible, but a general solution may not be possible

[Joeturbot]

I'm going to close this as it's a broader problem than just the Turbot plugin.