Currently no, since this was just meant to be a toy project. But I agree with you that I have to deal with moderation if I intend to keep this up. The only countermeasure is to peek into the URL without having to be redirected cause each shortened URL has a stats page. e.g https://emojied.net/stats/%F0%9F%8D%8A%F0%9F%8C%90
I don’t understand what metrics you use for “more scalable” and “more maintainable”. If your application’s needs is sufficiently fulfilled by RLS, you don’t have to reinvent the wheel in the application level. Less code there to maintain is good.
Plus, I don’t see how “just by reading application code you can tell the expected behavior” doesn’t apply to RLS. Policies are written in a consistent format. USING for visibility, WITH CHECK for altering. I only have to keep an eye for these, and I’ll already get a good summary on what it does, no?
You can create a role whose sole job is to switch to the roles needed. Doesn’t require you to escalate to superuser-level privileges that way. But still, if SQL injections aren’t properly considered then it’s possible for a user to gain more privileges than planned. Although SQL injections are usually mitigated by the DB libraries these days.
Also, it’s more convenient to use SET LOCAL ROLE <ROLE_NAME>, since that only keeps the role for the transaction. Manually resetting it is error prone (IME), and forgetting will have the supposedly “temporary” role bleed to the next transaction.