47

I had asked an earlier question which did not get any replies.

Basically I get an error invalid database url when I try to do heroku db:push.

I figured I can try explicitly providing the database url.

I tried:

heroku db:push postgres://postgres@localhost/myrailsdb

But that gave error:

Failed to connect to database:
  Sequel::DatabaseConnectionError -> PGError fe_sendauth: no password supplied

What is the format for providing username and password?

Community
  • 1
  • 1
Omnipresent
  • 28,384
  • 47
  • 137
  • 186

5 Answers5

68

Try heroku db:push postgres://username:password@localhost/myrailsdb.

Håvard S
  • 22,173
  • 6
  • 59
  • 70
  • 1
    what if your local db user has no password? – mkirk Nov 15 '11 at 04:13
  • 8
    Just drop the password, i.e. `db push postgres://username@localhost/myrailsdb` – Håvard S Nov 15 '11 at 21:31
  • 2
    If no password is supplied, this will give an error of Failed to connect to database: Sequel::DatabaseConnectionError -> PGError fe_sendauth: no password supplied – user181677 Feb 03 '12 at 02:32
  • You can also add query parameters at the end to specify additional attributes - e.g. `?pool=5`. For Ruby on Rails specifically any attributes specified in `DATABASE_URL` will override attributes specified in `config/database.yml`. See: https://edgeguides.rubyonrails.org/configuring.html#configuring-a-database – user2490003 Feb 01 '19 at 18:27
14

Here's how to do it in a Ruby script:

# Connect to database.
uri = URI.parse(ENV['DATABASE_URL'])
postgres = PG.connect(uri.hostname, uri.port, nil, nil, uri.path[1..-1], uri.user, uri.password)

# List all tables.
tables = postgres.exec('SELECT * FROM pg_catalog.pg_tables')
tables.num_tuples.times do |i|
  p tables[i]
end
ma11hew28
  • 113,928
  • 113
  • 437
  • 631
14

according to documentation

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

examples

postgresql://
postgresql://localhost
postgresql://localhost:5432
postgresql://localhost/mydb
postgresql://user@localhost
postgresql://user:secret@localhost
postgresql://other@localhost/otherdb?connect_timeout=10&application_name=myapp
postgresql://localhost/mydb?user=other&password=secret
Toumi
  • 2,586
  • 3
  • 32
  • 29
  • "postgresql://localhost/mydb?user=other&password=secret" does not seem to use the specified password. Only "postgresql://user:secret@localhost" works. – gucki Jul 17 '20 at 10:00
5

Edit your postgresql configuration (pg_hba.conf) file and change 'host' type method to 'trust'. But be aware that this is not secure.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 md5

Restart your postgresql server and re-run the command

$ heroku db:push postgres://postgres@localhost/myrailsdb

Here is the reference to my answer: https://stackoverflow.com/a/7667344/181677

Community
  • 1
  • 1
user181677
  • 785
  • 2
  • 11
  • 15
1

Heroku cli has changed the command.

heroku pg:push postgres://username:password@localhost/myrailsdb
Harry Moreno
  • 9,093
  • 6
  • 57
  • 101