5 ms·

I understand this stuff is "cool" but this is only one step removed from db->query(input[sql]). I'm not talking about SQL injection either, I'm talking about wh

by meritt 7y ago

I understand this stuff is "cool" but this is only one step removed from db->query(input[sql]). I'm not talking about SQL injection either, I'm talking about wholesale data exfiltration of your entire database because you've auto-generated some slick GraphQL API and you don't take the time to think through what should, and should not, be exposed to end users or bother to implement any form of access control.

MikeAmelung 7y ago
Not only that, it only lets you query your "root" type by primary key? So it's less functional than the normal interface to the database. I'm having a really hard time understanding the point of this.
jdc 7y ago
Seems like a good usecase for row-level permissions
atleta 7y ago
It's yet another attempt to step back to two-tier. (It's the usual thesis-antithesis cycle. First, we had that, then we figured out it doesn't work so we turned towards three-tier then we saw that was pretty tedious so we started to look at two-tier again. But this time in a disguise.)
spullara 7y ago
Agreed. Incredibly bad idea and there are a few others like it that are very popular. Yikes.
tensor 7y ago
Security is done via row level access control, a system that is vastly better then the ad-hoc systems people reinvent outside the database for every application. You also absolutely take the time to think through what should and should not be exposed to end users. Either you creates a namespace for your API with views that expose only the necessary data, or with something like Postgraphile you can specify which functionality, columns, and tables are hidden. Personally I prefer the view approach as it decouples your API from the database implementation.
- sbilstein 7y ago
  RLS is one of the shittiest things about Postgraphile and why I decided against for one project and am doing my best to root it out of a current project. RLS is difficult to unit test, to configure with flags, not portable to secondary stores, and requires knowledge of arcane database features. When you put some private data on a cache somewhere and query it, now you need to write your access layer twice since the other one is so tightly coupled to the database.
  - tensor 7y ago
    We'll have to agree to disagree. Writing a manual security layer that doesn't understand your data model is one of the worst things you can do. Multiple databases? Now you're also manually doing integrity and probably manually doing joins. That's a ton of extra things to go wrong. As an example, consider a query that is fairly complex. How exactly are you going to make resuable security tests for it? How do you know that an error in the query won't expose unexpected data, or that a modification to the query that introduces and error exposes unexpected data? Your security now has to be customized to every query like this. The alternative case is that you only use super simple queries so that you can write resuable security checks. But now I'm going to ask why you are using a relational database in the first place? Because almost certainly you're doing manual joins in this scenario and incurring huge performance hits. If you are using a relational database to its full extent, there is nothing as secure as built in row level security. You wouldn't dream of doing integrity checks outside the database, why do security?
    - snuxoll 7y ago
      > If you are using a relational database to its full extent, there is nothing as secure as built in row level security. You wouldn't dream of doing integrity checks outside the database, why do security? This isn’t exactly PostgreSQL’s fault, but mapping application users to database roles is not as easy in pretty much any framework I’ve worked compared to implementing basic post-retrieval filters. I absolutely think bringing security into the database is good, but it’s also not straightforward to integrate in even assuming you know how to define the necessary database elements.

by meritt 7y ago

MikeAmelung 7y ago

Not only that, it only lets you query your "root" type by primary key? So it's less functional than the normal interface to the database. I'm having a really hard time understanding the point of this.

jdc 7y ago

Seems like a good usecase for row-level permissions

atleta 7y ago

It's yet another attempt to step back to two-tier. (It's the usual thesis-antithesis cycle. First, we had that, then we figured out it doesn't work so we turned towards three-tier then we saw that was pretty tedious so we started to look at two-tier again. But this time in a disguise.)

spullara 7y ago

Agreed. Incredibly bad idea and there are a few others like it that are very popular. Yikes.

tensor 7y ago

Security is done via row level access control, a system that is vastly better then the ad-hoc systems people reinvent outside the database for every application. You also absolutely take the time to think through what should and should not be exposed to end users. Either you creates a namespace for your API with views that expose only the necessary data, or with something like Postgraphile you can specify which functionality, columns, and tables are hidden. Personally I prefer the view approach as it decouples your API from the database implementation.

sbilstein 7y ago
RLS is one of the shittiest things about Postgraphile and why I decided against for one project and am doing my best to root it out of a current project. RLS is difficult to unit test, to configure with flags, not portable to secondary stores, and requires knowledge of arcane database features. When you put some private data on a cache somewhere and query it, now you need to write your access layer twice since the other one is so tightly coupled to the database.
- tensor 7y ago
  We'll have to agree to disagree. Writing a manual security layer that doesn't understand your data model is one of the worst things you can do. Multiple databases? Now you're also manually doing integrity and probably manually doing joins. That's a ton of extra things to go wrong. As an example, consider a query that is fairly complex. How exactly are you going to make resuable security tests for it? How do you know that an error in the query won't expose unexpected data, or that a modification to the query that introduces and error exposes unexpected data? Your security now has to be customized to every query like this. The alternative case is that you only use super simple queries so that you can write resuable security checks. But now I'm going to ask why you are using a relational database in the first place? Because almost certainly you're doing manual joins in this scenario and incurring huge performance hits. If you are using a relational database to its full extent, there is nothing as secure as built in row level security. You wouldn't dream of doing integrity checks outside the database, why do security?
  - snuxoll 7y ago
    > If you are using a relational database to its full extent, there is nothing as secure as built in row level security. You wouldn't dream of doing integrity checks outside the database, why do security? This isn’t exactly PostgreSQL’s fault, but mapping application users to database roles is not as easy in pretty much any framework I’ve worked compared to implementing basic post-retrieval filters. I absolutely think bringing security into the database is good, but it’s also not straightforward to integrate in even assuming you know how to define the necessary database elements.