Aurora Serverless Postgresql as data source for app sync

Question

I was facing problem with app sync where data source is Relational Database.

So, my goal is to connect Aurora server less postgresql cluster with app sync as data source and get it working.

Steps I have performed(all via server-less framework) :

Created aurora postgreSQL cluster.

Connected aurora postgreSQL cluster to app sync.

Created graphql Schema in app sync.

Authentication Type is - API key.

app sync configuration taken form here( https://github.com/serverless-components/aws-app-sync#data-sources--templates ). :

appSync:
name: Posts
authenticationType: API_KEY
apiKeys:
  - myApiKey
dataSources:
  - type: RELATIONAL_DATABASE
    name: Posts
    config:
      awsSecretStoreArn: 
        Ref: SecretsManager
      databaseName: "mydatabase"
      dbClusterIdentifier: 
        Ref: AuroraRDSCluster
schema: schema.graphql
mappingTemplates:
  - dataSource: Posts
    type: Query
    field: getPet
    request: "request.vtl"
    response: "response.vtl"
  - dataSource: Posts
    type: Query
    field: listPets
    request: "listPets_request.vtl"
    response: "listPets_response.vtl"

graphql schema taken from here( https://docs.aws.amazon.com/appsync/latest/devguide/tutorial-rds-resolvers.html )

my getPet request.vtl :

{
"version": "2018-05-29",
    "statements": [
        $util.toJson("select * from Pets WHERE id='$ctx.args.id'")
]

}

my getPet response.vtl:

$utils.toJson($utils.rds.toJsonObject($ctx.result)[0][0])

and everything deployed perfectly like cluster Arn(dbClusterIdentifier) , awsSecretStoreArn database name with app sync datasource , resolvers with schema.

And got graphql api with api key in output of deployment.

After this I have created a table Pets with column id, type, price with one record through query editor of rds cluster.

So now , when I was trying to query in console of app sync, I was getting value as null. query is :

    query {getPet(id: "1"){id type price }}

tried with node js code also but same result.

 
 
// url of your GraphQL API. If you configured a custom domain, you could use that instead
 

    const url =
 
  "https://z4#############.appsync-api.us-east-1.amazonaws.com/graphql";
 
 
// GraphQL API api key
 
const apiKey = "da2-7by6############";
 
 
// ID of the post you wanna query
 
const id = "1";
 
 
fetch(url, {
 
  method: "POST",
 
  headers: {
 
    "Content-Type": "application/json",
 
    "x-api-key": apiKey
 
  },
 
  body: JSON.stringify({
 
    query: `query {getPet(id: "${id}"){id}}`
 
  })
 
})
 
  .then(res => res.text())
 
  .then(post => console.log(post));

any help how to make it working.

Answer 1

So, the problem was with the default iam role policies assumed by appsync. By default appsync will create iam role with following policies

- Effect: Allow
  Action:
    - secretsmanager:GetSecretValue
  Resource: "your resource mentioned"
- Effect: Allow
  Action:
    - rds-data:DeleteItems
    - rds-data:ExecuteSql
    - rds-data:GetItems
    - rds-data:InsertItems
    - rds-data:UpdateItems
  Resource: "your resource mentioned"

Now here one thing was missing , which is

- rds-data:ExecuteStatement

So, i created new iam role with missing police and attached it to appsync and it works for me.