Database Queries / Data Explorer / Source Control / Non-Live Instance?

Support
1

Hi,

We have a hosted Discourse solution, and recently I’ve had a little experiment with the Data Explorer plugin to run some queries.

This has led to a discussion regarding source control for the queries, which lead to a discussion regarding having access to a non-live instance. Below are some hypothetical thoughts, there isn’t a specific reason for needing any of the below at this time - just putting it out there for feedback etc.

So far, the queries have only been written using the plugin, whilst I assume these are also included in the backup that is taken, I am not entirely familiar with the process of a recovery from this backup. I have personally come from an MS background, we used SQL Server’s Management Studio, RedGate Tools etc and setup repositories for each database (on both live/dev environments).

By only using the Data Explorer plugin, and not accessing the database directly, any PostreSQL client tools are presumably of little use to us. This would leave us perhaps writing the scripts in the Plugin, then copying them out to a separate file and committing them to a repository.

Additionally, as a bit of a PostreSQL noob, trying to brush the dust off of his SQL writing skills, must queries via the plugin in go through a series of iterations before a) they work at all b) they return anything close to what I wanted. It dawns on me that firing these against the live environment is perhaps less than ideal.

This brings us to the point regarding a non-live instance. I have often thought it was a bit risky making our HTML/CSS changes on our only copy, as anything we did was reflected straight away and as such it was/is quite easy to screw things up for live users. In the same vein, whilst my little SQL scripts are fairly light weight, and in comparison to the work being done by the forum itself I’m sure amount to very little, it would be nice to perhaps develop/test/play on a non-live environment for putting the queries together, but perhaps one that is routinely populated from our backup data.

Any thoughts/feedback on this would be really appreciated. :slight_smile:

2

AFAIK, the data explorer plugin is safe to use on a live site because it can do only SELECT queries, and those are subject to time and result set limits.

A problem might be using those same queries in the database console. If you run the query in data explorer with “include query plan” checked you can see information that might help with avoiding problems.

2 Likes
3

Data Explorer can only read, not write. This is enforced at the connection level. So there’s zero risk of damaging anything. The main risk is exposing people’s emails or cookies, though cookies get rotated very quickly and the cookie is a hash fingerprint match, not an exact copy of the cookie string.

4 Likes
4

Just expanding on that, the cookie is a hash of “a secret string” (stored either in environment or Redis) and data in the database. There is no risk that the auth cookie can leak unless environment or redis is exposed as well. If a DB is exposed the biggest risk is that passwords and emails can leak. In particular BAD passwords can potentially be brute force attacked since the salt is stored in the database. Keep in mind though 10 char passwords (which is our minimal default is not that easy to brute force attack)

4 Likes
5

Thanks for all of the feedback regarding the safety aspect, that’s interesting to know, appreciated.

Any thoughts on the other bits? e.g. source control for the scripts, backup recovery process, non-live instance for testing things?

I appreciate my topic covers more than one specific, sorry…

6

I would not mind having some form of revision control built into data explorer, but it is not currently on the roadmap.

If what you want is a crash course in postgres and to hack on stuff locally, I would recommend downloading a backup of your site and restoring locally. Then you get to use all the tooling that is out there for authoring, and you can always just paste in finished queries into data explorer.

Personally, I usually find it way more convenient just to author stuff direct in data explorer.

4 Likes
7

That would be quite cool actually, wasn’t something I was thinking of, as was really looking at ways to get our scripts under a source control repo instead. I suppose that functionality already exists for the wiki/posts etc, so would be quite similar.

Thanks for the suggestion regarding the download/tools etc, and I guess that would give us the ability to then commit those before pasting into Data Explorer.

Do you guys provide the non-live versions, a test instance etc? Not something I’d really given much thought to before, apart from when changes were made with css/html, and sometimes they’re not quite right, so again, people are effected.

I did suggest to the person I was talking to from GameDev.tv that they would probably need to contact you directly regarding this, wasn’t sure if you offer that or not, or, if you do, whether the test instance could be linked in such a way that the data is populated from a production instance backup?

1 Like
8

This absolutely something we do for our Enterprise plans. For business/standard plans best we can do is offer you a discount on a second standard instance, but the cost is high and stuff would not be magically replicated.

If costs are a concern I recommend you download backups as needed from the admin UI.

4 Likes
9

Thanks for this info Sam, appreciated. I can’t really comment on the costs side of things, as a) I don’t know how much you charge, and b) how much those that hold the wallet can afford to pay - but I will provide this information to them.

I did wonder whether the second instance approach would be an option.

Thanks again for all of the information above, really appreciated. :slight_smile:

Info passed on :slight_smile:

1 Like
10

For now, if you need to experiment with queries you can do an export/import cycle to create a copy.

3 Likes
11

Thanks Kane, that’s most likely the approach we will take. :slight_smile:

12

This topic was automatically closed after 2385 days. New replies are no longer allowed.