How to connect to pg database in console in Discourse installation?


(Anton) #1

I’ve tried to setup with a different pg locale, but I forgot to install that locale in ubuntu first, so I’d like to check if it worked, but how do I connect to the database from my DigitalOcean console?


How to use SQL to query discourse databse
(Jens Maier) #2

cd /var/docker
sudo ./launcher enter app
su -c 'psql discourse' postgres


How best to import users to migrate a mailing list to Discourse
Discourse sending 4 users many repeat notifications of new posts
(phil) #3

Any chance to be able to connect via a user friendly console like sequel pro ? or navicat ?
I am just ok at the SQL thing and WYSIWYG interface is really so much easier.

finger’s crossed.


(Jens Maier) #4

If you’re feeling adventurous, simply expose PostgreSQL’s TCP port in your Discourse docker container definition (app.yml) and restart the container. You might have to adjust Postgres’ security configuration (pg_hba.conf) to allow password-based logins from remote hosts, and you absolutely should protect the exposed port in your iptables rules.


(phil) #5

If someone can detail the step by step :wink: I’ll be taker.
I am currently connecting to my digital ocean droplet via ssh key, so I figured if I opened the postgresql port, and limit connection to ssh key , I should be ‘ok’.
I know it is not ideal but it seems like access to the db would be really nice.


(Michael Brown) #6

I would strongly suggest not opening it up. What specific problem are you trying to solve?

If you really do need it, I’d forward port 5432 from your local machine to the server over SSH and connect through that.


(Spero Koulouras) #7

pgadmin3 and teampostgresql both provide reasonable GUI front ends. I’ve used these to generate reports and make some DB content changes that would have been slow through the Discourse UI. Security is a real issue. I would not expose 5432 on the Internet.


(David García-Navas) #8

Similar to this question…

…if we have a tool to automate reports, I’m wondering about what would be the best way to import data from Discourse to one of this tool.

As I’ve read in this topic, SQL queries would produce a security hole?

SQL query

So, is the API endpoints the best way to obtain the data? Could I use the “Basic HTTP Auth”?

API Restful

What about a connection by SFTP?

SFTP


Thanks for your help!


(Jens Maier) #9

Not by themselves.

Exposing the database to the internet is bad practice and a potential security flaw since database servers are commonly optimized towards performance, not security. (This doesn’t mean that databases are inherently insecure, but they do lack features such as authentication attempt rate limiting.)

If you can gain access to the database in a secure manner, such as tunneling through SSH, connecting to the database directly is probably fine. Just be aware that you are now handling your live data and a single statement could instantly nuke everything, reverting your forum to the latest backup. You will either have to trust that your reporting/analysis tool is safe, or you can guard against it failing by creating a new role in PostgreSQL that can query but not alter Discourse’s database and tables.


(David García-Navas) #11

We’ve recently exposed port 5432 through app.yml, rebuild the app and finally we can connect via pgAdmin3.

Following your advice, we enable UFW for security, allowing the ip of the devices with pgAdmin3 and blocking other connections:

To                     Action      From
--                     ------      ----
5432                   ALLOW       my.ip
5432                   DENY        Anywhere
22                     ALLOW       Anywhere
80                     ALLOW       Anywhere
5432                   ALLOW       my.ip
5432 (v6)              DENY        Anywhere (v6)
22 (v6)                ALLOW       Anywhere (v6)
80 (v6)                ALLOW       Anywhere (v6)

But now it seems that it’s not working for connections via pgAdmin3:

could not connect to server: Operation timed out
Is the server running on host “domain.com” (server ip) and accepting TCP/IP connections on port 5432?

Could someone say if we have to edit some file to be able to connect this? perhaps postgresql.conf?


(David García-Navas) #12

Finally, we can run queries to postgres in order to use with pgAdmin or Klipfolio (the tool we use to visualize metrics). If someone have interest on this, here there are the steps we’ve followed:

1. Exposing the port
cd /var/discourse
nano containers/app.yml

Add this in expose: section:

 - "5432:5432"

…so it looks like:

## which TCP/IP ports should this container expose?
expose:
  - "80:80"   # fwd host port 80   to container port 80 (http)
  - "2222:22" # fwd host port 2222 to container port 22 (ssh)
  - "5432:5432"

Then, rebuild:

./launcher rebuild app
2. Create a only-read user
cd /var/discourse
./launcher enter app
su -c 'psql discourse' postgres
CREATE USER user WITH PASSWORD 'pass';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO user;
\du
\q
exit
3. Configure ufw firewall

Basically the same steps described here, only one step more. In your.ip we set the ip from our office in order to make request from pgAdmin and another three rules with the ip set in order to make requests with another app that make this automatically and represent in charts (in our case, Klipfolio)

ufw allow http
ufw allow https
ufw allow ssh
ufw allow from your.ip to any port 5432
ufw enable
  • The error in my past post was to add DENY rules that was blocking any request, including that from the our ip.
  • Enjoy!

/cc @Stackfish


By the way, as I’ve read in another topic:

So perhaps in the near future perhaps this steps are no longer necessary. I wish we could finally get a hosting plan with Discourse so we can support this feature :smile:


(Kane York) #13

It’s actually finished, I just haven’t gotten around to writing the announcement. GitHub - discourse/discourse-data-explorer: SQL Queries for admins in Discourse