1

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

Beeba
  • 642
  • 1
  • 7
  • 18
  • Is the inbound connection open to all IP address? – MLavoie May 04 '18 at 22:39
  • I’m not sure, how would I know? Does it say somewhere on the inbound security group section on AWS? – Beeba May 05 '18 at 02:34
  • difficult to explain, maybe this could help: https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/authorizing-access-to-an-instance.html – MLavoie May 05 '18 at 08:38
  • hmm I've added an image of all my inbound connections, but I don't think that's the problem because this app used to run on this instance without connecting to a database and everyone who had the instance public IP address could access it (with a password I created).. the problem lies in getting the shiny app to read from the database. Also to be clear both the app and the database are on the same instance – Beeba May 05 '18 at 11:24
  • You also need to give access to your database – MLavoie May 05 '18 at 13:43
  • How could I do that? Do I have missing inbound connections? – Beeba May 05 '18 at 19:41
  • you should find it in the security group of your database. It is similar to the EC2 instance. – MLavoie May 05 '18 at 22:20
  • Im sorry for taking up so much of your time and I really appreciate you trying to help me, but I’m not sure I understand. My database is in the same instance as my shiny app so they have the same security group because they are one instance. When I ssh into it I can see my shiny app and I can see my database but the shiny app is not connecting to the database. The host inside my con() function is not correct even when it’s “localhost” which I thought logically should work. – Beeba May 05 '18 at 22:30
  • have you tried with `dplyr` and `src_postgres()`? – MLavoie May 05 '18 at 22:52
  • Just tried it and same deal. I can read from the database in R but not in app.R with the same error. I've added a picture to my question. – Beeba May 05 '18 at 23:09
  • Can you add a minimum reproducible example of your shiny app? And are you running your app on your instance or local computer? – MLavoie May 05 '18 at 23:20
  • sure I'll try. I've been running it locally throughout development but there was an old version of it on the instance that ran by reading from text files instead of a database. Once I was done developing the version that read from the database locally I've been trying to get it and my database on my instance. Database upload has been successful and the R code img I put up though is from my instance, not local. I've found a difference in file color between my local machine and the instance.. maybe it has something to do with permissions?? I'll add pictures – Beeba May 05 '18 at 23:31
  • I think I know what the problem is.. my user is wrong for the .lock file it should be ubuntu not postgres.. is it ok to delete these files? will they be regenerated when I try to run the app? – Beeba May 05 '18 at 23:44

1 Answers1

1

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

Beeba
  • 642
  • 1
  • 7
  • 18