EDIT: my two posts are minutes apart because I forgot to press Reply to the first one. I’ve since done some more digging.
EDIT2: So here is what I think is going on.
There seems to be a problem with
ident identification on WSL. i.e. the operating system username, on its own, doesn’t seem to allow clients to connect to Postgres.
So two problems here - one Postgres installed on the wrong port and user identification seems not to work on WSL.
The Postgres server is running on 5433 and rake is expecting it on 5432.
I installed Postgres (using the Discourse dev setup script) into the Windows Subsystem for Linux but I already had a Postgres server running on port 5432 in the native Windows system.
I assume that as a result the WSL Postgres server got bumped to 5433. So, after uninstalling the Postgres windows server, I changed the
postgresql.conf file to start on port 5432. This then allowed me to connect to Postgres - by using
su - postgres.
Validation of User
By trying to connect to the server, now running on the correct port, using
psql it would ask me for a password for the user with my username
Now I created the
danny Postrges user, using @techAPJ’s instructions here…
I obviously changed
danny but at no time in the creation process was I asked for a password.
My assumption here is that the Postgres
createuser command creates a Postgres user linked to a local user and that it relies on the operating system to identify and validate the user - hence no password?
So why was I being asked for a password when I was logged in as the user
When I looked in the 'pg_hba.conf` (which controls how users gain access for different types of connection - local, tcp, etc.) file…
# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
# Database administrative login by Unix domain socket
local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 ident
# IPv6 local connections:
host all all ::1/128 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5
method of connection for the
danny user was set to
ident - i.e let the operating system handle identification. But again, I was logged in as
danny and was still challenged for my password. So I don’t know why that wasn’t working.
To test if this was causing the problem, and because this is my dev system with no crucial data, I changed the
ident setting to
trust which it seems allows a connection with no challenge at all.
Et voila, I was connected and it worked - the
rake command executed and I’ve started the server and got the party time splash screen.
I note that the instructions for creating the database user on the Beginners Guide to Install Discourse on Ubuntu for Development thread changed in the not so distant past.
The instructions used to be (revision 45).
Open psql prompt as postgres user
sudo -u postgres psql postgres
Create role with the same name as your ubuntu system username with discourse as password:
CREATE ROLE techapj WITH LOGIN ENCRYPTED PASSWORD 'discourse' CREATEDB SUPERUSER;
I wonder if these instruction might still need to be followed for WSL? i.e. explicitly specify a password.
IF I get time I will try a reinstallation and test at each stage because the
trust solution does feel like a solution, TBH.