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 <admad...@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.huan...@gmail.com > <mailto:hannah.huan...@gmail.com>> wrote: > > >> On 30 Sep 2020, at 5:19 pm, aditya desai <admad...@gmail.com >> <mailto:admad...@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
