What kinds of attacks are possible with credentials to a read-only Postgres user?

Question

Assuming I implement the literal SQL-injection on an HTTP endpoint with a read-only user that limits execution time to e.g. some amount of seconds. What's the worst that could happen? I know simple attacks could be just flooding simple queries. But is there another potential risk?

I already noticed some issues to mitigate:

• The public role needs to be completely revoked of all permission and never granted anything.
• Some functions might be more time and cpu intensive. I do wonder if there's a way to limit the exposure to these other than a simple time out.
• In general exposure to too many tables and columns.

Because this is a web service I control - I can constrain the contents of the SQL query, so that's something I'd love to hear if there are functions, keywords or characters that I need to strip to make sure people are only making reasonable SELECT queries.

I realize this is somewhat of a vague request, but then again, security is a bit vague.

Answer 1

A few things:

Bugs

There are two sources for bugs here: Your role definition, and the database software itself. If you configure the role wrong, or the database software has a bug which allows people to do things they shouldn't, then... well, it depends on the exact bug, of course, but really anything can happen.

"I can constrain the contents of the SQL query"

Given SQL's complexity, that's a lot harder than I think you realize. Sure, it's possible, but there's really only one way to do it effectively: Make a query builder client, and build the actual query serverside. You might still introduce a SQLi vuln by accident, but it'll be easier to constrain than arbitrary SQL.

A better way?

I'm basically stealing this from Anders, but in my defense, I did also have the same idea independently -- funnily enough, even with the same inspiration.

In short, use a throwaway database. Postgres natively supports read-only replicas, where the data only ever flows one way, but if you want to be paranoid you could always set up a job to manually move files over. Postgres supports that, too.

Then, once you have your read-only replica, you can expose it to the world and basically not care about it. It's read-only at the database level, so there's no chance of any privilege bugs; your attack surface is now smaller. It's much easier to reset than your real production database -- just nuke and reinstall. You can filter out data you don't want them to see and entirely exclude it from the database they have access to. As a nice plus, because this is so well-supported by Postgres, it's really easy to scale up to meet demand. Likewise, if one of the servers gets DoSed, you can just tear it down and spin up a new one in its place.