PSQL Connect Issues

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?

PSQL Connect Issues

Apr 4 '20 Comments: 1 Answers: 2

0

Ever since I installed pgAdmin 4, I've been getting issues with Rails projects. Just like this one:

FATAL:  permission denied for database "postgres"
DETAIL:  User does not have CONNECT privilege.
Couldn't create 'db_name' database. Please check your configuration.
rails aborted!
ActiveRecord::NoDatabaseError: FATAL:  permission denied for database "postgres"
DETAIL:  User does

…

Open Full Question

Comments

[andre aliaman]

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]

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]

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]

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.

[Vicente G. Reyes]

I think my psql's f*cked up. Would uninstalling & reinstalling it fix the issue?

[andre aliaman]

Maybe. That's why my next suggestion is to try connect first with db client like pgadmin, etc from your laptop. To see if your db(postgre) actually works or not.

After that it's 90% permission issue. Something(Permission) need you give to the user.

[Vicente G. Reyes]

Thanks Andre