Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kNqZA-0004Az-4z for pgsql-performance@arkaria.postgresql.org; Thu, 01 Oct 2020 04:51:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kNqZ8-0003JP-VF for pgsql-performance@arkaria.postgresql.org; Thu, 01 Oct 2020 04:51:38 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kNqZ8-0003JH-KF for pgsql-performance@lists.postgresql.org; Thu, 01 Oct 2020 04:51:38 +0000 Received: from mail-pg1-x52d.google.com ([2607:f8b0:4864:20::52d]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kNqZ1-0005LV-55 for pgsql-performance@lists.postgresql.org; Thu, 01 Oct 2020 04:51:37 +0000 Received: by mail-pg1-x52d.google.com with SMTP id 5so3038162pgf.5 for ; Wed, 30 Sep 2020 21:51:31 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=X3qolhW6I37aSkeMwKvH9yBfYlaE6q0s20eP7sZOxIM=; b=Vo0ppMJs7VYvd+XbO+3+IlGKyWndvu3RaZLy0HazLOk5aug03kelC7yWXVjtef9Dyw fT7wxQBb4KYcs2HnUzjmB8DJKl6HnU9O2yRsQWqKvQ7xRtZukYHwjY9BrG0PZxDDoG1n S5VudiyeaE8OdGUDCQSSzCPhWCGvlX+YV8C/CdcvTKvdf5xrQCElRofrehEE4jLyUVWI HhwXSiD9a9v5AoIsj6Oa3stVjgh3DrYGvcS8JLGFIfcIewtglRmcI+oZbNm1ymHUQkB7 MCD+cyldgFpTvncfKsoG7KMCuPJfZm/n5kaWo9xXga2MSp5fKg9pQrYOunFFe+skDlde YMrw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=X3qolhW6I37aSkeMwKvH9yBfYlaE6q0s20eP7sZOxIM=; b=WHgblHrTijilWJtCj+Z9kaXwVRqCZD9dwweu1jVOEpEPTjO9UdPwnWnbGmnk9rzcu6 xf8bPJt5sG8PX9Ar+pAMhlMOiqKh7Lv8V9x024Rzt1srCOw3S3gZA3CBKC05+QUm9Ss2 l/nlcH2H8gBNTkygaDmKWDynfOvLTtQdFh8cp3FVdiC6iNLqAtW+khv4dfVCtivYBxJd 3ZI26AGRV50w6N407doD+yEUGG+9+ncMeJofJNE6jUTuCwTM5NpIisXyACWj3XXjMIO/ coBOqonoO4IniCwCMhlPIFkylf0o999YVGyVGt021Q2A4CC2hw8IUa8Y95Vs9mWmkUPV zzRQ== X-Gm-Message-State: AOAM533dHKz3mbJH4nN2ldWGoxotdBVjFEtxeLuYL0UsQ2R+W1N4r/a8 YWrxoMoa0DT7LErMeyYZ6lI= X-Google-Smtp-Source: ABdhPJwoLFrQ7ZlSlqtIBZjWGzKHuX+6ltIpcGGKcg2sW1gekUVm/cqG+b27W5fMI9ELxl5YTHyivg== X-Received: by 2002:a63:160b:: with SMTP id w11mr4676472pgl.110.1601527889202; Wed, 30 Sep 2020 21:51:29 -0700 (PDT) Received: from 192-168-1-114.tpgi.com.au ([203.63.94.51]) by smtp.gmail.com with ESMTPSA id d64sm272925pfc.183.2020.09.30.21.51.27 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Wed, 30 Sep 2020 21:51:28 -0700 (PDT) From: Hannah Huang Message-Id: <384A0D39-2E48-4EF0-B20A-85ADE6554D1D@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_6D31427A-FD2D-4F0D-A120-3CFCCE2B3522" Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.1\)) Subject: Re: SSL connection getting rejected on AWS RDS Date: Thu, 1 Oct 2020 14:51:24 +1000 In-Reply-To: Cc: pgsql-performance@lists.postgresql.org To: aditya desai References: X-Mailer: Apple Mail (2.3608.120.23.2.1) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_6D31427A-FD2D-4F0D-A120-3CFCCE2B3522 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 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-postgre= sql-from-ec2-instance-without-password-how-to-configure-iam-db-authenticat= ion/ Let me know if you have any problem following through. Thanks, Hannah > On 1 Oct 2020, at 1:50 am, aditya desai wrote: >=20 > 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.=20 >=20 > = https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBA= uth.Connecting.AWSCLI.PostgreSQL.html = >=20 > Also do we have to grant the role below to the user? >=20 > grant rds_iam to app_user; >=20 >=20 > If you have any document/Steps to set this up from scratch,could you = please forward? That would be really helpful. >=20 > Regards, > Aditya. >=20 >=20 > On Wed, Sep 30, 2020 at 4:47 PM Hannah Huang > wrote: >=20 >=20 >> On 30 Sep 2020, at 5:19 pm, aditya desai > wrote: >>=20 >> Hi, >> We have AWS RDS and we are trying to connect to DB remotely from EC2 = 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? >>=20 >> 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 >>=20 >> Regards, >> Aditya. >>=20 >=20 > Hi Aditya, >=20 > See the below example of me connecting to RDS from an EC2 instance: >=20 > You need to change the $RDSHOST value > you need to replace my =E2=80=9Capp_user=E2=80=9D to your = =E2=80=9Ctestuser=E2=80=9D and database =E2=80=9Cpostgres=E2=80=9D to = your =E2=80=9Ctestdb=E2=80=9D >=20 > [ec2-user@ip-172-31-13-121 ~]$ export = RDSHOST=3D"mypg.cfvvs1nh3f7i.ap-southeast-2.rds.amazonaws.com = " >=20 > [ec2-user@ip-172-31-13-121 ~]$ export PGPASSWORD=3D"$(aws rds = generate-db-auth-token \ > --hostname $RDSHOST \ > --port 5432 \ > --username app_user)=E2=80=9D >=20 > [ec2-user@ip-172-31-13-121 ~]$ psql "host=3D$RDSHOST port=3D5432 = sslmode=3Drequire dbname=3Dpostgres user=3D app_user" >=20 > 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=3D> >=20 > Thanks, > Hannah --Apple-Mail=_6D31427A-FD2D-4F0D-A120-3CFCCE2B3522 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
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/UsingWit= hRDS.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> wrote:


On 30 = Sep 2020, at 5:19 pm, aditya desai <admad123@gmail.com> wrote:

Hi,
We have AWS RDS and we are trying to connect = to DB remotely from EC2 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 =E2=80=9Capp_user=E2=80=9D= to your =E2=80=9Ctestuser=E2=80=9D and database =E2=80=9Cpostgres=E2=80=9D= to your =E2=80=9Ctestdb=E2=80=9D

[ec2-user@ip-172-31-13-121 ~]$ export = RDSHOST=3D"mypg.cfvvs1nh3f7i.ap-southeast-2.rds.amazonaws.com"

[ec2-user@ip-172-31-13-121 ~]$ export = PGPASSWORD=3D"$(aws rds generate-db-auth-token \
--hostname $RDSHOST \
--port 5432 = \
--username app_user)=E2=80=9D

[ec2-user@ip-172-31-13-121 ~]$ psql "host=3D$RDSHOST = port=3D5432 sslmode=3Drequire dbname=3Dpostgres user=3D = 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=3D>

Thanks,
Hannah

= --Apple-Mail=_6D31427A-FD2D-4F0D-A120-3CFCCE2B3522--