Aws glue connects to RDS PostgreSQL

Question

I failed to connect to the RDS postgreSQL database using glue, and failed to return the following message:

Check that your connection definition references your JDBC database with correct URL syntax, username, and password. The authentication type 10 is not supported. Check that you have configured the pg_hba.conf file to include the client's IP address or subnet, and that it is using an authentication scheme supported by the driver.

Exiting with error code 30

My connection steps:
type	JDBC
JDBC URL	jdbc:postgresql://xxx.xxx.us-west-2.rds.amazonaws.com:5432/xxx
VPC ID	       vpc-xxx
Subnet	       xxx
Security group	       sg-xxx
SSL connection required	false

I have checked the pg database for the above configuration and there should be no problem.

My glue IAM permissions：

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": "rds:*",
            "Resource": "*",
            "Condition": {
                "BoolIfExists": {
                    "aws:MultiFactorAuthPresent": "true"
                }
            }
        },
        {
            "Sid": "VisualEditor1",
            "Effect": "Allow",
            "Action": [
                "s3:GetAccessPoint",
                "ec2:DescribeAddresses",
                "ec2:DescribeByoipCidrs",
                "s3:GetBucketPolicy",
                "glue:*",
                "kms:*",
                "s3:GetAccessPointPolicyStatus",
                "s3:GetBucketPolicyStatus",
                "s3:GetBucketPublicAccessBlock",
                "s3:GetMultiRegionAccessPointPolicyStatus",
                "rds-db:*",
                "s3:GetMultiRegionAccessPointPolicy",
                "s3:ListAccessPoints",
                "s3:GetMultiRegionAccessPoint",
                "rds-data:*",
                "s3:ListMultiRegionAccessPoints",
                "s3:GetBucketAcl",
                "s3:DescribeMultiRegionAccessPointOperation",
                "s3:PutObject",
                "s3:GetObject",
                "s3:GetAccountPublicAccessBlock",
                "s3:ListAllMyBuckets",
                "ec2:DescribeVpcs",
                "ec2:DescribeVpcEndpoints",
                "s3:GetBucketLocation",
                "s3:GetAccessPointPolicy"
            ],
            "Resource": "*"
        }
    ]
}

The error message gives three possible directions:
1
Check that your connection definition references your JDBC database with correct URL syntax, username, and password.

I don't think this should be wrong

2
The authentication type 10 is not supported.

I'm not exactly sure what this error means, and all my Google queries are to modify pg_hba.conf. However, RDS does not provide modifications to this file.

3
Check that you have configured the pg_hba.conf file to include the client's IP address or subnet, and that it is using an authentication scheme supported by the driver.
I don't understand what this mistake means

Accepted Answer

I have solved the problem
The problem is caused by the fact that the PGSQL engine is 14+
I changed it back to 13.4 and everything is fine.

Answer

Starting from Postgres version 14 `scram-sha-256` is the default password encryption type.  And it looks like it is not being supported by the JDBC driver used by Glue.

This is how I solved it in my case.

1. Create a new parameter group if using the default parameter group, and attach to RDS
2. Changed `password_encryption` to `md5`
3. RDS reboot may be required
4. Connect to DB using existing credentials, this must be using `scram-sha-256`
5. Create a new user and grant access to the required db/schema:
```
CREATE ROLE glue_readaccess;
GRANT CONNECT ON DATABASE MY_DB TO glue_readaccess;
GRANT USAGE ON SCHEMA public TO glue_readaccess;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO glue_readaccess;
CREATE USER aws_glue WITH PASSWORD '323233232klfdjkfdjkfjkdjfjdfjdkjfdjk';
GRANT glue_readaccess TO aws_glue;

```

6. Check that new user has `encryption_type` to md5, older user should have `scram-sha-256`. At this moment both types of users should be able to connect to DB with their respective credentials.
7. Note: From now onwards all new users would have `md5` password encryption. Even if we reset the password for old users, those also are `md5`
8. Use this new user `aws_glue` in your Glue connection. This should work. It worked in my case.

Aws glue connects to RDS PostgreSQL

Relevanter Inhalt