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

gefragt vor 3 Jahren8890 Aufrufe
2 Antworten
1
Akzeptierte Antwort

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.

beantwortet vor 3 Jahren
  • 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.
beantwortet vor einem Jahr
  • This worked for me too. Thanks @bansalakhil

Du bist nicht angemeldet. Anmelden um eine Antwort zu veröffentlichen.

Eine gute Antwort beantwortet die Frage klar, gibt konstruktives Feedback und fördert die berufliche Weiterentwicklung des Fragenstellers.

Richtlinien für die Beantwortung von Fragen