Skip to main content

Build a REST API with Aiven for PostgreSQL® Data API Limited availability

Expose a table in Aiven for PostgreSQL® as REST endpoints, secure them with Auth0, and call them with a bearer token.

note

Data API access is Limited availability.

Prerequisites​

Step 1: Create a table​

Connect to your database and create a table to expose through Data API:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10,2) NOT NULL
);

INSERT INTO products (name, price) VALUES
    ('Pen', 1.99),
    ('Notebook', 9.99),
    ('Backpack', 49.99);

Step 2: Create a role and grant privileges​

Create a PostgreSQL role for Data API to use, and grant it to postgrest_authenticator, the role Data API uses to connect to your database:

CREATE ROLE api_worker NOLOGIN;
GRANT USAGE ON SCHEMA public TO api_worker;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO api_worker;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO api_worker;
GRANT api_worker TO postgrest_authenticator;

For more information about the roles Data API creates automatically, see Authorize requests with PostgreSQL roles.

Step 3: Get your JWKS URL and audience from Auth0​

Data API verifies request tokens against your IdP's JWKS URL. In Auth0:

  1. Click Settings in the left sidebar and note your domain, for example dev-example.us.auth0.com.
  2. Your JWKS URL is that domain with /.well-known/jwks.json appended, for example https://dev-example.us.auth0.com/.well-known/jwks.json.
  3. Click Applications > APIs, then click your API.
  4. On the Settings tab, copy the Identifier value. This is your audience.

Step 4: Enable Data API​

  1. In the Aiven Console, open your Aiven for PostgreSQL service.
  2. Click Connect > Data API.
  3. In the Database list, select the database with the products table.
  4. Click Set up API.
  5. Enter the JWKS URL and audience from step 3.
  6. Accept the recommended cloud and plan, or choose a custom one.
  7. Click Confirm and deploy.
  8. Wait for the status to change to Running.

For more information about this step, including cloud and plan options, see Enable Data API.

Step 5: Add the role claim to Auth0 tokens​

Data API reads the PostgreSQL role to use from a role claim in the token. Add this claim to tokens issued for your Auth0 API with an Auth0 Action, as described in Add the role to your IdP tokens. Set the claim to api_worker, the role you created in step 2.

Step 6: Get an access token​

Request a token from Auth0 with the client credentials grant. Replace the placeholders with your Auth0 domain and the client ID and secret of the Machine to Machine application authorized for your API:

tip

Keep the client secret private; anyone with it can request tokens for your API. The access token expires after a period set by your Auth0 API configuration, so request a new one when it does.

curl --request POST \
  --url "https://AUTH0_DOMAIN/oauth/token" \
  --header "Content-Type: application/json" \
  --data '{"client_id": "CLIENT_ID", "client_secret": "CLIENT_SECRET", "audience": "AUDIENCE", "grant_type": "client_credentials"}'

Replace the following:

  • AUTH0_DOMAIN: your Auth0 domain from step 3.
  • CLIENT_ID and CLIENT_SECRET: the credentials of the Machine to Machine application authorized for your API.
  • AUDIENCE: the audience from step 3.

The response contains an access_token value. Use it as the bearer token in the following step.

Step 7: Call the endpoints​

Find your API URL on the Data API page. The following examples use REST_API_BASE_URL for that URL and TOKEN for the access token from step 6. Replace both placeholders with your own values.

Read the products:

curl "https://REST_API_BASE_URL/products?select=id,name,price" \
  -H "Authorization: Bearer TOKEN"

Insert a product:

curl -X POST "https://REST_API_BASE_URL/products" \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer TOKEN" \
  -d '{"name": "Marker", "price": 2.49}'

Filter the products with a price under 10:

curl "https://REST_API_BASE_URL/products?price=lt.10" \
  -H "Authorization: Bearer TOKEN"

Sort the products by price, descending:

curl "https://REST_API_BASE_URL/products?order=price.desc" \
  -H "Authorization: Bearer TOKEN"

For the full query syntax, including pagination and embedding related tables, see Call the endpoints.

Related pages