4 ms·

A year ago the Russian-based Lingualeo (20 mln users) presented an article where they described their new architecture which has a similar concept. They have a

by kgeist 5y ago

A year ago the Russian-based Lingualeo (20 mln users) presented an article where they described their new architecture which has a similar concept. They have a simple proxy server in Go and all the logic happens in Postgres, i.e. frontend directly manipulates DB. Their claimed advantages were superior performance and less boilerplate/faster iteration times. In a matter of hours, commenters were able to dump sensitive data (passwords and all) by constructing specially crafted queries. Another problem they had was that many core devs left because they perceived the new architecture a step backwards. Many also noted that they don't have adequate tools for version control anymore. I don't know how PostgREST compares, maybe it doesn't have such issues.

michelpp 5y ago
I heard there was this one website that released software with a bug in it once that leaked data.
- kgeist 5y ago
  Of course, it was a bug in their code, which they promptly fixed. But it's the only case of a company with a large userbase using this kind of architecture that I know of, and their experience was far from smooth, so I thought I'd share.
  - michelpp 5y ago
    My point was your anecdote has nothing to do with PostgREST. It's a foggy hand wave that could be laid over just about anything with an HTTP socket on one end and a database on the other.
    - fluidcruft 5y ago
      There's something to be said about layers of security providing redundancy to catch errors--that's part of what goes into highly reliable operations used in aerospace and health care. If you get the postgres wrong and that's all you're relying on, then that's all you've got. If you've got the same postgres wrapped in a more rigid validation then arguably there are more bugs that need to align for a mishap. Of course the flip side is that's even more complex and more difficult to implement. The reality is people probably tend to skimp on the postgres layer's security if they're burying it deep under other things. Whether you're focusing on the postgres layer or some higher later (python or whatever), that's still just one layer of protection.
      - sigstoat 5y ago
        > If you've got the same postgres wrapped in a more rigid validation then arguably there are more bugs that need to align for a mishap. > The reality is people probably tend to skimp on the postgres layer's security if they're burying it deep under other things. so the reality is, they don't have layers. they'll have one security layer. best to put it in postgres, where other folks who aren't in a hurry to deploy the feature your boss wants, have tested the security model.
    - kgeist 5y ago
      I agree it's just an anecdote and I have nothing against PostgREST. I think it's an interesting approach which has its uses. Currently at my job all I've been doing lately has been replacing slow ORM-based access with pure SQL queries for performance/readability and I can see the advantages of ditching the layer between the frontend and the DB entirely, at least for queries. But there are several things I'd like to see an analysis/comparison of, between the traditional approach and PostgREST's approach: 1) what is the cost of a mistake (what if I misconfigure row-based policy vs. misconfigure in the traditional backend) 2) the size of the attack surface (if we expose DB directly, as opposed to having an additional layer) 3) how many developers are proficient in Postgres enough to avoid #1 and #2 as opposed to traditional code-based security policies
      - michelpp 5y ago
        > 1) what is the cost of a mistake (what if I misconfigure row-based policy vs. misconfigure in the traditional backend) Good question. Ok let's do a thought experiment between PostgREST and Django. Every single Django production system I've seen logs into Postgres as a superuser (so it can do migrations). PostgREST logs into the database as a user with NO privileges (because migration is a problem for a migration tool, not a web framework!) and then switches roles based on the (signed) JWT token. The PostgREST user is NEVER a superuser. So you flub a Django permission and users can now run queries as a superuser and potentially leak everythinv. If you flub a PostgREST configuration, you leak data maybe in one or two tables. > 2) the size of the attack surface (if we expose DB directly, as opposed to having an additional layer) This is going to differ depending on who you ask. People talk about attack "surfaces" but I personally think of them as "attack paths". Does more code mean more paths? I think yes. Others think no. > 3) how many developers are proficient in Postgres enough to avoid #1 and #2 as opposed to traditional code-based security policies Postgres' security mechanism comes down to "who can see this" it's the same question those developers would be asking in Django. If they're not proficient enough to understand now to GRANT or REVOKE are they going to be proficient enough to understand the implications of their application logging into the database as a superuser?
        minusf 5y ago
Twisol 5y ago
> In a matter of hours, commenters were able to dump sensitive data (passwords and all) by constructing specially crafted queries. To be fair, SQL injection attacks have been a common problem for a long time, and most websites did not expose their database directly like this. It certainly feels like PostgREST and systems like it are dancing close to the fire here, since there's a lot less in between you and an attacker. But as far as I can tell, for me, right now, it's just that: a feeling. I'd love to have a robust analysis of the access controls Postgres provides, and more importantly, how many mistakes (relative and absolute) need to be made for a breach. I'd like to know whether the security story here is fundamentally brittle, or whether it's practically viable and just needs better support for doing the "right" thing. Personally, I like the architectural philosophy a lot. We saw a project on HN yesterday [0] that takes a similar approach. I'm oversimplifying, but I like that instead of trying to figure out what series of API calls will put the system in the state you want, you can simply put it in the state you want. It's easier for me to look at a schema/type and understand what it's supposed to mean than to look at a piece of code and figure out what it's supposed to do. [0] https://news.ycombinator.com/item?id=29375911 https://news.ycombinator.com/item?id=29375911
- michelpp 5y ago
  > It certainly feels like PostgREST and systems like it are dancing close to the fire here, since there's a lot less in between you and an attacker. There are definitely fewer buffered copies of your responses, yes. This is one advantage few people know about PostgREST, once the database produces even a single row of data, it starts to stream JSON to the frontend. Users don't have to wait for the response to fully buffer in memory in some Python or Go process before being relayed on. As for security, Postgres supports Row Level Security. You can literally protect every single row of your data with a solid security policy using a security mechanism older and tested longer than the average age of your typical HN reader. This security mechanism doesn't just apply to your web frontend either, but to all SQL clients regardless of the frontend language. No other framework has such a deep integration with Postres' native security mechanism.
  - Twisol 5y ago
yeputons 5y ago
Here are some links in Russian for the curous: * The post: https://habr.com/ru/company/lingualeo/blog/515530/ https://habr.com/ru/company/lingualeo/blog/515530/ * The corresponding talk: https://pgconf.ru/en/2020/264859 https://pgconf.ru/en/2020/264859 * Twitter thread with data breach: https://twitter.com/SanSYS/status/1299657221085835264 https://twitter.com/SanSYS/status/1299657221085835264 If the Twitter thread is accurate, their API received a list of DB field (and their JSON names to use) from the client. It was hard to guess names of fields, so someone decided to fuzz it a little bit and the API endpoint returned all existing fields in the database.
LAC-Tech 5y ago
IIRC the way postgREST works is that not everything in the database is accessible, just everything in your public schema. So you put most of your logic in views. Which has gone out of fashion or something, I think.

by kgeist 5y ago

michelpp 5y ago

I heard there was this one website that released software with a bug in it once that leaked data.

kgeist 5y ago
Of course, it was a bug in their code, which they promptly fixed. But it's the only case of a company with a large userbase using this kind of architecture that I know of, and their experience was far from smooth, so I thought I'd share.
- michelpp 5y ago
  My point was your anecdote has nothing to do with PostgREST. It's a foggy hand wave that could be laid over just about anything with an HTTP socket on one end and a database on the other.
  - fluidcruft 5y ago
    There's something to be said about layers of security providing redundancy to catch errors--that's part of what goes into highly reliable operations used in aerospace and health care. If you get the postgres wrong and that's all you're relying on, then that's all you've got. If you've got the same postgres wrapped in a more rigid validation then arguably there are more bugs that need to align for a mishap. Of course the flip side is that's even more complex and more difficult to implement. The reality is people probably tend to skimp on the postgres layer's security if they're burying it deep under other things. Whether you're focusing on the postgres layer or some higher later (python or whatever), that's still just one layer of protection.
    - sigstoat 5y ago
      > If you've got the same postgres wrapped in a more rigid validation then arguably there are more bugs that need to align for a mishap. > The reality is people probably tend to skimp on the postgres layer's security if they're burying it deep under other things. so the reality is, they don't have layers. they'll have one security layer. best to put it in postgres, where other folks who aren't in a hurry to deploy the feature your boss wants, have tested the security model.
  - kgeist 5y ago
    I agree it's just an anecdote and I have nothing against PostgREST. I think it's an interesting approach which has its uses. Currently at my job all I've been doing lately has been replacing slow ORM-based access with pure SQL queries for performance/readability and I can see the advantages of ditching the layer between the frontend and the DB entirely, at least for queries. But there are several things I'd like to see an analysis/comparison of, between the traditional approach and PostgREST's approach: 1) what is the cost of a mistake (what if I misconfigure row-based policy vs. misconfigure in the traditional backend) 2) the size of the attack surface (if we expose DB directly, as opposed to having an additional layer) 3) how many developers are proficient in Postgres enough to avoid #1 and #2 as opposed to traditional code-based security policies
    - michelpp 5y ago
      > 1) what is the cost of a mistake (what if I misconfigure row-based policy vs. misconfigure in the traditional backend) Good question. Ok let's do a thought experiment between PostgREST and Django. Every single Django production system I've seen logs into Postgres as a superuser (so it can do migrations). PostgREST logs into the database as a user with NO privileges (because migration is a problem for a migration tool, not a web framework!) and then switches roles based on the (signed) JWT token. The PostgREST user is NEVER a superuser. So you flub a Django permission and users can now run queries as a superuser and potentially leak everythinv. If you flub a PostgREST configuration, you leak data maybe in one or two tables. > 2) the size of the attack surface (if we expose DB directly, as opposed to having an additional layer) This is going to differ depending on who you ask. People talk about attack "surfaces" but I personally think of them as "attack paths". Does more code mean more paths? I think yes. Others think no. > 3) how many developers are proficient in Postgres enough to avoid #1 and #2 as opposed to traditional code-based security policies Postgres' security mechanism comes down to "who can see this" it's the same question those developers would be asking in Django. If they're not proficient enough to understand now to GRANT or REVOKE are they going to be proficient enough to understand the implications of their application logging into the database as a superuser?
      - minusf 5y ago

Twisol 5y ago

> In a matter of hours, commenters were able to dump sensitive data (passwords and all) by constructing specially crafted queries. To be fair, SQL injection attacks have been a common problem for a long time, and most websites did not expose their database directly like this. It certainly feels like PostgREST and systems like it are dancing close to the fire here, since there's a lot less in between you and an attacker. But as far as I can tell, for me, right now, it's just that: a feeling. I'd love to have a robust analysis of the access controls Postgres provides, and more importantly, how many mistakes (relative and absolute) need to be made for a breach. I'd like to know whether the security story here is fundamentally brittle, or whether it's practically viable and just needs better support for doing the "right" thing. Personally, I like the architectural philosophy a lot. We saw a project on HN yesterday [0] that takes a similar approach. I'm oversimplifying, but I like that instead of trying to figure out what series of API calls will put the system in the state you want, you can simply put it in the state you want. It's easier for me to look at a schema/type and understand what it's supposed to mean than to look at a piece of code and figure out what it's supposed to do. [0] https://news.ycombinator.com/item?id=29375911 https://news.ycombinator.com/item?id=29375911

michelpp 5y ago
> It certainly feels like PostgREST and systems like it are dancing close to the fire here, since there's a lot less in between you and an attacker. There are definitely fewer buffered copies of your responses, yes. This is one advantage few people know about PostgREST, once the database produces even a single row of data, it starts to stream JSON to the frontend. Users don't have to wait for the response to fully buffer in memory in some Python or Go process before being relayed on. As for security, Postgres supports Row Level Security. You can literally protect every single row of your data with a solid security policy using a security mechanism older and tested longer than the average age of your typical HN reader. This security mechanism doesn't just apply to your web frontend either, but to all SQL clients regardless of the frontend language. No other framework has such a deep integration with Postres' native security mechanism.
- Twisol 5y ago

yeputons 5y ago

Here are some links in Russian for the curous: * The post: https://habr.com/ru/company/lingualeo/blog/515530/ https://habr.com/ru/company/lingualeo/blog/515530/ * The corresponding talk: https://pgconf.ru/en/2020/264859 https://pgconf.ru/en/2020/264859 * Twitter thread with data breach: https://twitter.com/SanSYS/status/1299657221085835264 https://twitter.com/SanSYS/status/1299657221085835264 If the Twitter thread is accurate, their API received a list of DB field (and their JSON names to use) from the client. It was hard to guess names of fields, so someone decided to fuzz it a little bit and the API endpoint returned all existing fields in the database.

LAC-Tech 5y ago

IIRC the way postgREST works is that not everything in the database is accessible, just everything in your public schema. So you put most of your logic in views. Which has gone out of fashion or something, I think.