Connect phpPgAdmin to Discourse PostgreSQL


(Stephen Kerr Jr) #1

I have been racking my brain on these forums for the past day and I am a little stuck.

Currently:
I have installed Discourse from the main install instructions on Digital Ocean. I have Discourse installed via Docker. I installed NGINX and Wordpress and PHP and MySQL all on a single VPS.

I have been able to get everything running so far, Wordpress and Discourse. I am now attempting to connect phppgadmin to the local PostgreSQL database to view and administer databases. I have gotten so far as to get phppgadmin installed, but I am lost when it comes to getting it connected. Currently it lets me see the Database, but it prompts for a username and password and none of the ones I am using work.

I have attempted to connect to the psql instance via command line, but I have not be able to successfully connect to great a password for the postgres user. I get this error message:
root@server:/var/discourse# psql -h 127.0.0.1 -U postgres -d discourse
Password for user postgres:
psql: FATAL: password authentication failed for user "postgres"
FATAL: password authentication failed for user “postgres”

I have passed the 5432 port through the app.yml.
I have also granted the following access in the pg_hba.conf file:

    # TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             172.17.42.1/32          trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                trust
#host    replication     postgres        127.0.0.1/32            trust
#host    replication     postgres        ::1/128                 trust

I cant seem to login and I have no idea where any file would be that I need to modify to allow me to login to the psql instance. Any help to allow a username\password to authenticate against the database would be greatly appreciated.


(Stephen Kerr Jr) #2

Still very interested in finding out how to activate user/pass access to the PostgreSQL Discourse database


(Stephen Kerr Jr) #3

Well it seems I am a bit overwhelmed with the complexity of Discourse. Due to the inability to access the PostgreSQL database, not being able to have access to an “easy to use” API, and the inability to connect receive the help I am looking for on these forums, I am going to have to stear away form using Discourse as my user back-end (which I was hoping it could be).
I have Discourse setup with wordpress and I wish there was a better plugin to allow Discourse to be embeded into a Wordpress page or anything so I dont have to make the user go to an entirely different website.

For now I think I will proceed with attempting to setup SSO to WordPress and use that as my back-end user database. This should hopefully give me the freedom I am looking for to pull data on users, check login-status, and pretty much anything else I need.


(czep) #4

Dredging up this question because I too have the same issue: new install of discourse and my first curiosity is to see what’s in the db. I can get an idea from the models in the source, but would like to explore the actual db structure.

I don’t even see a discourse db in psql. Forgive my ignorance of the platform, but does docker somehow encapsulate the postgresql server?

Should I be able to “psql -U somebody -h somewhere discourse” and thus connect to the db?

Thanks!


(Kenny Meyer) #5

Same question here - where can I find the default Postgres password? Searched everywhere inside the app of the docker container.


(Kenny Meyer) #6

I wanted to apply a PG dump from Heroku to the discourse app inside Docker, this is what I did:

  1. Entered Docker instance ./launch enter app
  2. su - postgres
  3. pg_restore --verbose --clean --no-acl --no-owner -h /var/run/postgresql -U discourse -d discourse latest.dump assuming the backup is in the current folder and it’s called latest.dump.
  4. Voilá

(Simon Eichenauer) #7

This is certainly not best practice, but for lack of documentation I used this approach:
I installed the docker image with port 5432 reachable from outside, then

./launcher enter app
su - postgres
psql
ALTER ROLE postgres WITH PASSWORD 'your-new-password';