3 ms·

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

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