50

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
  • 29,434
  • 47
  • 142
  • 186

5 Answers5

72

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

Håvard S
  • 23,244
  • 8
  • 61
  • 72
  • 2
    what if your local db user has no password? – mkirk Nov 15 '11 at 04:13
  • 10
    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
16

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,925
  • 4
  • 37
  • 31
  • "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
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
  • 121,420
  • 116
  • 450
  • 651
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
  • 795
  • 2
  • 12
  • 15
1

Heroku cli has changed the command.

heroku pg:push postgres://username:password@localhost/myrailsdb
Harry Moreno
  • 10,231
  • 7
  • 64
  • 116