DEV Community

PSQL Connect Issues

Vicente G. Reyes on April 04, 2020

I've been having an issue with this for a few weeks now but hasn't focued on this since rails isn't my focus. Can someone help me? ...

Read full post

andre aliaman • Apr 4 '20

I answer your question at there.

This is the good url for learn about your problem: stackoverflow.com/questions/391692...

Btw, my suggestion, you can try to connect to database first from postgresql client. to make sure your permission is already right

Vicente G. Reyes • Apr 4 '20

Hey Andre thanks for your time. I actually saw this earlier and did the commands but rails db:setup threw the error on the SO question. But when I ran rails db:migrate it generated a shema.rb on the db folder with data on it even if I haven't written any schemas.

andre aliaman • Apr 4 '20

help.heroku.com/63D7ALXT/why-am-i-...

Try to read from there, this is the answer:

We typically see CONNECT privilege issues where applications try to create databases as part >of db:setup, as the user provided does not have permission to create or drop databases.

For Rails apps you should look to use db:schema:load, db:structure:load or db:migrate instead >of db:setup.

Vicente G. Reyes • Apr 4 '20

I actually did rails db:migrate but the schema looked like it came from one of my old Django projects



ActiveRecord::Schema.define(version: 2019_08_08_110256) do

  # These are extensions that must be enabled in order to support this database
  enable_extension "plpgsql"

  create_table "auth_group", id: :serial, force: :cascade do |t|
    t.string "name", limit: 150, null: false
    t.index ["name"], name: "auth_group_name_a6ea08ec_like", opclass: :varchar_pattern_ops
    t.index ["name"], name: "auth_group_name_key", unique: true
  end

  create_table "auth_group_permissions", id: :serial, force: :cascade do |t|
    t.integer "group_id", null: false
    t.integer "permission_id", null: false
    t.index ["group_id", "permission_id"], name: "auth_group_permissions_group_id_permission_id_0cd325b0_uniq", unique: true
    t.index ["group_id"], name: "auth_group_permissions_group_id_b120cbf9"
    t.index ["permission_id"], name: "auth_group_permissions_permission_id_84c5c92e"
  end

  create_table "auth_permission", id: :serial, force: :cascade do |t|
    t.string "name", limit: 255, null: false
    t.integer "content_type_id", null: false
    t.string "codename", limit: 100, null: false
    t.index ["content_type_id", "codename"], name: "auth_permission_content_type_id_codename_01ab375a_uniq", unique: true
    t.index ["content_type_id"], name: "auth_permission_content_type_id_2f476e4b"
  end

  create_table "auth_user", id: :serial, force: :cascade do |t|
    t.string "password", limit: 128, null: false
    t.datetime "last_login"
    t.boolean "is_superuser", null: false
    t.string "username", limit: 150, null: false
    t.string "first_name", limit: 30, null: false
    t.string "last_name", limit: 150, null: false
    t.string "email", limit: 254, null: false
    t.boolean "is_staff", null: false
    t.boolean "is_active", null: false
    t.datetime "date_joined", null: false
    t.index ["username"], name: "auth_user_username_6821ab7c_like", opclass: :varchar_pattern_ops
    t.index ["username"], name: "auth_user_username_key", unique: true
  end

  create_table "auth_user_groups", id: :serial, force: :cascade do |t|
    t.integer "user_id", null: false
    t.integer "group_id", null: false
    t.index ["group_id"], name: "auth_user_groups_group_id_97559544"
    t.index ["user_id", "group_id"], name: "auth_user_groups_user_id_group_id_94350c0c_uniq", unique: true
    t.index ["user_id"], name: "auth_user_groups_user_id_6a12ed8b"
  end

  create_table "auth_user_user_permissions", id: :serial, force: :cascade do |t|
    t.integer "user_id", null: false
    t.integer "permission_id", null: false
    t.index ["permission_id"], name: "auth_user_user_permissions_permission_id_1fbb5f2c"
    t.index ["user_id", "permission_id"], name: "auth_user_user_permissions_user_id_permission_id_14a6b632_uniq", unique: true
    t.index ["user_id"], name: "auth_user_user_permissions_user_id_a95ead1b"
  end

  create_table "blog_about", id: :serial, force: :cascade do |t|
    t.text "content", null: false
  end

  create_table "blog_code", id: :serial, force: :cascade do |t|
    t.text "content", null: false
  end

  create_table "blog_faq", id: :serial, force: :cascade do |t|
    t.text "content", null: false
  end

  create_table "blog_post", id: :serial, force: :cascade do |t|
    t.string "title", limit: 100, null: false
    t.text "content", null: false
    t.datetime "date_posted", null: false
    t.integer "author_id", null: false
    t.index ["author_id"], name: "blog_post_author_id_dd7a8485"
  end

  create_table "blog_privacy", id: :serial, force: :cascade do |t|
    t.text "content", null: false
  end

  create_table "blog_terms", id: :serial, force: :cascade do |t|
    t.text "content", null: false
  end

  create_table "django_admin_log", id: :serial, force: :cascade do |t|
    t.datetime "action_time", null: false
    t.text "object_id"
    t.string "object_repr", limit: 200, null: false
    t.integer "action_flag", limit: 2, null: false
    t.text "change_message", null: false
    t.integer "content_type_id"
    t.integer "user_id", null: false
    t.index ["content_type_id"], name: "django_admin_log_content_type_id_c4bce8eb"
    t.index ["user_id"], name: "django_admin_log_user_id_c564eba6"
  end

  create_table "django_content_type", id: :serial, force: :cascade do |t|
    t.string "app_label", limit: 100, null: false
    t.string "model", limit: 100, null: false
    t.index ["app_label", "model"], name: "django_content_type_app_label_model_76bd3d3b_uniq", unique: true
  end

  create_table "django_migrations", id: :serial, force: :cascade do |t|
    t.string "app", limit: 255, null: false
    t.string "name", limit: 255, null: false
    t.datetime "applied", null: false
  end

  create_table "django_session", primary_key: "session_key", id: :string, limit: 40, force: :cascade do |t|
    t.text "session_data", null: false
    t.datetime "expire_date", null: false
    t.index ["expire_date"], name: "django_session_expire_date_a5c62663"
    t.index ["session_key"], name: "django_session_session_key_c0390e0f_like", opclass: :varchar_pattern_ops
  end

  create_table "items", force: :cascade do |t|
    t.string "item"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

  create_table "users", force: :cascade do |t|
    t.string "email", default: "", null: false
    t.string "encrypted_password", default: "", null: false
    t.string "reset_password_token"
    t.datetime "reset_password_sent_at"
    t.datetime "remember_created_at"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["email"], name: "index_users_on_email", unique: true
    t.index ["reset_password_token"], name: "index_users_on_reset_password_token", unique: true
  end

  create_table "users_profile", id: :serial, force: :cascade do |t|
    t.string "image", limit: 100, null: false
    t.integer "user_id", null: false
    t.index ["user_id"], name: "users_profile_user_id_key", unique: true
  end

  add_foreign_key "auth_group_permissions", "auth_group", column: "group_id", name: "auth_group_permissions_group_id_b120cbf9_fk_auth_group_id"
  add_foreign_key "auth_group_permissions", "auth_permission", column: "permission_id", name: "auth_group_permissio_permission_id_84c5c92e_fk_auth_perm"
  add_foreign_key "auth_permission", "django_content_type", column: "content_type_id", name: "auth_permission_content_type_id_2f476e4b_fk_django_co"
  add_foreign_key "auth_user_groups", "auth_group", column: "group_id", name: "auth_user_groups_group_id_97559544_fk_auth_group_id"
  add_foreign_key "auth_user_groups", "auth_user", column: "user_id", name: "auth_user_groups_user_id_6a12ed8b_fk_auth_user_id"
  add_foreign_key "auth_user_user_permissions", "auth_permission", column: "permission_id", name: "auth_user_user_permi_permission_id_1fbb5f2c_fk_auth_perm"
  add_foreign_key "auth_user_user_permissions", "auth_user", column: "user_id", name: "auth_user_user_permissions_user_id_a95ead1b_fk_auth_user_id"
  add_foreign_key "blog_post", "auth_user", column: "author_id", name: "blog_post_author_id_dd7a8485_fk_auth_user_id"
  add_foreign_key "django_admin_log", "auth_user", column: "user_id", name: "django_admin_log_user_id_c564eba6_fk_auth_user_id"
  add_foreign_key "django_admin_log", "django_content_type", column: "content_type_id", name: "django_admin_log_content_type_id_c4bce8eb_fk_django_co"
  add_foreign_key "users_profile", "auth_user", column: "user_id", name: "users_profile_user_id_2112e78d_fk_auth_user_id"
end

I don't know what happened.

andre aliaman • Apr 4 '20 • Edited

hmmm.. I think you still misunderstood the answer above.

So I think the problem you have, lies in rails db:setup because of those commands needs permission to create/drop the database which is it needs more authorization than just privileges for doing anything in one database.

all this time, you just give all privileges to one database like you mention it on your question at SO.

GRANT ALL PRIVILEGES ON DATABASE db_name TO user_name;
GRANT CONNECT ON DATABASE db_name TO user_name;

Those command just give authorization to user_name for have all_privileges and connect to one database which is db_name.

But, rails db:setup need permission beyond that. those command need to create/drop database(like db_name not just permission to access) which still not provide from the command you give it to the user_name.

So my suggestion to you,
You can add permission to create database also to your DB user(user_name).

ALTER USER CREATEDB; or
ALTER USER SUPERUSER; should be help.
Refer to this doc: postgresql.org/docs/current/sql-al...

Or try to find other command from rails like db:schema:load, db:structure:load, db:migrate which can fulfill your needs without give more permission to your user.

Vicente G. Reyes • Apr 4 '20

Thanks for that detailed reply. I did this:

highcenoid=# ALTER USER highcenoid CREATEDB;
ALTER ROLE
highcenoid=# GRANT ALL PRIVILEGES ON DATABASE postgres to highcenoid;
GRANT
highcenoid=# ALTER DATABASE postgres owner to highcenoid;
ALTER DATABASE
highcenoid=# GRANT CONNECT ON DATABASE postgres to highcenoid;
GRANT
highcenoid=# \q

Then it spit out

~/Dev/RoR/project   master ?  rails db:setup  ✔
FATAL: permission denied for database "postgres"
DETAIL: User does not have CONNECT privilege.
Couldn't create 'dca9qlbmn4rek1' database. Please check your configuration.
rails aborted!
ActiveRecord::NoDatabaseError: FATAL: permission denied for database "postgres"
DETAIL: User does not have CONNECT privilege.

I don't know what to do anymore.