Can I run commands from aws_s3 extensions as non-root on PostgreSQL?

Question

I'm trying to copy data from S3 to PostgreSQL (Amazon AWS RDS). I've set up the following:

CREATE EXTENSION aws_s3 cascade schema reporting;
CREATE DATABASE my_database;
\connect my_database;  -- reconnect to my_database
CREATE SCHEMA my_schema;
SET password_encryption = 'md5'; -- required by the Airflow connector I'm using
CREATE USER airflow_devel WITH PASSWORD 'my_password';
ALTER DATABASE my_database OWNER airflow_devel;
ALTER USER airflow_devel IN DATABASE my_database SET search_path TO 'my_schema';

Then I run

SELECT aws_s3.table_import_from_s3(
    '"table_name"',  /*table_name*/
    '',  /*empty column_list for all columns*/
     '(FORMAT CSV, HEADER FALSE, DELIMITER ''|'')',  /*options*/
    aws_commons.create_s3_uri(
        'my_bucket',
        'my/s3/key',
        'eu-central-1'
    )  /*s3_info*/
);

It runs fine using the master user. However, when I run from just-created user airflow_devel, I get

permission denied for schema aws_s3

I haven't found an explanation on the internet why this function and extension should not be possible from non-root users. But is this assumption wrong? What does the message permission denied for schema aws_s3 mean? I mean, aws_s3 is not a schema anyway. I'm grateful for any advice or alternatives!

Answer 1

With some research I found the solution was already given here: https://stackoverflow.com/questions/66535534/permission-denied-for-function-table-import-from-s3

We need to run

GRANT USAGE ON schema aws_s3 TO airflow_devel;

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA aws_s3 TO airflow_devel;

That makes it work for me.