Connecting to database externally via PHP


(D) #1

I wish to connect to my Discourse database externally from my site’s homepage. My forum is located on a different server than my main website, and it’s PHP-based so I understand I need to use pg_connect() to accomplish this. The trouble I’m having is that it requires a password to do this, and I cannot find anything regarding a password for the Discourse database.

What I’m using:

$host = "host = forum.website.com";
$port = "port = 54320";
$dbname = "dbname = postgres";
$creds = "user = postgres password = ??";
$db = pg_connect("$host $port $dbname $creds");

if(!$db) {
 echo "Error";
} else {
  echo "Success";
}

Is the answer simply to set a password for the postgres user?


(Matt Palmer) #2

Don’t set a password for the postgres user; that’s the “superuser” account that can do anything (including create/drop databases, tables, etc). Instead, create a separate user, and grant it the minimum privileges required to achieve what you need to do, and only allow that user to connect remotely.


(D) #3

Say I simply just want to read the database and obtain a list of recent topics, would the LOGIN privilege be sufficient?

Edit: Will doing this require a rebuild for the effects to be seen?


(Michael Brown) #4

This seems like a LOT of work compared to using for example the /latest.json path to pull the latest topics in from your site in JSON format:

○ → curl -s https://meta.discourse.org/latest.json | jq '.topic_list.topics[].title' | head
"CSS Theme Contest (with Prizes!)"
"Welcome to meta.discourse.org"
"How do I configure \"Show Full Post\""
"Connecting to database externally via PHP"
"Topic List Previews"
"Lost files after upgrade, got them back, and don't really know why"
"Zendesk Integrations"
"Ability to feature groups on the About page"
"How does one change a username via the API?"
"Locations Plugin"

If you really want to do this I would suggest for example checking Postgres documentation and examples on how to create read-only users.


(Jay Pfaffman) #5

As suggested above you almost certainly don’t want to do that. Back up and describe what you want to do. The API is probably the solution.


(Mittineague) #6

IIRC the API allows access to Data Explorer and should be easier than setting up a role with SELECT permission and putting safeguards in place.


(D) #7

What I currently do is pull from my myBB forum. I have three boxes on my homepage, each box pulls the 10 topics with the latest post/bump and their respective topic creators from a different category. For example, one box pulls the latest Announcements from an announcement category.


(Michael Brown) #8

Perfect. You can view the API docs, or as a short-cut you can add .json to any navigable path to get the equivalent in JSON. For example:

→ curl -s https://meta.discourse.org/c/releases.json | jq '.topic_list.topics[].title' | head
"About the releases category"
"Discourse Version 2.2"
"Discourse Version 2.1"
"Discourse Version 2.0"
"Discourse Version 1.9"
"Discourse Version 1.8"
"Discourse Version 1.7"
"Discourse Version 1.6"
"Discourse Version 1.5"
"Discourse Version 1.4"

There is more data in the JSON file (such as the topic creator) which you can parse, but this is the way to go.


(D) #9

(Unsure if the right thing to do was continue on this topic or create a new one)

Ok, so after finally finding some time to actually begin parsing all the necessary information I need, I’m running into a problem I’m unfamiliar with. As a refresher, I’m using /latest.json to fetch latest threads from within a specific category to display on my websites homepage. I’ve successfully done this–parsing topic titles and user_id–however I wish to display the username rather than the user_id. I know usernames are listed in a different object, and I’m not quite sure how to access them with the user_id.

Currently I have this (again, this is PHP):

    foreach($topics as $value){
      if($value['category_id'] == '6'){

        print $value['title'];


        foreach($value['posters'] as $p){
          print $p['user_id'];
        }
      }
    }

So I need to match the above printed $p['user_id'] with the corresponding username below.

The usernames are separate from the topic_list / topics:

{
   "users":[
      {
         "id":13,
         "username":"UserA",
         "avatar_template":"/url.png"
      },
      {
         "id":5,
         "username":"UserB",
         "avatar_template":"/url.png"
      },
      {
         "id":11,
         "username":"UserC",
         "avatar_template":"/url.png"
      }
]

This is my first time actually parsing multidimensional arrays from JSON using PHP, so I apologize if this is a rather simple task to accomplish.


(Jay Pfaffman) #10

You might have a look at the RSS feeds: https://meta.discourse.org/c/support.rss.


(Simon Cossar) #11

The code in this function might be a useful reference: https://github.com/scossar/wp-discourse-shortcodes/blob/master/lib/discourse-topic-formatter.php#L61. It’s a WordPress plugin, but the code in the function is mostly regular PHP. The function’s $discourse_topics argument is an array created from /latest.json. A cooked key has been added to the array to add an optional excerpt.

The original poster’s usename is found by getting the Original Poster user_id from the posters array, and then finding that user_id in the users array. That happens here: https://github.com/scossar/wp-discourse-shortcodes/blob/master/lib/discourse-topic-formatter.php#L106.


(D) #12

Thank you, I was attempting to avoid nesting loops as much as possible, but I guess it’s unavoidable lol

I ended up getting everything to work with this code:

foreach($topicsM as $value){
         while($value['category_id'] == '6'){
                $slug = $value['slug'];
                echo "<span><span><a href='https://forum.site.net/t/$slug'>" . $value['title'] . "</a></span>";

                foreach($value['posters'] as $p){
                  while(strpos($p['description'], 'Original Poster') !== false){
                    foreach($usersM as $u){
                      while($u['id'] == $p['user_id']){
                        echo "<span>" . $u['username'] . "</span></span>";
                        break;
                      }
                    }
                    break;
                }
              }
              break;
          }
 }

(Michael Brown) #13

Before you parse the topics, you could parse all the user IDs from the feed into a hash, then you can avoid the inner loop.