We are trying to do Federated query from Redshift to Aurora PostgreSQL DB with Redshift version 1.0.51973 and Aurora PG latest version 15.2, please find the steps we followed for the setup.
CREATE EXTERNAL SCHEMA test_pg;
FROM POSTGRES
DATABASE 'test_db' SCHEMA 'test_schema'
URI abcd.us-east-1.rds.amazonaws.com'
IAM_ROLE 'arn:aws:iam::123:role/test_role
SECRET_ARN 'arn:aws:secretsmanager:us-east-1:123:secret:test_secrets’;
SELECT count(*) FROM test_pg.pg_table_test; -- Fails
Above query faiiling with SQL Error [D;600]: ERROR: authentication method 10 not supported
Caused by: com.amazon.redshift.util.RedshiftException: ERROR: authentication method 10 not supported
at com.amazon.redshift.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2601)
at com.amazon.redshift.core.v3.QueryExecutorImpl.processResultsOnThread(QueryExecutorImpl.java:2269)
at com.amazon.redshift.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1880)
at com.amazon.redshift.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1872)
at com.amazon.redshift.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
at com.amazon.redshift.jdbc.RedshiftStatementImpl.executeInternal(RedshiftStatementImpl.java:514)
at com.amazon.redshift.jdbc.RedshiftStatementImpl.execute(RedshiftStatementImpl.java:435)
at com.amazon.redshift.jdbc.RedshiftStatementImpl.executeWithFlags(RedshiftStatementImpl.java:376)
at com.amazon.redshift.jdbc.RedshiftStatementImpl.executeCachedSql(RedshiftStatementImpl.java:362)
at com.amazon.redshift.jdbc.RedshiftStatementImpl.executeWithFlags(RedshiftStatementImpl.java:339)
at com.amazon.redshift.jdbc.RedshiftStatementImpl.execute(RedshiftStatementImpl.java:329)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:329)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.lambda$0(JDBCStatementImpl.java:131)
at org.jkiss.dbeaver.utils.SecurityManagerUtils.wrapDriverActions(SecurityManagerUtils.java:96)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131)
Seems RS is not supporting the latest scram-sha-256 authentication method of PG. In this regard what’s the best option suggested by Redshift team.