Discussion on: PSQL Connect Issues

[Vicente G. Reyes]

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]

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]

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.

[andre aliaman]

Hmm.. I see..
You still have option.
How about altering as superuser?

ALTER USER SUPERUSER;

[Vicente G. Reyes]

I did that.

ALTER USER highcenoid;

highcenoid is my superuser.

Are there any other options?

[andre aliaman]

can you check all your user permission? use this command

\du

post it here.

[Vicente G. Reyes]

Here it is

                                List of roles

Role name	Attributes	Member of
highcenoid	Superuser, Create role, Create DB	{}
icvn	Superuser, Create role, Create DB	{}
postgres	Superuser, Create role, Create DB, Replication, Bypass RLS	{}
saleor		{}
webmaster

[andre aliaman]

hahaha.. such strange error!

Hmmm.. if you running other command like rails db:create and rails db:migrate. is that running well too?

and can you copy your database.yml also to here? (just delete the password part or it's not possible, just confirm all the value is right)

[Vicente G. Reyes]

I know!!!

rails db:migrate actually does it's job and I think rails db:create too. It's just the rails db:setup that messes up our brains.

Wait, so I need to include my user password on the database.yml file?

default: &default
  adapter: postgresql
  encoding: unicode
  # For details on connection pooling, see Rails configuration guide
  # https://guides.rubyonrails.org/configuring.html#database-pooling
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

development:
  <<: *default
  database: db_name

  # The specified database role being used to connect to postgres.
  # To create additional roles in postgres see `$ createuser --help`.
  # When left blank, postgres will use the default role. This is
  # the same name as the operating system user that initialized the database.
  #username: user_name

  # The password associated with the postgres role (username).
  #password:

  # Connect on a TCP socket. Omitted by default since the client uses a
  # domain socket that doesn't need configuration. Windows does not have
  # domain sockets, so uncomment these lines.
  #host: localhost

  # The TCP port the server listens on. Defaults to 5432.
  # If your server runs on a different port number, change accordingly.
  #port: 5432

  # Schema search path. The server defaults to $user,public
  #schema_search_path: myapp,sharedapp,public

  # Minimum log levels, in increasing order:
  #   debug5, debug4, debug3, debug2, debug1,
  #   log, notice, warning, error, fatal, and panic
  # Defaults to warning.
  #min_messages: notice

[andre aliaman]

Hmmm..
Check it here:
digitalocean.com/community/tutoria...

At those post, They give an example minimal database.yml that can be running for rail apps:

...
default: &default
adapter: postgresql
encoding: unicode
# For details on connection pooling, see Rails configuration guide
# guides.rubyonrails.org/configuring...
pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
username: sammy
password: <%= ENV['APPNAME_DATABASE_PASSWORD'] %>

development:
<<: *default
database: appname_development
...

[Vicente G. Reyes]

I haven't tried to fix the database.yml but I think this will fix it. Thanks Andre! Will let you know if the problem would still be persistent.