Heroku Postgres: AWS s3 presigned URL not working

user_13278594 0
Viewed 572

I am 6 hours into this error and going mad.

I have created a heroku app using the CLI and pushed the git successfully however I need to initialise a bunch of tables using db.create_all() before the app works, but I have 6gb of data in my localhost postgres that I wish to migrate to heroku for use.

Instantiating the tables in the usual way (create_all) won't work because certain content is required from tables for the index page hence my tables must be pre-populated with said localhost data before it will work.

I have followed the documentation here to the letter and exported my .dump file and uploaded it to my bucket: https://devcenter.heroku.com/articles/heroku-postgres-import-export#import

Public sharing is enabled, and I have used setx commands to set ACCESS_KEY_ID and SECRET_ACCESS_KEY in the AWS CLI. I have also set heroku config:set AWS_ACCESS_KEY_ID=matching_aws_cli_blah AWS_SECRET_ACCESS_KEY=matching_aws_cli_blah.

Then using the command aws s3 presign s3_url I have copied the output (which I'll add looks different to AWS documentation I used https://awscli.amazonaws.com/v2/documentation/api/latest/reference/s3/presign.html)

An example of how mine looks (randomly changed some figures) https://mybucketname.s3.eu-west-2.amazonaws.com/mydb.dump?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=LLLLL4C7LLLLLLLNLLLQ%2FNNNNNN08%2Feu-west-2%2Fs3%2Faws4_request&X-Amz-Date=20201208T004613Z&X-Amz-Expires=3600&X-Amz-SignedHeaders=host&X-Amz-Signature=ll9d80nnnnc457ea83298fdnnnn4a25b0c9ll066f54e6ff8acf42dafnnnea8877 whereas the documentation provides a nice neat AWSAccessKeyId= variable.

I have only one bucket and the Block public access (bucket settings) is set to OFF and the file itself is share-able publicly. The object URL initialises the download fine, including in incognito.

When using the following command (as per heroku documentation)

heroku pg:backups:restore 'https://mybucketname.s3.eu-west-2.amazonaws.com/mydb.dump?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=LLLLL4C7LLLLLLLNLLLQ%2FNNNNNN08%2Feu-west-2%2Fs3%2Faws4_request&X-Amz-Date=20201208T004613Z&X-Amz-Expires=3600&X-Amz-SignedHeaders=host&X-Amz-Signature=ll9d80nnnnc457ea83298fdnnnn4a25b0c9ll066f54e6ff8acf42dafnnnea8877' DATABASE_URL

I get the following output to terminal.

Restoring... !
 !    An error occurred and the backup did not finish.
 !
 !    waiting for restore to complete
 !    pg_restore finished with errors
 !    waiting for download to complete
 !    download finished with errors
 !    please check the source URL and ensure it is publicly accessible
 !
 !    Run heroku pg:backups:info r001 for more details.
'X-Amz-Credential' is not recognized as an internal or external command,
operable program or batch file.
'X-Amz-Date' is not recognized as an internal or external command,
operable program or batch file.
'X-Amz-Expires' is not recognized as an internal or external command,
operable program or batch file.
'X-Amz-SignedHeaders' is not recognized as an internal or external command,
operable program or batch file.
'X-Amz-Signature' is not recognized as an internal or external command,
operable program or batch file.

which in the log file shows

=== Backup r001
Database:         BACKUP
Started at:       2020-12-08 00:47:33 +0000
Finished at:      2020-12-08 00:47:34 +0000
Status:           Failed
Type:             Manual
Backup Size:      0.00B (0% compression)

=== Backup Logs
2020-12-08 00:47:34 +0000 2020/12/08 00:47:34 aborting: could not write to output stream: Expected HTTP Status 200, received: "400 Bad Request"
2020-12-08 00:47:34 +0000 pg_restore: error: could not read from input file: end of file
2020-12-08 00:47:34 +0000 waiting for restore to complete
2020-12-08 00:47:34 +0000 pg_restore finished with errors
2020-12-08 00:47:34 +0000 waiting for download to complete
2020-12-08 00:47:34 +0000 download finished with errors
2020-12-08 00:47:34 +0000 please check the source URL and ensure it is publicly accessible

Please can someone point where I am going wrong?

Thanks!

amazon-web-services heroku amazon-s3 postgresql
2 Answers

The trick is to pass extra quotes to the heroku command.

It looks something like this in:

  • powershell:

    $url = "https://provider/bucket/file"
heroku pg:backups:restore -a <your-app-name> "`"$url`""

  • bash:

    url="https://provider/bucket/file"
heroku pg:backups:restore -a <your-app-name> "$url"
user_80776680

In order to debug this, it might be useful to get the response error message from the request to your AWS url, which you can easily do with curl:

curl <SIGNED AWS URL>

In my case I got the following message:

Error parsing the X-Amz-Credential parameter; the region 'us-east-1' is wrong; expecting 'eu-west-3'

Which means that for some reason AWS is signing my URL with the wrong region. If I try to manually add the right region to my signed url then the request fails because the signatures don't match.

Instead, I need to make AWS sign the URL correctly by specifying the region in the presign command:

aws s3 presign s3://URL --region eu-west-3

when I did this and used the resulting signed url with the heroku pg:backups:restore command, the request was successful and heroku was able to import my db.

user_93475350
Related