I'm new to postgres and I'm struggling with 401 errors on a project that uses a 'codeless' postgrest api with JWT authentication. Now stuck!

Can anyone suggest what I can do to diagnose what's going on?

Thanks!

Docker

I have two docker services running, using this compose file, which appears to start correctly, with the admin-api connecting to the db...

  db:
    image: postgis/postgis:12-master
    ports:
      - '55432:5432'
    environment:
      POSTGRES_DB: #db_name
      POSTGRES_USER: #admin_user
      POSTGRES_PASSWORD: #admin_pass
    volumes:
      - type: bind
        source: ./db-schema
        target: /db-schema
        read_only: true
    command: >
        postgres -c custom.jwt_secret= #jwt_key_definition

  admin-api:
    image: postgrest/postgrest:v7.0.1
    ports:
      - "3000:3000"
    environment:
      PGRST_DB_URI: #db_uri
      PGRST_DB_SCHEMA: api
      PGRST_DB_ANON_ROLE: anonymous
      PGRST_LOG_LEVEL: "debug"
      PGRST_ROLE_CLAIM_KEY: '.role'
      PGRST_JWT_SECRET: #same_jwt_key_definition_as_above
    depends_on:
      - db

SQL

I have an SQL file to define the db-schema including an admin_users table, and grant permissions for views to 'db_user'

This section uses a custom sign_jwt function to handle logins and signing JWT tokens.

It appears to generate valid JWT tokens with a correct role claim, and valid signature tested on jwt.io...

create role db_user nologin;

grant anonymous to admin_user;
grant db_user to admin_user;

grant usage on schema api to anonymous;
grant usage on schema api to db_user;


CREATE OR REPLACE FUNCTION api.authenticate(
  IN username text,
  IN password text
)
RETURNS json
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  _user public.admin_users;
  jwt_token text;
BEGIN
  -- Add parameter validation
  IF username IS NULL OR password IS NULL THEN
    RETURN json_build_object('error', 'Email and password are required');
  END IF;

  SELECT * INTO _user
  FROM public.admin_users
  WHERE public.admin_users.email = username
  AND public.admin_users.password_hash = crypt(password, public.admin_users.password_hash);
  
  IF _user.id IS NULL THEN
    RETURN json_build_object('error', 'Invalid email or password');
  END IF;

  -- Prepare payload for JWT
  jwt_token := sign_jwt(
    jsonb_build_object(
      'user_id', _user.id,
      'role', _user.role,
      'exp', extract(epoch from (now() + interval '7 days'))::integer  -- Token expiration time
    ),
    current_setting('custom.jwt_secret',true)  -- Use from the environment
  );
  RAISE INFO 'JWT Token: %', jwt_token;

  -- Return the JWT token along with a success message
  RETURN json_build_object('message', 'Authentication successful', 'token', jwt_token);

END;
$$;

-- Grant execute permission to anonymous
GRANT EXECUTE ON FUNCTION api.authenticate(text, text) TO anonymous;

react-admin

The frontend which uses react-admin with this package:

It's login function gets a 'Authentication successful' response and stores the token in authData.token

I use this function to add the token to request headers...

// HTTP client that adds JWT auth header
export const httpClient = (url: string, options: any = {}) => {
  const authData = JSON.parse(localStorage.getItem('auth') || '{}');
  console.log("Auth Data for httpClient:", authData);

  if (!authData || !authData.token) {
    return fetchUtils.fetchJson(url, options);
  }

  options.user = {
        authenticated: true,
        token: `${authData.token}`,
    };

  if (!options.headers) {
    options.headers = new Headers({ 'Content-Type': 'application/json' });
  }

  options.headers.set('Authorization', `Bearer ${authData.token}`);
  console.log("Request Headers:", Object.fromEntries(options.headers));

  return fetchUtils.fetchJson(url, options);
};

I've checked that the token appears correctly at each stage but I continue to get 401 unauthorised errors following a successful log-in.

The db log shows errors for requests that should be allowed to db_user...

ERROR: permission denied for view...

The admin-api service doesn't appear to show any errors. Only the request details...

172.19.0.1 - - [06/Feb/2025:17:59:36 +0000] "GET /config?id=eq.config HTTP/1.1" 200 - "http://localhost:9000/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/132.0.0.0 Safari/537.36"

Can anyone suggest what I can do to diagnose what's going on?

Thanks again!