We've been swamped with a flood of spam for the last few months. Some losers are creating hundreds or thousands of accounts on undermoderated servers and pestering the whole fediverse with junk. Mastodon itself provides no mechanism for admins to reject statuses that contain certain strings, even though many people have begged for this over the years. And while I could learn enough Ruby on Rails to implement such a feature myself, I'm not confident that it would be accepted into the main project and I don't want to maintain a fork.
What I do have is root-level access to my instance's database, enough SQL knowledge to be dangerous, and a willingness to break things and see what happens. I put all that in a blender and some working code came out the other side.
What you're looking at is a PostgreSQL check constraint that applies a function I wrote to every status insert into the database, and rejects ones that contain text I don't ever want to store on my instance. If I try to post a toot that contains such text, I get a little "500" popup in the corner of my screen and it doesn't get sent. I'm not sure what happens if another server tries to send us a toot with that text. I'm guessing the API returns a 500, too, and it fills up their outbound queue with retries. I honestly couldn't care less. Don't send us spam, yo.
Before you apply this on your own server, read the giant warning at the top. If you don't, and you mess around with this without following the advice, you're going to be a very sad camper next time you try to restore your database. Don't panic, though. This uses normal, built-in PostgreSQL features in the normal, not-"clever" way they're meant to be used. The risk isn't to this database check specifically, but to all PostgreSQL check constraints that call user-defined functions. Like so many other database features, it's something to learn, understand, and respect.
AirTrail is a self-hosted, open-source personal flight tracking system. The objective is to provide a simple and easy-to-use interface to track your flights, list them all and provide a way to analyze them. View all your flights on an interactive world map. Keep track of all your flights in one place. Get insights into your flight history with statistics. Allow multiple users and secure your data with user authentication. Use the application on any device with a responsive design. Import flights from various sources.
Written in Typescript, uses Postgres as its back-end.
Manual installation is an option: https://johanohly.github.io/AirTrail/docs/install/manual
A complete implementation of Tetris in a SQL query. Written for Postgres. Has a Python front-end that uses psycopg2.
Clew is a web search engine trying to be different from the rest.
- We focus on writing by independent creators. There's no point in indexing Wikipedia; it has its own perfectly serviceable search bar.
- We are not ad-supported; currently, Clew is a labor of love by Benjamin Hollon and donation-supported.
- Because our funding source is not dependent on the interests of for-profit businesses and corporations, our search rankings are unbiased and apply the same standards equally to all indexed domains.
- In addition, we incorporate variables that would not be in the interests of commercial search engines, such as whether we detect invasive ads or tracking on a site and how much bandwidth pages require to download, perfect for individuals who want to see whether a website will respect their privacy and data before opening it.
Git repo: https://codeberg.org/Clew/Clew
Public api for aircraft, airlines, and flight routes. No API key, everything is rate limited over a 60 second period.
I don't know how useful or reliable it is yet.
A single-file database administration utility written in PHP. Supports MySQL, PostgreSQL, SQLite, MS SQL, and Oracle database servers. Formerly phpMyAdmin. Seems to let you do everything you could do from a database shell. Session-aware. Has plugins for a number of popular web apps to help you fix their databases should you need to. Easily themeable, just drop a new CSS file into the directory Adminer is installed in.
CKAN is open source data management portal software for extremely large data sets, on the scale of entire governments. Used to publish, curate, share, search, and find datasets of just about any kind. Does visualization and metrics. Source code on Github (https://github.com/ckan/ckan). Written in Python, uses Postgres as its back end. Can interface with Apache Solr for search and indexing.
A FOSS platform for analysing and visualizing large sets of documents. Designed with investigative journalists in mind but has other uses. Has a plugin system. Can be run as a server or locally (https://github.com/overview/overview-local). Designed to run as a docker container (bluh) but running it as-is shouldn't be difficult. Written in scala (wtf?) Seems to use postgres as its back-end.
Clair is a FOSS utility for conducting static security analysis of Linux containers, Docker containers in particular. Clair continually updates its internal index of known vulnerabilities so it can keep constant watch over what it monitors. Has a modular architecture to make it easier to extend the project without having to fork() it. Also designed to fit into a CI/CD pipeline to monitor in-house containers as they're built. Plugs into Kubernetes. Requires Postgres. Written in Go. sysadmin infosec scanner scanning