Ignore exceptions in select query

Question

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.

You can wrap it in a [plpgsql function](https://www.postgresql.org/docs/15/sql-createfunction.html), where you try to get rows one by one, `return next` if it worked, catch and ignore exception, skipping the row if it didn't. — Zegarek, Dec 29 '22 at 18:24
@Zegarek hi, thank you for the comment. I tried a query which I just posted in the question but it doesn't return anything. Can you help me fix that query? thanks. — Waqar Ahmed, Dec 29 '22 at 18:28
That query tries to retrieve all rows at once and just gives up when it fails. You're supposed to query row by row and skip the ones that fail - [here's an example](https://dbfiddle.uk/R4XXgf6N) loosely based on [this](https://stackoverflow.com/q/20325687/5298879) - here a cursor might not work if the exception is thrown upon opening it. See if `limit` and `offset` let you query without an error and it might be your way around the rows that throw one. You could also just `select * from aws_s3_bucket where account_id in (123456789,)` — Zegarek, Dec 29 '22 at 19:23
It also seems like you could [set steampipe to ignore this type of error, returning a null instead](https://github.com/turbot/steampipe-plugin-aws/blob/main/CHANGELOG.md#v0600-2022-05-25). — Zegarek, Dec 29 '22 at 19:44
@Zegarek thanks for that. I totally missed that configuration. Regarding that pglsql function, I will test it tomorrow. Just one question, how can i return a complete row instead of manually writing all the columns name in DECLARE. — Waqar Ahmed, Dec 29 '22 at 22:43
*return a complete row instead of manually writing all the columns* - I think you already are returning a complete row using your current `return query select * `. Do you have some other example you could share? Assigning column values one by one is handy when you want to use `return next` in a table-returning function. — Zegarek, Dec 30 '22 at 14:09

Zegarek · Accepted Answer · 2023-04-28T13:21:21.197

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"]
  # ...
}

To ignore an exception raised inside a loop and continue looping rather than exit the routine, you can nest a plpgsql BEGIN..EXCEPTION..END, wrapping the exception-throwing part: demo

create table aws_s3_bucket(example text);
insert into aws_s3_bucket values 
  ('1'),
  ('2'),
  ('not a number'),--this will cause an error if I try casting to numeric
  ('3'),
  ('4');

CREATE OR REPLACE FUNCTION skip_exceptions() RETURNS setof numeric AS $BODY$
DECLARE current_value  text;
BEGIN
  FOR current_value IN SELECT example
                       FROM aws_s3_bucket LOOP
     BEGIN RETURN NEXT current_value::numeric;
     EXCEPTION WHEN OTHERS THEN NULL;--do nothing, keep looping
     END;
  END LOOP;
END $BODY$ LANGUAGE plpgsql;

--note the "not a number" entry is ignored without raising exception
SELECT * FROM skip_exceptions();
-- skip_exceptions
-------------------
--               1
--               2
--               3
--               4

Ignore exceptions in select query

1 Answers1