# PostgreSQL
source: https://docs.chalk.ai/docs/postgresql

## Integrate with SQL data sources.

Chalk supports PostgreSQL
as a SQL source.
You can configure the PostgreSQL-specific
options using the PostgreSQLSource init args,
or configure the source through your dashboard, and
reference the source in your code.

### Adding PostgreSQL

By navigating to Integrations > Add a data source and selecting PostgreSQL, you'll find a form where you can input information about your PostgreSQL integration.
Note that the data source must be accessible by the IAM Role defined in your cluster deployment.

### Integrations Setup

After configuring your PostgreSQL integration in the dashboard, define your data sources in Python:

```
from chalk.sql import PostgreSQLSource

risk_pg = PostgreSQLSource(name="RISK_PG")
marketing_pg = PostgreSQLSource(name="MARKETING_PG")
```

Then reference them in SQL file resolvers using the name parameter. For example, to query from the RISK_PG source:

```
-- type: online
-- resolves: User
-- source: RISK_PG
SELECT id, credit_score FROM users
```

And to query from the MARKETING_PG source:

```
-- type: online
-- resolves: User
-- source: MARKETING_PG
SELECT id, email, campaign_status FROM users
```



### Authenticating with AWS RDS IAM

For databases hosted on Amazon RDS, Chalk can authenticate with
IAM database authentication
in place of a stored password. Chalk signs a short-lived auth token for every new connection, so your
Chalk environment holds no database password.

IAM authentication requires platform version v3.41.11 and onwards. Configuring it
with the PostgreSQLSource init args also requires chalkpy version 2.157.6 and onwards in your
project. Testing an IAM-authenticated data source from the dashboard requires platform version
v3.42.38 and onwards.

MySQL and Redshift data sources do not support IAM database authentication. Those sources
authenticate with a user name and password.

### Preparing your database

- Enable IAM database authentication on the RDS instance or Aurora cluster.
- Create the database user that Chalk connects as, and grant it the rds_iam role:CREATE USER chalk WITH LOGIN;
GRANT rds_iam TO chalk;Grant that user the read permissions your resolvers need. A user holding rds_iam authenticates
only with IAM tokens, so it does not need a password.
- Attach a policy allowing rds-db:connect for that user to the IAM role defined in your cluster
deployment:{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": "rds-db:connect",
      "Resource": "arn:aws:rds-db:us-east-1:123456789012:dbuser:db-ABCDEFGHIJKL01234/chalk"
    }
  ]
}The last two segments of the resource are the database's resource ID and the database user name.
The resource ID is a separate value from the instance identifier: find it under Configuration on
the instance's page in the RDS console. See
Creating and using an IAM policy for IAM database access
for the full ARN format.

### Configuring the data source

On the PostgreSQL data source form, set AWS RDS IAM authentication to Enabled, set AWS Region to
the region of your RDS instance, and leave Password blank. Those fields map to three integration
variables:

| Variable          | Description                                                                                                     |
| ----------------- | --------------------------------------------------------------------------------------------------------------- |
| `PG_AWS_IAM_AUTH` | Set to `true` to authenticate with IAM instead of a password                                                    |
| `PG_AWS_REGION`   | Region of the RDS instance, used when signing auth tokens; defaults to your cluster's ambient AWS configuration |
| `PG_AWS_ROLE_ARN` | Optional IAM role to assume before signing auth tokens                                                          |

You can also configure IAM authentication with the PostgreSQLSource init args:

```
from chalk.sql import PostgreSQLSource

risk_pg = PostgreSQLSource(
    name="RISK_PG",
    host="risk.abcdefghijkl.us-east-1.rds.amazonaws.com",
    port=5432,
    db="risk",
    user="chalk",
    aws_iam_auth=True,
    aws_region="us-east-1",
)
```

Chalk mints a token for each connection the pool opens, and RDS validates it during the connection
handshake. Enabling IAM authentication does not change how your resolvers query the source.

RDS refuses IAM authentication over an unencrypted connection, so Chalk connects IAM-authenticated
sources with sslmode=require.

### Connecting across AWS accounts

When the database lives in a different AWS account from your Chalk cluster, create a role in the
database's account carrying the rds-db:connect policy above, and set AWS Role ARN (or
aws_role_arn) to that role. Chalk assumes the role before signing each token. The role's trust
policy has to allow the IAM role defined in your cluster deployment to assume it.