0

Question

I set up an AWS RDS PostgreSQL 10.4 database. The dataset is in a private network, therefore, I use a bastion to forward the connection to it. I can then connect using Postico but not using psql.

Details

I installed PostgreSQL locally using brew install postgres. The command output is (and on a AWS instance see update):

❯ psql --version
psql (PostgreSQL) 10.5

Then I use the following SSH command to tunnel:

ssh -q -o "ConnectTimeout 3" -o "StrictHostKeyChecking no" -o "UserKnownHostsFile /dev/null" -i bastion-key.pem -L 5432:xxxxxx.xxxxx.eu-central-1.rds.amazonaws.com:5432 ec2-user@xx.xx.xx.xx -N

Then I connect using Postico .. everything fine!

Last but not least I try to connect using the following command:

❯ psql \
   --host=127.0.0.1 \
   --port=5432 \
   --username=USERNAME \
   --password \
   --dbname=DBNAME
Password for user USERNAME:
psql: FATAL:  password authentication failed for user "USERNAME"
FATAL:  password authentication failed for user "USERNAME"

I use a macOS High Sierra Version 10.13.6 Macbook 13".

Update 2018-12-05:

Tried it also from a Amazon image running Amazon Linux release 2 (Karoo) same result with psql 9 as version 10. Version 10 was installed following this recommendation.

lony
  • 6,733
  • 11
  • 60
  • 92
  • Did brew by any chance start a local postgres which is already running on port 5432? To be safe use other local port for your tunnel and psql command so you know for certain you are not connecting to local postgresql server. – Eelke Nov 22 '18 at 11:39
  • ps aux is not showing any process and as I tested it with Postico before I quite certain its the AWS one – lony Nov 22 '18 at 12:35
  • In that case from the error you get I can see you have a connection and nothing in the pg_hba is stopping you so you must have an error in your username or password. Note postgresql makes identifiers lowercase when you do not quote them so if you did `create user MyUser ...` the username will actually become `myuser`! – Eelke Nov 22 '18 at 17:20
  • The username is lowercase and has no special characters I just substituted it with the USERNAME string – lony Nov 22 '18 at 20:17
  • Could it be a hashing issue? – lony Nov 23 '18 at 09:27
  • Could you post the commands you gave to create the user and set the password? – Eelke Nov 23 '18 at 10:42
  • `CREATE ROLE "{}" LOGIN PASSWORD '{}';` – lony Nov 23 '18 at 12:28

1 Answers1

0

Solved, still a really shitty problem!

I generated my PostgreSQL passwords using secrets.token_urlsafe(90) Python3 method, which resulted in strings including [_-] which psql could not handle.

Using secrets.choice(string.ascii_uppercase) 1 with the right selection solved the problem!

Long story short: Use only characters and digits for your password and you will have no problems!

lony
  • 6,733
  • 11
  • 60
  • 92