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.

Answer 1

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