0

i finished developing a little app, and try to put it into production. I copied the app to a new folder, created a new psql database with a new user, adjusted my database.yml file and try to run rake db:schema:load to get my database in the right state. Rake is able to connect to my db, since the first steps are executed, but after those, it aborts with the following message. As far as google tells me, it should be an issue with the user. However, since rake can execute the first steps, i doubt that i have an issue with it.

[root@l1vmgt08 web_qip_parser_v2]# rake db:schema:load    
-- enable_extension("plpgsql")
   -> 0.0171s
-- create_table("qip_changes", {:force=>:cascade})
   -> 0.0919s
-- create_table("users", {:force=>:cascade})
   -> 0.0989s
-- add_foreign_key("qip_changes", "users")
   -> 0.0042s
-- enable_extension("plpgsql")
rake aborted!
ActiveRecord::NoDatabaseError: FATAL:  role "root" does not exist

config/database.yml:

default: &default
  adapter: postgresql
  encoding: unicode
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

development:
  <<: *default
  database: my_db_name
  username: my_user_name
  password: my_password
  host: localhost
  port: 5432

test:
  <<: *default
  database: my_db_name

production:
  <<: *default
  database: my_db_name
  username: my_user_name
  password: my_password
  host: localhost
  port: 5432

Psql output of my Dev and the new Prod user for privilege comparison:

postgres=# \l
                                                 List of databases
          Name          |       Owner       | Encoding |   Collate   |    Ctype    |       Access privileges        
------------------------+-------------------+----------+-------------+-------------+--------------------------------
my_dev_user_name        | postgres          | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres                  +
                        |                   |          |             |             | postgres=CTc/postgres         +
                        |                   |          |             |             | my_dev_db_name=CTc/postgres
 my_user_name           | postgres          | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres                  +
                        |                   |          |             |             | postgres=CTc/postgres         +
                        |                   |          |             |             | my_db_name=CTc/postgres

so it seems to me that iam trying to connect via UNIX socket, using the local user. I found some articles describing this issue. This should be fixed when i connect via TCP/IP, by simple adding "host: localhost" to my database.yml file. However, thats exactly what i have done, so i have no idea how to proceed

here my pg_hba.conf:

 # TYPE  DATABASE        USER            ADDRESS                 METHOD
 local   all             all                                     peer
 host    all         all         127.0.0.1/32          md5

there is another application running on the server, which works. It has the same database.yml config set as i do.

Bastian
  • 177
  • 2
  • 12

1 Answers1

0

Might have happened due to absence of username statement for test environment. Please see this question

Dende
  • 545
  • 6
  • 19
  • i just added user/password for test. Now i get "Peer authentication failed for user "my_db_user"". I tested the password with "psql -d my_db_name -U my_user_name -h localhost" and it is working. – Bastian Feb 21 '19 at 10:10
  • "Peer authentication" means that it's using a unix socket and expecting the connecting unix user to have the same unix username as the postgresql username. Please see the accepted answer here: https://stackoverflow.com/questions/15306770/pg-peer-authentication-failed – Dende Feb 21 '19 at 11:03
  • thats what i have seen before. Since iam working on a shared server, i need to check the postgres config. However tcp/ip connections are usually working, i dont get it why it is not working this time.. – Bastian Feb 21 '19 at 11:20
  • Because your local unix username is `root`, and you try to connect as `my_user_name` and they should be the same – Dende Feb 21 '19 at 11:27
  • there is another app running on the server, which also does use tcp/ip to connect to the database, and its database.yml looks like mine. I also found, that "host: localhost" is enough to tell rails to connect via tcp/ip. So i dont get, why it still opens it via unix socket. furthermore, due to the other app, iam not able to change the postgres config. i have added the config in my initial post, and it looks good from what i have seen in the documentation – Bastian Feb 21 '19 at 11:38
  • Try to switch to `md5` authorization: https://www.postgresql.org/docs/current/auth-password.html - this allows authorization via password from your `database.yml`. Peer authentication works by obtaining the client's operating system user name from the kernel and using it as the allowed database user name – Dende Feb 21 '19 at 11:39
  • thats what is already configured. output of pg_hba.conf in my initial post. I dont think the issue is that i have misconfigured postgres, but that rails still tries to connect via socket, when it should connect via tcp/ip. i dont find anything that explains that – Bastian Feb 21 '19 at 12:45
  • this confirms that i just need to add "host: localhost" to use tcp/ip, however i still connect via socket/peer.. https://stackoverflow.com/questions/15306770/pg-peer-authentication-failed/28215732 – Bastian Feb 21 '19 at 12:53
  • Editing`pg_hba.conf` to use `md5` password authentication instead of peer authentication for unix sockets (`local` connection type) so Pg accepts password authentication would definitely solve your problem, but it won't tell you why it is still using `local` connection type – Dende Feb 21 '19 at 12:59
  • i have created a new thread to check why iam not able to open the tcp/ip connection. thanks for your help so far. https://stackoverflow.com/questions/54808473/rails-doesnt-connect-to-postgres-via-tcp-ip-but-unix-socket – Bastian Feb 21 '19 at 13:42
  • One more possible solution: check your .env file, it should contain `POSTGRES_USER=my_db_user` – Dende Feb 21 '19 at 16:11