Getting started
      Back to home
      1. Knowledge base
      2. Getting started

      How to use postgREST with pgEdge

      Article explains how postREST is used with pgEdge

      The following is also automatically done by pgEdge when you choose to create a cluster with postgREST. Highlighted commands can be used to test. 

      Configuring Permissions in PostgreSQL database


      create role web_anon nologin;


      grant usage on schema public to web_anon;

      grant select on public.pgbench_branches to web_anon;


      create role authenticator noinherit login password 'mysecretpassword';

      grant web_anon to authenticator;


      create role test_auth nologin;

      grant test_auth to authenticator;


      grant usage on schema public to test_auth;

      grant all on public.pgbench_branches to test_auth;

      Install and start posgREST

      The nodeCtl project makes it easy to install postgREST on your cluster. nodeCtl will check the system you’re on and download and install the correct version of postgREST and create the configuration and service files for you. 


      Install postgREST

      ./nc um install postgrest

      Modify postgres.conf in /opt/pgedge/postgrest/postgrest.conf to match your database name and update the anonymous web role to web_anon:

        db-uri = "postgres://localhost:5432/demo"

        db-schemas = "public"

        db-anon-role = "web_anon"

      Restart postgREST to pick up these new configuration settings:

      ./nc service restart postgrest

      Testing Select 

      Use a curl command to test the read only calls with the web_anon user. The URL followed by the table name is similar to running a “SELECT *” appending ? allows you to filter rows. In the example below ?tid=eq.5 is similar to running “WHERE tid=5”

      curl http://localhost:3000/pgbench_branches

          [{"bid":1,"bbalance":0,"filler":null}]


      curl "http://localhost:3000/pgbench_tellers?tid=eq.5"

      [{"tid":5,"bid":1,"tbalance":0,"filler":null}]

      Check out the postgREST documentation for more select query options. 


      Creating Trusted User

      In order to update a table in the postgres database through the postgREST API, you have to create a trusted user. This involves creating a jwt secret and using that to create a token. 


      On Linxz you can make a secret on the command line:

      export LC_CTYPE=C

      < /dev/urandom tr -dc A-Za-z0-9 | head -c32

      In my case this returned: TK9UrGc530bKHtwLDcmOS7Znv11F29SK


      Modify postgres.conf in /opt/pgedge/postgrest/postgrest.conf by appending:

      jwt-secret = "TK9UrGc530bKHtwLDcmOS7Znv11F29SK"

      Restart postgREST again to pick up these changes:

      ./nc service restart postgrest

      Use this site to create your token based off of the secret you created: https://jwt.io/#debugger-io

        Replace the payload data with “role”: ”test_auth”

        Replace your-256-bit-secret with the secret created above, 

      TK9UrGc530bKHtwLDcmOS7Znv11F29SK in my case


      Copy the token generated and export it as token: 

      export TOKEN="eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoidGVzdF9hdXRoIn0.WDFZRwba3v_0-Q9ixRxmNBNewOYFOaOPCXPLxcGTtmg"

      Testing Insert

      Now that you have this token saved, use it in the curl command Authorization. Executing a POST command like the one below will add a single row to pgbench_branches.

      curl http://localhost:3000/pgbench_branches -X POST \

           -H "Authorization: Bearer $TOKEN"   \

           -H "Content-Type: application/json" \

           -d '{"bid": 2,"bbalance":0,"filler":null}'

      Selecting again from that table will now show:

      curl http://localhost:3000/pgbench_branches

      [{"bid":1,"bbalance":0,"filler":null},

       {"bid":2,"bbalance":0,"filler":null}]

      This article was submitted by Cady Motyka