How to connect to postgresql using url

Question

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?

Piano

Answer 1

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

Jolee

what if your local db user has no password? – Sashasashay

Just drop the password, i.e. db push postgres://username@localhost/myrailsdb – Barter

If no password is supplied, this will give an error of Failed to connect to database: Sequel::DatabaseConnectionError -> PGError fe_sendauth: no password supplied – Godmother

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: edgeguides.rubyonrails.org/… – Couplet

Answer 2

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

Monteith

"postgresql://localhost/mydb?user=other&password=secret" does not seem to use the specified password. Only "postgresql://user:secret@localhost" works. – Jellyfish

Answer 3

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

Pendent

What are your imports/requires? – Chromolithograph

Answer 4

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://mcmap.net/q/355455/-heroku-db-pull-failing-due-to-lack-of-password-when-i-haven-39-t-specified-a-password

Godmother

where is the pg_hba.conf file located? – Honniball

actually, found it. for ubuntu, Debian/Ubuntu: /etc/postgresql/9.0/main/pg_hba.conf – Honniball

what to do if I have the error about pg_hba.conf on remote server (heroku)? I can't change anything there and changing local file does not make any difference, ofc – Gena

Answer 5

Heroku cli has changed the command.

heroku pg:push postgres://username:password@localhost/myrailsdb

Joettejoey