how to query different schema

[barbalex]

In my App I use two different schemas:

• mostly schema apflora
• sometimes schema beob

I need to find out, how to query a table on schema beob when postgrest was started with -s apflora.

There is this a comment in this issue, mentioning that I could set the schema as version in the http accept header. But that does not work for me (404 error).

I have also tried to start postgrest using an option -s apflora,beob as ist possible with postgraphql. This does not work either.

The only way I can see circumventing this is by starting TWO instances of postgrest, one for each schema. Looks like complicated overkill to me though.

[ruslantalpa]

@barbalex two running instances is the way to go, then you put nginx in front then you can namespace each running instance under a different location. I think you actually want one schema but you have your tables in 2 different schemas. Create a separate schema called api and create mirror views for each of the tables in those 2 schemas.

It's not overkill, the idea is to push you in the right direction (along with simplifying the code) so you put some thought into the schema you expose and be careful about what you expose there (should be only views and stored procedures) and not just connect postgrest blindly to the database and have everything exposed (then blame postgrest for being insecure technology :) ).

[barbalex]

hm. Views. Good idea, as the tables in beob are read only.

They are kept in a separate schema because they are not domain data and contain millions of datasets which should not be backed up together with the domain data.

But Views will do. Thanks!

[ruslantalpa]

You know you can insert/update stuff directly in the views as long as they are not joins, right? auto updatable views

[barbalex]

yes, I learned that just a few days ago :-)

[trungtin]

How does the current version implement the versioning, the old way of using version in accept header doesn't seem to work anymore. My app is used by multiple teams, each team with a set of specific table (some is common), sometimes with different column in similar table between teams, so I cannot use the method of set permission on the table as suggested. I intend to separate each team with new schema (there are large amounts of teams, so each process for each schema doesn't work either), how would I do it? I just start to learn Haskell so still get lost in this codebase, if this feature is out of scope of this project, can you guide me where to take a look to create my fork. Thanks much

[ruslantalpa]

@trungtin i think almost every time, having a different set of tables for different users of the same application is an incorrect schema design. The first thing to know is that you don't expose schemas where the tables live, you expose schemas where only views live (that extract data from your source tables). Also look into how RLS works so that you can filter the rows in those common tables for each team (alternatively you can do it in the views) Then have the tables with common columns for everybody. The stuff that is custom for each team can go into a json column so instead of heaving a new column for each team, you have different keys in a single column.

[trungtin]

@ruslantalpa Thanks much for your help, really appreciate your time In my case, I building a customizable CRM for multiple types of client (car dealers, real estate broker, ...), so I really need different table for each team. Anyway, I think I should put everything in the same schema for now, and re-architecture in the future. But what is that RLS you are referring to?

[ruslantalpa]

RLS is this https://www.postgresql.org/docs/9.5/static/ddl-rowsecurity.html row level security

[ruslantalpa]

PostgREST is relying on the power of the PostgreSQL for flexibility, almost always, when developing an api with postgrest, the answer will come from the DB docs.

[woicha]

@ruslantalpa Thank you very much for your explanations. Unfortunately I don't get why you recommend to expose only a schema containing no table. Could you explain that point in greater detail?

[tsingson]

@woicha sure, the schema can be anythings in postgres like table / view / materied view / functions and postgrest will work fine. but most time, we provide REST api with postgrest that no table in exposed schema, that meaning this schema provide read only REST api for .

[ruslantalpa]

@woicha tables represent your data model and that is almost never the thing you want to expose 100% as is to your api user ( you want to hide some things, rename others, combine a few). You want to retain flexibility in your api without making a hard link to your model. For these reasons, you always start with a separate schema composed of only views for your api, even if those views in the beginning are mirrors of the tables. You might find some more insight into the architecture in docs.subzero.cloud

[woicha]

@ruslantalpa Again thank you very much for your helpful advice. I tried to set up our database schemas to separate the relational model and from the api using views. But I ran into a big problem because i want to use row security policies to control the access to our tables and it seems that PostgreSQL uses the owner of the views as the "current_role". I imagine I'm not the first one having that problem. Have you got any idea how to deal with that problem or is there already a solution within PostgREST? I can't find a suitable solution in the PostgREST or PostgreSQL documentation or searching the net. At the moment it seems I have to choose between RLS and using views at all. I really would appreciate help on that matter because the research already cost a lot of time and there seems to be no solution.

[steve-chavez]

@woicha Have a look at https://github.com/PostgREST/postgrest-docs/issues/81#issuecomment-385497947. There's also a note with a workaround in http://postgrest.org/en/v5.2/api.html#horizontal-filtering-rows, at the bottom.