I built GetMoarFediverse so that I could bring posts tagged with stuff I care about.

That was good for me, I could just hardcode the list of hashtags I follow and I’d be happy!

Now, the problem is when more people joined my server (just friends and family, but still). I want them to be able to follow hashtags and get meaningful content, even in this tiny instance.

weedyverse.de had a great idea: I can query the database directly!

It was quite straightforward to implement… so now, if you use a config.json like this one:

{
    "FakeRelayUrl": "https://fakerelay.gervas.io",
    "FakeRelayApiKey": "1TxL6m1Esx6tnv4EPxscvAmdQN7qSn0nKeyoM7LD8b9m+GNfrKaHiWgiT3QcNMUA+dWLyWD8qyl1MuKJ+4uHA==",
    "MastodonPostgresConnectionString": "Host=myserver;Username=mastodon_read;Password=password;Database=mastodon_production",
    "Instances": [ "hachyderm.io", "mastodon.social" ]
}

Then GetMoarFediverse will index content your users are interested in. I recommend you create a read-only user for this, and if you want to restrict its privileges, it needs access to the tag_follows and tags tables.

You can create a mastodon_readread only user with access to those tables with these commands:

CREATE USER mastodon_read WITH PASSWORD 'password';
GRANT CONNECT ON DATABASE mastodon_production TO mastodon_read;
GRANT USAGE ON SCHEMA public TO mastodon_read;
GRANT SELECT ON tag_follows TO mastodon_read;
GRANT SELECT ON tags TO mastodon_read;

Gervasio Marchand

@g3rv4@mastodonte.tech g3rv4


Published