[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-performance
Subject: Re: SSL connection getting rejected on AWS RDS
From: Hannah Huang <hannah.huang.y () gmail ! com>
Date: 2020-10-01 4:51:24
Message-ID: 384A0D39-2E48-4EF0-B20A-85ADE6554D1D () gmail ! com
[Download RAW message or body]
Hi Aditya,
Yes, you need to grant the role to the user inside PostgreSQL database.
Please checkout this article: \
https://suyahuang.wordpress.com/2020/10/01/hands-on-lab-access-rds-postgresql-from-ec2-instance-without-password-how-to-configure-iam-db-authentication/
Let me know if you have any problem following through.
Thanks,
Hannah
> On 1 Oct 2020, at 1:50 am, aditya desai <admad123@gmail.com> wrote:
>
> Hi Hannah,
> Thank you very much!! this is really helpful. Do we need to pass 'sslrootcert" as \
> mentioned in the doc below? I see that you have not used it in your command.
> https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.Connecting.AWSCLI.PostgreSQL.html \
> <https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.Connecting.AWSCLI.PostgreSQL.html>
>
> Also do we have to grant the role below to the user?
>
> grant rds_iam to app_user;
>
>
> If you have any document/Steps to set this up from scratch,could you please \
> forward? That would be really helpful.
> Regards,
> Aditya.
>
>
> On Wed, Sep 30, 2020 at 4:47 PM Hannah Huang <hannah.huang.y@gmail.com \
> <mailto:hannah.huang.y@gmail.com>> wrote:
>
> > On 30 Sep 2020, at 5:19 pm, aditya desai <admad123@gmail.com \
> > <mailto:admad123@gmail.com>> wrote:
> > Hi,
> > We have AWS RDS and we are trying to connect to DB remotely from EC2 instance.as \
> > <http://instance.as/> client connection using psql. We are trying to set up IAM \
> > roles. We did all the necessary settings but got below error. Could you please \
> > advise?
> > Password for user lmp_cloud_dev:
> > psql: FATAL: PAM authentication failed for user "testuser"
> > FATAL: pg_hba.conf rejects connection for host "192.168.1.xxx", user "testuser", \
> > database "testdb", SSL off
> > Regards,
> > Aditya.
> >
>
> Hi Aditya,
>
> See the below example of me connecting to RDS from an EC2 instance:
>
> You need to change the $RDSHOST value
> you need to replace my "app_user" to your "testuser" and database "postgres" to \
> your "testdb"
> [ec2-user@ip-172-31-13-121 ~]$ export \
> RDSHOST="mypg.cfvvs1nh3f7i.ap-southeast-2.rds.amazonaws.com \
> <http://southeast-2.rds.amazonaws.com/>"
> [ec2-user@ip-172-31-13-121 ~]$ export PGPASSWORD="$(aws rds generate-db-auth-token \
> \
> --hostname $RDSHOST \
> --port 5432 \
> --username app_user)"
>
> [ec2-user@ip-172-31-13-121 ~]$ psql "host=$RDSHOST port=5432 sslmode=require \
> dbname=postgres user= app_user"
> psql (11.5, server 12.3)
> WARNING: psql major version 11, server major version 12.
> Some psql features might not work.
> SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, \
> compression: off) Type "help" for help.
> postgres=>
>
> Thanks,
> Hannah
[Attachment #3 (unknown)]
<html><head><meta http-equiv="Content-Type" content="text/html; \
charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; \
line-break: after-white-space;" class=""><div class="">Hi Aditya,</div><div \
class=""><br class=""></div><div class="">Yes, you need to grant the role to the user \
inside PostgreSQL database.</div><div class=""><br class=""></div>Please checkout \
this article: <a href="https://suyahuang.wordpress.com/2020/10/01/hands-on-lab-access- \
rds-postgresql-from-ec2-instance-without-password-how-to-configure-iam-db-authentication/" \
class="">https://suyahuang.wordpress.com/2020/10/01/hands-on-lab-access-rds-postgresql \
-from-ec2-instance-without-password-how-to-configure-iam-db-authentication/</a><div \
class=""><br class=""></div><div class="">Let me know if you have any problem \
following through.<br class=""><div class=""><br class=""></div><div \
class="">Thanks,</div><div class="">Hannah<br class=""><div><br class=""><blockquote \
type="cite" class=""><div class="">On 1 Oct 2020, at 1:50 am, aditya desai <<a \
href="mailto:admad123@gmail.com" class="">admad123@gmail.com</a>> wrote:</div><br \
class="Apple-interchange-newline"><div class=""><div dir="ltr" class=""><div \
dir="ltr" class="">Hi Hannah,<div class="">Thank you very much!! this is really \
helpful. Do we need to pass 'sslrootcert" as mentioned in the doc below? I see that \
you have not used it in your command. </div><div class=""><br \
class=""></div><div class=""><a \
href="https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.Connecting.AWSCLI.PostgreSQL.html" \
class="">https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.Connecting.AWSCLI.PostgreSQL.html</a><br \
class=""></div><div class=""><br class=""></div><div class="">Also do we have to \
grant the role below to the user?</div><div class=""><br class=""></div><div \
class="">grant rds_iam to app_user;</div><div class=""><br class=""></div><div \
class=""><br class=""></div><div class="">If you have any document/Steps to set this \
up from scratch,could you please forward? That would be really helpful.</div><div \
class=""><br class=""></div><div class="">Regards,</div><div \
class="">Aditya.</div><div class=""><br class=""></div></div></div><br class=""><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, Sep 30, 2020 at 4:47 PM \
Hannah Huang <<a href="mailto:hannah.huang.y@gmail.com" \
class="">hannah.huang.y@gmail.com</a>> wrote:<br class=""></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div style="overflow-wrap: break-word;" \
class=""><br class=""><div class=""><br class=""><blockquote type="cite" \
class=""><div class="">On 30 Sep 2020, at 5:19 pm, aditya desai <<a \
href="mailto:admad123@gmail.com" target="_blank" class="">admad123@gmail.com</a>> \
wrote:</div><br class=""><div class=""><div dir="ltr" class=""><div dir="ltr" \
class="">Hi,<br class=""><div class="">We have AWS RDS and we are trying to connect \
to DB remotely from EC2 <a href="http://instance.as/" target="_blank" \
class="">instance.as</a> client connection using psql. We are trying to set up IAM \
roles. We did all the necessary settings but got below error. Could you please \
advise?</div><div class=""><br class=""></div><div class=""><p class="MsoNormal" \
style="margin-bottom:0.0001pt;line-height:normal"><span \
style="font-size:10pt;font-family:"Segoe UI",sans-serif" class="">Password \
for user lmp_cloud_dev:</span><span class=""></span></p><p class="MsoNormal" \
style="margin-bottom:0.0001pt;line-height:normal"><span \
style="font-size:10pt;font-family:"Segoe UI",sans-serif" class="">psql: \
FATAL: PAM authentication failed for user "testuser"</span><span \
class=""></span></p><p class="MsoNormal" \
style="margin-bottom:0.0001pt;line-height:normal"><span \
style="font-size:10pt;font-family:"Segoe UI",sans-serif" \
class="">FATAL: pg_hba.conf rejects connection for host "192.168.1.xxx", user \
"testuser", database "testdb", SSL off</span><span class=""></span></p><p \
class="MsoNormal" style="margin-bottom:0.0001pt;line-height:normal"><span \
style="font-size:10pt;font-family:"Segoe UI",sans-serif" class=""><br \
class=""></span></p><p class="MsoNormal" \
style="margin-bottom:0.0001pt;line-height:normal"><span \
style="font-size:10pt;font-family:"Segoe UI",sans-serif" \
class="">Regards,</span></p><p class="MsoNormal" \
style="margin-bottom:0.0001pt;line-height:normal"><span \
style="font-size:10pt;font-family:"Segoe UI",sans-serif" \
class="">Aditya.</span></p><div class=""><br class=""></div></div></div></div> \
</div></blockquote><br class=""></div><div class="">Hi Aditya,</div><div class=""><br \
class=""></div>See the below example of me connecting to RDS from an EC2 \
instance:<div class=""><br class=""></div><div class="">You need to change the \
$RDSHOST value</div><div class="">you need to replace my "app_user" to your \
"testuser" and database "postgres" to your "testdb"<div class=""><br class=""><div \
class=""><div class="">[ec2-user@ip-172-31-13-121 ~]$ export \
RDSHOST="mypg.cfvvs1nh3f7i.ap-<a href="http://southeast-2.rds.amazonaws.com/" \
target="_blank" class="">southeast-2.rds.amazonaws.com</a>"</div><div class=""><br \
class=""></div><div class="">[ec2-user@ip-172-31-13-121 ~]$ export PGPASSWORD="$(aws \
rds generate-db-auth-token \</div><div class="">--hostname $RDSHOST \</div><div \
class="">--port 5432 \</div><div class="">--username app_user)"</div></div><div \
class=""><br class=""></div><div class=""><div class="">[ec2-user@ip-172-31-13-121 \
~]$ psql "host=$RDSHOST port=5432 sslmode=require dbname=postgres user= \
app_user"</div><div class=""><br class=""></div><div class="">psql (11.5, server \
12.3)</div><div class="">WARNING: psql major version 11, server major version \
12.</div><div class="">Some psql features might not work.</div><div class="">SSL \
connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, \
compression: off)</div><div class="">Type "help" for help.</div><div \
class="">postgres=></div></div></div><div class=""><br class=""></div><div \
class="">Thanks,</div><div class="">Hannah</div></div></div></blockquote></div> \
</div></blockquote></div><br class=""></div></div></body></html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic