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?
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
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.
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
If someone can detail the step by step 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.
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.
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.
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?
So, is the API endpoints the best way to obtain the data? Could I use the “Basic HTTP Auth”?
What about a connection by SFTP?
Thanks for your help!
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.
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?
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:
…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"
./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.
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
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