Rails - connecting to postgres with url, role does not exist

Question

I'm attempting to deploy a Rails 5 app using Capistrano, and I'm hung up on the db:migrate step.

Specifically, when my scripts attempt to run db:migrate I get the error:

ActiveRecord::NoDatabaseError: FATAL: role "admin" does not exist

My database.yml file looks like this for the production block:

production:
  adapter: postgresql
  encoding: unicode
  # For details on connection pooling, see rails configuration guide
  # http://guides.rubyonrails.org/configuring.html#database-pooling
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  url: <%= ENV['DATABASE_URL'] %>

and my database_url takes the form of:

postgresql:://mydb_user:mydbuser_pass@localhost/db_name

With mydb_user, mydbuser_pass, and db_name substituted in with my own correct values.

Inside of postgres I can see these permissions on my database:

 db_name | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres + postgres=CTc/postgres + mydb_user=CTc/postgres

Which I think means that mydb_user has the permissions necessary for using my database.

Now, the user I'm logged in as during the capistrano deploy does have the username of 'admin', which leads me to believe that I fundamentally don't understand something about postgres roles. Why would my currently logged in user have anything to do with the postgres connection when I've given my username and password via the database url for a specific postgres user I've already created? Is this saying that my ubuntu username needs to match mydb_user in order to login? How do I fix this error specific to rails and database urls with postgres?

Have you run a db:create, db:create:all or db:setup? The NoDatabaseError says that you have not created your required database. stackoverflow.com/questions/25611004/… also has some options. I haven't used Capistrano, so I don't know how that interplays, however. — Richard_G
– Richard_G, Commented Dec 31, 2016 at 2:17
Running db:create results in a similar error. PG::ConnectionBad: FATAL: role "admin" does not exist — Msencenb
– Msencenb, Commented Dec 31, 2016 at 6:24
Have you tried to create admin? In postgres, \du will show the users/roles. (Users and roles are now the same thing.) Then, create role admin with Superuser Createrole CreateDB Replication BypassRLS; Well, that's what I used. — Richard_G
– Richard_G, Commented Dec 31, 2016 at 14:57

dostapn · Accepted Answer · 2020-12-20 16:57:27Z

2

prev commenter was wrong: correct format of database url is

postgresql://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]

for example:

postgresql://db_user:db_password@localhost:5432/db_name?timeout=5000&connect_timeout=5&pool=10

answered Dec 20, 2020 at 16:57

dostapn

714 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tallboy Over a year ago

He's not wrong in some cases, for example in postgres on heroku the prefix that heroku generates is postgres://

Msencenb · Accepted Answer · 2016-12-31 20:12:18Z

0

Classic case of dumb mistakes. The urls do not have two colons in them like I describe above, instead they are

postgres://

By default rails was attempting to connect as 'admin' because that's the unix user I was logged in as. Solution, double check your postgres url format.

answered Dec 31, 2016 at 20:12

Msencenb

5,10411 gold badges56 silver badges87 bronze badges

Collectives™ on Stack Overflow

Rails - connecting to postgres with url, role does not exist

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related