SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE'

Using Lamda function:

var AWS = require('aws-sdk');
AWS.config.update({
    region: 'us-west-2',
    maxRetries: 2,
    httpOptions: {
        timeout: 60000,
        connectTimeout: 5000
    }
});

var dbConfig = {
  user: '<username>',
  password: '<password>',
  database: '<database_name>',
  host: '<host>',
  port: 5432
};

var pg = require('pg');

async function getData(queryString) {
  const pool = new pg.Pool(dbConfig);
  var response = {
    "headers": { "Content-Type" : "application/json" },
    "isBase64Encoded": false,
    "body": ""
  };
  let res;
  let client;
  try {
    client = await pool.connect();
    res = await client.query(queryString);
        
    if (res.rows.length == 0) {
      response.statusCode = 204;
    } else {
      response.statusCode = 200;
      response.body = JSON.stringify(res);
    }
  } catch (err) {
    response.statusCode = 400;
    response.body = err;
  } finally {
    client.release()
  }
  return response;
}

exports.handler = async (event) => {
    if (!event.queryStringParameters || !event.queryStringParameters.QueryString) {
        return {
            "statusCode": 400,
            "body": "Please provide parameter: QueryString"
        }
    }
    const queryString = event.queryStringParameters.QueryString;
    return getData(queryString);
};

Test Event

{
  "queryStringParameters": {
    "QueryString": "SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE'"
  }
}

URL Get Method

{{url}}/dev/customQuery?QueryString=