I’ve decided to take a step back to see if I can connect to the DB without exposing any ports.
If I enter the container I see this:
# netstat -lp | grep postgres
tcp 0 0 0.0.0.0:postgresql 0.0.0.0:* LISTEN -
tcp6 0 0 [::]:postgresql [::]:* LISTEN -
unix 2 [ ACC ] STREAM LISTENING 263612292 - /var/run/postgresql/.s.PGSQL.5432
If I exit the container and I’m in my remote server (not on my local computer yet), shouldn’t I be able to connect using this?
/var/discourse# psql -h localhost -d discourse -U postgres
The problem is that I get a password prompt. Since the postgres user doesn’t have one, I tried creating a different user and assigning them a password:
CREATE USER whatever_user WITH ENCRYPTED PASSWORD '<whatever password>';
GRANT CONNECT ON DATABASE discourse TO whatever_user;
GRANT USAGE ON SCHEMA public TO whatever_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO whatever_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO whatever_user;
I added a line for that user with
pg_hba.conf and restarted PG with
service postgresql restart
# Database administrative login by Unix domain socket
local all postgres peer
local all whatever_user md5
However, when I try to connect from the remote server I get an authentication failure:
# psql -h localhost -d discourse -U whatever_user
Password for user whatever_user:
psql: FATAL: password authentication failed for user "whatever_user"
FATAL: password authentication failed for user "whatever_user"
What am I missing? I’m trying to at least be able to connect to the DB from the same server. Step 2 would be doing the same using an SSH tunnel, but I guess I need to take care of step 1 first. Any help is appreciated.