PostgREST API Setup

[mchatrvd]

Everyone,

I am new to API setup but well experienced with database. I am trying got setup API service using PostgREST on our PostgreSQL Database Server. This service will help user access the PostgREST service to make API calls to the PostgreSQL Database.

I understand that we need to setup anonymous database account for users not having any database account setup or using the service as guest. Also, that we will need a database account for PostgREST service to start and keep the service running.

My question is that - If I want the user to use their database accounts to access the PostgreSQL database, then how shall i go about setting this up in PostgREST and/or PostgreSQL. This is to make user accessing the database can only access what they are setup for in the Database Roles/Groups. Any help is greatly appreciated. Thanks in advance.

[taimoorzaeem]

@mchatrvd Thanks for reaching out! Detailed documentation on PostgREST API and setup is available on our website here.

[mchatrvd]

Hi

Thanks for sharing the documentation link. I have gone though it and will like to make sure that my understanding of the authenticated setup is correct.

So, as an example if we have 50 users with their individual database account (roles in PostgreSQL world) who will like to make API call to the PostgreSQL Database, then the following assumptions hold true -

1. These 50 users (database roles) will need it be assigned to PostgREST service Authentication Role as well via Grant database command so that Authenticator can assume these roles
2. The individual roles will need to generate their own JWT Token by using the JWT link (https://jwt.io/) which is based on Database Role (User ID) and the Database Password (secret phrase)
3. The users will pass this token under the following command line parameter in the API call - -H "Authorization: Bearer $TOKEN"
4. As the API call is made, the JWT Token will be decrypted to provide the Database Role and Password. Authenticator will then switch to the User Role and execute the query.

I just want to make sure that this is how the PostgREST service will work. Thanks.

[laurenceisla]

Hi, I'm assuming you're using a single PostgREST instance for one database and manage your users with roles using JWT as specified in the docs. Then:

1. Yes, by doing a GRANT role TO authenticator; for each role.
2. Yes, but not exactly using https://jwt.io . You'll need a more robust JWT generator like Auth0. And the JWT secret is not the DB password, it should be a 32 character value specified in the jwt-secret configuration parameter.
3. Yes, that is correct.
4. Yes, but again, it does not use the DB Password, only the JWT secret. What PostgREST does is connect to the database using the user and password specified in db-uri, which we refer as "authenticator". This authenticator (suggested to have access only to login to the database and role impersonation in case it is compromised) is the one that switches to any of the roles, which may not even have permission to connect directly to the database (created with NO LOGIN). So these roles may not expose a "password" and you should handle this Authentication layer (user/password) in the external JWT generator (like Auth0).
   NOTE: Alternatively to Auth0 you could also handle the role/password from PostgreSQL inside the database like in this example, but it's experimental and cannot guarantee security.

[mchatrvd]

Thanks for your response. Another question - Does the PostgREST service need to be installed along with Tomcat or Docker? Is Docker or Tomcat needed to host this service so that it keeps running on the host server? Can I just install this service like another service on the server and have it as part of the server startup script so that it starts when server boots and shuts down along with the server shutdown, and that way keeps running on the server? Thanks

[laurenceisla]

Yes, it can run as a service in your server as-is. Here's an example: https://postgrest.org/en/stable/integrations/systemd.html?highlight=service#systemd. But if you need to expose your API, e.g. through HTTPS, you can do so by using Nginx as mentioned in the docs.