POSTgREST API Request is success however no data is returned

[soorajiyer]

Server : nginx/1.18.0 (Ubuntu)

Description of issue

I am trying to fetch data from postgresql database using REST API called say https://api.xxxxx.dev/iot-db using a python code. A JWT token has to be generated from microsoft and pass it as bearer token. Everything is alright however the api returns [ ] . When i checked the response status code it is 200, which means that the request is success, however the api is not returning any data from the postgresql database. The entire code is below.

import requests
from msal import ConfidentialClientApplication
from typing import List, Dict, Any, Union

TENANT_ID = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
CLIENT_ID = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
CLIENT_SECRET = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxx'  
SCOPE = ['api://xxxxxxxxxxxxxxxxxxxxxxx/.default']

data_endpoint_config = {
    'projects': {
        'fields': 'projects?select=id,name,route,imeis,subline,ws10Icon,routeSecondLayer',
        'order': '&order=name.asc',
    },
    'latestDeviceData': {
        'fields': 'get_latest_device_data',
        'order': '&order=timestamp.desc',
    },
    'data': {
        'fields': 'data?select=id,version,imei,timestamp,network,data,project,location,latest',
        'order': '&order=timestamp.desc',
    },
}

def get_access_token(tenant_id: str, client_id: str, client_secret: str, scope: List[str]) -> Union[str, None]:
    authority = f"https://login.microsoftonline.com/{tenant_id}"
    app = ConfidentialClientApplication(
        client_id,
        authority=authority,
        client_credential=client_secret,
    )
    result = app.acquire_token_for_client(scopes=scope)
    if 'access_token' in result:
        return result['access_token']
    else:
        print('Failed to retrieve access token:', result.get('error_description', 'Unknown error'))
        return None

def call_api_data(access_token: str, query: str) -> Union[Dict[str, Any], List[Dict[str, Any]]]:
    headers = {
        'Authorization': f'Bearer {access_token}',
        'Content-Type': 'application/json',
    }
    url = f"https://api.xxxxx.dev/iot-db/{query}"

    print(f"Requesting URL: {url}")  # Debug: Print URL
    print(f"Request Headers: {headers}")  # Debug: Print headers

    try:
        response = requests.get(url, headers=headers)
        response.raise_for_status()  # Raise an error for bad status codes

        # Debug: Print response status code and full response
        print(f"Response Status Code: {response.status_code}")
        print("Response Content:", response.text)
        
        return response.json()
    except requests.exceptions.HTTPError as http_err:
        print(f"HTTP error occurred: {http_err}")
        print("Response content:", response.text)  # Print full response content
    except Exception as err:
        print(f"Other error occurred: {err}")

    return []

def get_device_data(query_name: str) -> Union[List[Dict[str, Any]], Dict[str, Any]]:
    try:
        access_token = get_access_token(TENANT_ID, CLIENT_ID, CLIENT_SECRET, SCOPE)
        if access_token:
            query_config = data_endpoint_config.get(query_name, {})
            query_fields = query_config.get('fields', '')
            query_order = query_config.get('order', '')
            query = f"{query_fields}{query_order}"  # Combine fields and order
            if query:
                return call_api_data(access_token, query)
            else:
                print(f'No query found for query name: {query_name}')
        else:
            print('Failed to retrieve access token.')
    except Exception as e:
        print(f'Error retrieving data: {str(e)}')

    return []


if __name__ == '__main__':
    
    query_name = 'data'

  
    device_data = get_device_data(query_name)
    print('Device Data:', device_data)

Out put of the code :

Requesting URL:

https://api.xxxxx.dev/iot-db/data?select=id,version,imei,timestamp,network,data,project,location,latest&order=timestamp.desc

Response Status Code: 200
Response Content: []
Device Data: []

I have decoded the token and this is how the payload of the token looks like

{
  "aud": "api://xxxxxxxxxxxxxxxxxxxxxxxxxxxx",
  "iss": "https://sts.windows.net/82360567-02b6-4a1f-a4b2-910a811b8131/",
  "iat": 1721720388,
  "nbf": 1721720377,
  "exp": 1721727299,
  "aio": "E2dgYPCykjl65cyWyabKid9FhddlAAA=",
  "appid": "xxxxxxxxxxxxxxxxxxxxxxxxxx",
  "appidacr": "1",
  "idp": "https://sts.windows.net/8990567-02b6-4a1f-88b2-910a811b7331/",
  "oid": "b4022076-068e-4f1e-8c91-56a78be78308",
  "rh": "0.AXMAZwU2grYCH0qkspEKgRuBMceM2KOJBzhCvTv4l66wcIRzAAA.",
  "sub": "b4022076-068e-4f1e-8c91-56a78be78308",
  "tid": "82360567-068e-4f1f-8cb2-910a811b8131",
  "uti": "IsPMXlG0w0WOl7PgZu9vAA",
  "ver": "1.0"
}

I am stuck with this and i have no idea how to resolve this issue.

[wolfgangwalther]

What did you set the jwt-role-claim-key config option to?

[soorajiyer]

db-schema = "iot"
db-anon-role = "web_anon"
db-pool = 15
db-pool-timeout = 10
role-claim-key = ".oid"
server-host = "!4"
jwt-secret= "@public_jwt_keys.json"
log-level = "info"

the role-claim-key = ".oid"

[wolfgangwalther]

.oid, ok:

"oid": "b4022076-068e-4f1e-8c91-56a78be78308",

So a PostgreSQL ROLE with the name "b4022076-068e-4f1e-8c91-56a78be78308" exists, yes?

Can this role query the data you want to see when you do this directly in PostgreSQL?

SET ROLE "b4022076-068e-4f1e-8c91-56a78be78308";

SELECT * FROM iot.data;

[soorajiyer]

Yes the role name exists. The role is able to fetch data directly in PostgreSQL

[wolfgangwalther]

select=id,version,imei,timestamp,network,data,project,location,latest

Are all of those regular columns or any of them computed columns / functions?

[soorajiyer]

Those are regular columns. The problem is not only with the data, but also with the projects as well as the latestDeviceData tables. Initial problem was permission denied to access schema iot. So i did the following

CREATE ROLE "b4022076-068e-4f1e-8c91-56a78be78308"NOLOGIN;
GRANT web_anon TO "b4022076-068e-4f1e-8c91-56a78be78308";
GRANT USAGE ON SCHEMA iot TO "b4022076-068e-4f1e-8c91-56a78be78308";
GRANT SELECT ON ALL TABLES IN SCHEMA iot TO "b4022076-068e-4f1e-8c91-56a78be78308";

is there anything missing here??

[soorajiyer]

Hello, it worked finally. The problem was with the role and access within the PostgresSQL database.

CREATE ROLE "b4022076-068e-4f1e-8c91-56a78be78308"NOLOGIN;
GRANT web_anon TO "b4022076-068e-4f1e-8c91-56a78be78308";
GRANT USAGE ON SCHEMA iot TO "b4022076-068e-4f1e-8c91-56a78be78308";
GRANT SELECT ON ALL TABLES IN SCHEMA iot TO "b4022076-068e-4f1e-8c91-56a78be78308";

i did this queries again restarted the api and it worked. Thanks for the help.