4 ms·

Hey there! Thanks for your comment. Well actually we do infer directly from the Postgres schema. Only for users not familiar with Postgres directly, we recomme

by tango12 10y ago

Hey there! Thanks for your comment. Well actually we do infer directly from the Postgres schema. Only for users not familiar with Postgres directly, we recommend going through the UI so that not only is the table created, but the Data API is also asked to track it and provide an API for it. There is other metadata that we do need the user to specify, via the UI or the API, and that's for stuff like permissions and relationships. Regarding, the unrestricted querying, we address it in one of the following ways: 1. Timing out queries running for longer than a specified interval. 2. Cursors and streaming data if the dataset requested is large. 3. Query templates: We allow the developer to create a RESTful API endpoint that has a limited pre-defined surface area (eg: GET data.mysite.com/v1/template/article?limit=10) which internally maps to a pre-defined query. Our docs are still lagging behind on these unfortunately! What do you guys do to restrict access? Any suggestions?

ruslan_talpa 10y ago
about metadata: in Postgrest - you tell it which schema to expose and that means that anything inside it needs "tracking" and exposing, want it exposed, put it in that schema. Relations between entities ... that can be queried from the database itself, no need to ask the user for it. timing out - it works up to a point, if you for example set a hard limit to 1s then to an attacker it just means he needs to do a few parallel requests and he still kills your db. what postgrest does for protection: a - no fancy joins, joins are only done by a thing we call "embedding" and those are joins between tables that FKs defined between them so they are fast, if you need fancy joins then the user has to define the view doing the join and expose it to the api. b - no functions applied to the return columns, like you cant generate something like "select md5(bigtextcolumn) ..." c - the only way to "kill" the database under postgrest is to use a filter on a column that has no index ( name like "hello"), out of the box postgrest does not have protection build in for that, but it's easy to put a proxy in front and whenever there is a url parameter called "name" you just reply with "you are not allowed to filter by this column"
- ruslan_talpa 10y ago
  and also permission, between views/stored procedures/roles/grants/rls postgresql has all the features needed to implement any ACL needed.
  - 0x777 10y ago
    > and also permission, between True. That would also mean creating a database user for every application user. The auth service needs to attach more metadata to the users and since postgres does not allow you to reference system catalogs, the two user systems have to be kept in sync. We were not quite certain how this idea of mapping users would pan out when we started building the auth service.
    - ruslan_talpa 10y ago
      You don't have to have a database user for each application user. Database roles are mapped to groups and application users are defined in tables as always and their ids can be used in RLS policies. It's hard to explain in a few words but the main point is that you don't need a database role to map to each application user
- 0x777 10y ago
  I'm one of the engineers who work on the data service > about metadata: in Postgrest .. We don't want to restrict the data service to a particular schema. You can add a table from any schema to be tracked, say information_schema or pg_catalog. In fact, that is how introspection works (you can look at the queries made by the console). This means that the schema cache in memory would be quite large because of the number of tables in information_schema/pg_catalog if we were to auto load all these. There is also the issue that you may not want to expose some tables. We can definitely have a console button which will let you import all the tables in public schema. Sure, you can infer relationships from the foreign key constraints. But, how would you come up with names that do not conflict with the existing columns? What if you add a new column with the same name as an inferred relationship? With the data service, you can also define relationships on/to views across schemas (1). Making metadata explicit goes with one of our core principles, 'no magic'. > what postgrest does for protection: a - no fancy joins, explicit joins are not allowed even with our data service. The joins that happen are the implicit ones because of the relationships. We do the same thing if we need any custom joins/aggregations. Define a view and expose it. > b - you can't do anything other than selecting columns and relationships with the api. > c - the permission layer can be used to prevent these to some extent (like not allowing an anonymous user to filter on a particular column). 1. https://hasura.io/_docs/platform/0.6/getting-started/5-data-aggregations-views.html https://hasura.io/_docs/platform/0.6/getting-started/5-data-...
  - ruslan_talpa 10y ago
    all the things i explained about metadata are implemented in postgrest and they work, and there is no loss of flexibility sinceyou can expose any table you want from any schema by explicitly creating a "mirror" view in the exposed schema. and relations are detected across tables and views and there is a way to deal with collisions. I am not saying that your way is bad (having a metadata file), just saying there is a way to automatically create it. - b,c that's good that you don't expose the entire SQL
    - 0x777 10y ago
      I guess we'll need to have 'auto generate relationships' tooling. Just curious, how do you infer the relationships between a table and a view?

by tango12 10y ago

ruslan_talpa 10y ago

about metadata: in Postgrest - you tell it which schema to expose and that means that anything inside it needs "tracking" and exposing, want it exposed, put it in that schema. Relations between entities ... that can be queried from the database itself, no need to ask the user for it. timing out - it works up to a point, if you for example set a hard limit to 1s then to an attacker it just means he needs to do a few parallel requests and he still kills your db. what postgrest does for protection: a - no fancy joins, joins are only done by a thing we call "embedding" and those are joins between tables that FKs defined between them so they are fast, if you need fancy joins then the user has to define the view doing the join and expose it to the api. b - no functions applied to the return columns, like you cant generate something like "select md5(bigtextcolumn) ..." c - the only way to "kill" the database under postgrest is to use a filter on a column that has no index ( name like "hello"), out of the box postgrest does not have protection build in for that, but it's easy to put a proxy in front and whenever there is a url parameter called "name" you just reply with "you are not allowed to filter by this column"

ruslan_talpa 10y ago
and also permission, between views/stored procedures/roles/grants/rls postgresql has all the features needed to implement any ACL needed.
- 0x777 10y ago
  > and also permission, between True. That would also mean creating a database user for every application user. The auth service needs to attach more metadata to the users and since postgres does not allow you to reference system catalogs, the two user systems have to be kept in sync. We were not quite certain how this idea of mapping users would pan out when we started building the auth service.
  - ruslan_talpa 10y ago
    You don't have to have a database user for each application user. Database roles are mapped to groups and application users are defined in tables as always and their ids can be used in RLS policies. It's hard to explain in a few words but the main point is that you don't need a database role to map to each application user
0x777 10y ago
I'm one of the engineers who work on the data service > about metadata: in Postgrest .. We don't want to restrict the data service to a particular schema. You can add a table from any schema to be tracked, say information_schema or pg_catalog. In fact, that is how introspection works (you can look at the queries made by the console). This means that the schema cache in memory would be quite large because of the number of tables in information_schema/pg_catalog if we were to auto load all these. There is also the issue that you may not want to expose some tables. We can definitely have a console button which will let you import all the tables in public schema. Sure, you can infer relationships from the foreign key constraints. But, how would you come up with names that do not conflict with the existing columns? What if you add a new column with the same name as an inferred relationship? With the data service, you can also define relationships on/to views across schemas (1). Making metadata explicit goes with one of our core principles, 'no magic'. > what postgrest does for protection: a - no fancy joins, explicit joins are not allowed even with our data service. The joins that happen are the implicit ones because of the relationships. We do the same thing if we need any custom joins/aggregations. Define a view and expose it. > b - you can't do anything other than selecting columns and relationships with the api. > c - the permission layer can be used to prevent these to some extent (like not allowing an anonymous user to filter on a particular column). 1. https://hasura.io/_docs/platform/0.6/getting-started/5-data-aggregations-views.html https://hasura.io/_docs/platform/0.6/getting-started/5-data-...
- ruslan_talpa 10y ago
  all the things i explained about metadata are implemented in postgrest and they work, and there is no loss of flexibility sinceyou can expose any table you want from any schema by explicitly creating a "mirror" view in the exposed schema. and relations are detected across tables and views and there is a way to deal with collisions. I am not saying that your way is bad (having a metadata file), just saying there is a way to automatically create it. - b,c that's good that you don't expose the entire SQL
  - 0x777 10y ago
    I guess we'll need to have 'auto generate relationships' tooling. Just curious, how do you infer the relationships between a table and a view?