Ignore exceptions in select query-CodePudding

I am using a tool called steampipe which allows to query cloud resources using postgresql interface. I am running simple query to print all buckets on AWS S3:

select
  *
from
  aws_s3_bucket

But since there are buckets which I don't have access to, the query raises the following exception:

Error: operation error S3: GetBucketLocation, https response error StatusCode: 403, RequestID: ASDA34343DS, HostID: asdf35234234sfsfdt3453453454dgdfbvxvcg==, api error AccessDenied: Access Denied (SQLSTATE HV000)

and it fails. Is it possible (probably in postgresql) to ignore the rows which raises the exception (inaccessible buckets) and prints the rows which doesn't raise any exception?

For example, this is my aws_s3_bucket table:

 --------------------------------------------------------- ----------- -------------- 
| name                                                    | region    | account_id   |
 --------------------------------------------------------- ----------- -------------- 
| my-bucket-1                                             | us-east-1 | 123456789    |
| my-bucket-2                                             | us-east-1 | 123456789    |
| other-user-bucket-3                                     | us-east-1 | 987654321    |
| my-bucket-4                                             | us-east-1 | 123456789    |
 --------------------------------------------------------- ----------- --------------

Since other-user-bucket-3 belongs to other user, I am unable to query its properties hence, raises exception. Therefore, I want to ignore that exception and return only this:

 --------------------------------------------------------- ----------- -------------- 
| name                                                    | region    | account_id   |
 --------------------------------------------------------- ----------- -------------- 
| my-bucket-1                                             | us-east-1 | 123456789    |
| my-bucket-2                                             | us-east-1 | 123456789    |
| my-bucket-4                                             | us-east-1 | 123456789    |
 --------------------------------------------------------- ----------- --------------

I tried the following query, but it doesn't return anything:

CREATE OR REPLACE FUNCTION abc()
  RETURNS SETOF aws_s3_bucket AS
$BODY$
BEGIN
   RETURN QUERY
   SELECT * FROM aws_s3_bucket;
   EXCEPTION
        WHEN OTHERS THEN
            NULL;
END
$BODY$
LANGUAGE plpgsql;

Any help will be appreciated.

CodePudding user response：

Steampipe starting with v0.60.0 lets you ignore the permission errors, returning a null for the inaccessible rows instead of raising an exception. You'll need to uncomment and configure ignore_error_codes in ~/.steampipe/config/aws.spc:

connection "aws" {
  plugin = "aws"
  # ...
  # List of additional AWS error codes to ignore for all queries.
  # By default, common not found error codes are ignored and will still be ignored even if this argument is not set.
  ignore_error_codes = ["AccessDenied", "AccessDeniedException", "NotAuthorized", "UnauthorizedOperation", "UnrecognizedClientException", "AuthorizationError"]
  # ...
}