SQLSchema - Error: "contains" operator on scalar field is not allowed in auth filter

[kekami]

Environment information

System:
  OS: macOS 14.4.1
  CPU: (10) arm64 Apple M1 Max
  Memory: 153.73 MB / 32.00 GB
  Shell: /bin/zsh
Binaries:
  Node: 20.12.2 - ~/.nvm/versions/node/v20.12.2/bin/node
  Yarn: 3.8.2 - ~/.nvm/versions/node/v20.12.2/bin/yarn
  npm: 10.5.0 - ~/.nvm/versions/node/v20.12.2/bin/npm
  pnpm: undefined - undefined
NPM Packages:
  @aws-amplify/backend: 1.0.2
  @aws-amplify/backend-cli: 1.0.3
  aws-amplify: 6.3.2
  aws-cdk: 2.142.1
  aws-cdk-lib: 2.142.1
  typescript: 5.4.5
AWS environment variables:
  AWS_SDK_LOAD_CONFIG = 1
  AWS_STS_REGIONAL_ENDPOINTS = regional
  AWS_NODEJS_CONNECTION_REUSE_ENABLED = 1
No CDK environment variables

Description

Getting the following error when querying a nested object.

2024-05-28T21:00:09.760Z	e74367bc-75c9-4254-9ccd-f5db72b9f931	INFO	Error: "contains" operator on scalar field is not allowed in auth filter
    at Object.contains (/opt/nodejs/node_modules/rds-query-processor/adapters/postgres-adapter.js:67:31)
    at /opt/nodejs/node_modules/rds-query-processor/utils/sql-utils.js:48:83
    at Array.forEach (<anonymous>)
    at /opt/nodejs/node_modules/rds-query-processor/utils/sql-utils.js:42:39
    at Array.forEach (<anonymous>)
    at toSQLQueryExpression (/opt/nodejs/node_modules/rds-query-processor/utils/sql-utils.js:14:28)
    at /opt/nodejs/node_modules/rds-query-processor/utils/sql-utils.js:23:86
    at Array.map (<anonymous>)
    at /opt/nodejs/node_modules/rds-query-processor/utils/sql-utils.js:22:42
    at Array.forEach (<anonymous>)

Query:

query MyQuery {
  getUser(id: "f0bc490c-0061-70c3-fc3d-dbaa7ebc2eb9") {
    id
    worker {
      id
    }
  }
}

Schema:

.setRelationships((models) => [
    models.User.relationships({
      worker: a.hasOne('Worker', 'user_id'),
    }),

    models.Worker.relationships({
      user: a.belongsTo('User', 'user_id'),
    }),
  ])
.setAuthorization((models) => [
    models.User.authorization((allow) => [
      allow.ownerDefinedIn('owner'),
      allow.groups(['admin']),
    ]),
    models.Worker.authorization((allow) => [
      allow.ownersDefinedIn('owners'),
      allow.groups(['admin']),
    ]),
  ])

[ykethan]

Hey👋 thanks for raising this! I'm going to transfer this over to our API repository for better assistance 🙂

[palpatim]

@kekami Can you share the SQL table definitions for User and Worker, as well as the generated source schema.sql.ts? It's hard to pinpoint what the problem might be without a bit more detail.

[kekami]

Hey @palpatim Managed to create a smaller reproducible example of this one as well.

Table definitions

CREATE TABLE users (
    id UUID PRIMARY KEY NOT NULL,
    owner TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE workers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    owners TEXT[],
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    user_id UUID NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

schema.sql.ts

/* eslint-disable */
/* THIS IS AN AUTOGENERATED FILE. DO NOT EDIT THIS FILE DIRECTLY. */
import { a } from "@aws-amplify/data-schema";
import { configure } from "@aws-amplify/data-schema/internals";
import { secret } from "@aws-amplify/backend";

export const schema = configure({
    database: {
        identifier: "IDXrrO7OzwePLc4YSnk7J3Mw",
        engine: "postgresql",
        connectionUri: secret("SQL_CONNECTION_STRING_REPRO2")
    }
}).schema({
    "users": a.model({
        id: a.id().required(),
        owner: a.string(),
        created_at: a.datetime(),
        updated_at: a.datetime()
    }).identifier([
        "id"
    ]),
    "workers": a.model({
        id: a.id().required(),
        owners: a.string().array(),
        created_at: a.datetime(),
        updated_at: a.datetime(),
        user_id: a.id().required()
    }).identifier([
        "id"
    ])
});

resource.ts

import { type ClientSchema, a, defineData } from "@aws-amplify/backend";

import { schema as generatedSqlSchema } from "./schema.sql.js";

const sqlSchema = generatedSqlSchema
  .renameModels(() => [
    ["users", "User"],
    ["workers", "Worker"],
  ])
  .setRelationships((models) => [
    models.User.relationships({
      worker: a.hasOne("Worker", "user_id"),
    }),

    models.Worker.relationships({
      user: a.belongsTo("User", "user_id"),
    }),
  ])
  .setAuthorization((models) => [
    models.User.authorization((allow) => [
      allow.ownerDefinedIn("owner"),
      allow.groups(["admin"]),
    ]),
    models.Worker.authorization((allow) => [
      allow.ownersDefinedIn("owners"),
      allow.groups(["admin"]),
    ]),
  ]);

export type Schema = ClientSchema<typeof sqlSchema>;

export const data = defineData({
  schema: sqlSchema,
  authorizationModes: {
    defaultAuthorizationMode: "userPool",
    apiKeyAuthorizationMode: {
      expiresInDays: 30,
    },
  },
});

Also, worth mentioning, I noticed that the error only occurs when using userpool auth, but works fine with IAM auth.

[kekami]

The database is hosted on https://neon.tech/, not using AWS RDS if relevant.

[palpatim]

Thanks for the repro, @kekami.

1. Does this behavior happen in queries using the AppSync console, a JS client, or both?
2. Does the behavior change if you include workers.user_id in your query selection set? Since user_id is a required field, I'm wondering if there is some validation that might be conflicting.

Either way, we'll try digging into it locally.

[kekami]

Hi @palpatim

1. Tested with both, and neither work.

const { data, errors } = await client.models.User.list({
  selectionSet: ["id", "worker.id"],
});

query MyQuery {
  listUsers {
    items {
      id
      worker {
        id
      } 
    }
  }
}

2. Same behaviour when including worker.user_id in both the JS client and AppSync console.

[kekami]

I suspect it has to do with owners based auth, when the related object has an owners array. Since the inverse query works, e.g.

query MyQuery {
  listWorkers {
    items {
      id
      user {
        id
      } 
    }
  }
}

[kekami]

@palpatim Dug a little and found the reason, in the VTL for Connection Queries e.g.

## [Start] Invoke RDS Lambda data source. **
#if( $ctx.stash.deniedField )
  #set( $result = {
  "items":   []
} )
  #return($result)
#end
#set( $lambdaInput = {} )
#set( $lambdaInput.args = {} )
#set( $lambdaInput.table = "service_agreements" )
#set( $lambdaInput.operation = "LIST" )
#set( $lambdaInput.operationName = "ConnectionQuery" )
#set( $lambdaInput.args.metadata = {} )
#set( $lambdaInput.args.metadata.keys = [] )
#set( $lambdaInput.args.metadata.arrayFields = [] )
#set( $lambdaInput.args.metadata.nonScalarFields = [] )
#set( $lambdaInput.args.metadata.fieldMap = {} )
$util.qr($lambdaInput.args.metadata.fieldMap.putAll($util.defaultIfNull($context.stash.fieldMap, {})))
$util.qr($lambdaInput.args.putAll($util.defaultIfNull($context.arguments, {})))
#if( !$lambdaInput.args.filter )
  #set( $lambdaInput.args.filter = {} )
#end
#if( !$util.isNullOrEmpty($ctx.stash.authFilter) )
  #set( $lambdaInput.args.metadata.authFilter = $ctx.stash.authFilter )
#end
$util.qr($lambdaInput.args.filter.put("customer_id", {
  "eq": $ctx.source.id
}))
$util.qr($lambdaInput.args.metadata.keys.addAll($util.defaultIfNull($ctx.stash.keys, [])))
{
  "version": "2018-05-29",
  "operation": "Invoke",
  "payload":   $util.toJson($lambdaInput)
}
## [End] Invoke RDS Lambda data source. **

The problem is here:

#set( $lambdaInput.args.metadata.arrayFields = [] )
#set( $lambdaInput.args.metadata.nonScalarFields = [] )

These should be:

#set( $lambdaInput.args.metadata.arrayFields = ["owners"] )
#set( $lambdaInput.args.metadata.nonScalarFields = ["owners"] )

Not quite sure why they aren't being set though.

[github-actions]

This issue is now closed. Comments on closed issues are hard for our team to see.
If you need more assistance, please open a new issue that references this one.

[palpatim]

@kekami Thanks very much for your effort to troubleshoot and fix the issue, and for including tests in your PR! The fix is merged in #2689, and will be pushed to latest with our next release.

[palpatim]

This was released in https:/aws-amplify/amplify-category-api/releases/tag/%40aws-amplify%2Famplify-category-api%405.12.3

[github-actions]

This issue is now closed. Comments on closed issues are hard for our team to see.
If you need more assistance, please open a new issue that references this one.