CODEWITHSUNDEEP
rpostgresqlamazon-web-servicesamazon-ec2shiny
Beeba
Beeba

Reputation: 642

PostgreSQL dbConnect to shiny app in ec2 instance

I have an ec2 instance set up with my shiny app and my postgresql database, I want to get the shiny-app to read from the database

If I type psql and \conninfo while ssh-ed into my instance I get

You are connected to database "ubuntu" as user "ubuntu" via socket in "/var/run/postgresql" at port "5432".

When I use R in the ec2 command line and type the following, I can read from my database no problem!

drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname = "ubuntu", host = "/var/run/postgresql", port = 5432, user = "ubuntu", password = pw)

enter image description here

However, when I put these same lines in my shiny app.R file I get

 Error in postgresqlNewConnection(drv, ...) : 
 RS-DBI driver: (could not connect ubuntu@/var/run/postgresql:5432 on dbname "ubuntu": FATAL:  Peer authentication failed for user "ubuntu")

I've tried so many different values for host like

host = "localhost"
host = "my ec2 public ip address"
host = "127.0.0.1"

for example and nothing has been working. my security group for this ec2 instance has an inboud connection to port 5432.

all my inbound connections on my ec2 instance

could this be it: why is one file green and the other pink? the green one is the one that works (local) and the pink one is on my instance

enter image description here

enter image description here

Upvotes: 1

Views: 392

Answers (1)

Beeba
Beeba

Reputation: 642

Finally figured it out.. this is the same problem as Getting error: Peer authentication failed for user "postgres", when trying to get pgsql working with rails except that I was getting a different error for the same underlying problem.

the answer that worked for me is the second one:

1.

nano /etc/postgresql/9.x/main/pg_hba.conf

change peer in this line 

local   all             postgres                                peer

to

local   all             postgres                                trust
  1. Restart the server
sudo service postgresql restart
  1. Login into psql and set your password

psql -U postgres

ALTER USER postgres with password 'your-pass';
  1. Finally change the pg_hba.conf from
local   all             postgres                                trust

to

local   all             postgres                                md5

and that finally worked

Upvotes: 1

Related Questions