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!