Aws glue connects to RDS PostgreSQL

1

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

asked 3 years ago9.2K views
2 Answers
1
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.

answered 3 years ago
  • Actually, the problem is not solved, the Glue connection is working fine with RDS with postgres13.x but not on 14.x or 15.x. I am still struggling as I can't revert back to 13.x

1

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;

  1. 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.
  2. 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
  3. Use this new user aws_glue in your Glue connection. This should work. It worked in my case.
answered a year ago
  • This worked for me too. Thanks @bansalakhil

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions