1

I'm a rookie with postgres, and after I started the postgresql-11.service, I enter in 'su postgres', and try create my db, but appear this message:

[root@127 nemo]# systemctl enable postgresql-11.service
[root@127 nemo]# systemctl start postgresql-11.service
[root@127 nemo]# su postgres
bash-4.4$ createdb mydb
could not change directory to "/home/nemo": Permission denied
createdb: could not connect to database template1: FATAL:  no pg_hba.conf entry for host "[local]", user "postgres", database "template1", SSL off"

Can anyone help me?

  • Can you show the non commented lines of pg_hba.conf? – Daniel Vérité Feb 6 at 13:19
  • local test all 127.0.0.1/32 trust host test all 127.0.0.1/32 trust host test all ::1/128 trust – Nemo Feb 6 at 15:29
  • Your hba only allows you to connect to the database named "test". "createdb" by default tries to connect to "template1" or "postgres". Either change your hba, or use --maintenance-db=test. – jjanes Feb 6 at 15:57
  • --maintenance-db=test Command not found – Nemo Feb 6 at 16:01
  • It is an argument to createdb,not a command. – jjanes Feb 6 at 16:06
1

createdb needs to connect to a database to issue a CREATE DATABASE SQL statement, and by default it will use:

  • a Unix socket domain connection
  • the current username from the shell as the database user (so postgres in your case)
  • template1 as the database

When it does that, in your case you get this error message:

createdb: could not connect to database template1: FATAL: no pg_hba.conf entry for host "[local]", user "postgres", database "template1", SSL off"

It indicates that postgres as a database user does not have the permission to connect through a Unix domain socket (that's what [local] means).

This is unusual, since in general, postgres installers on Unix tend to install a pg_hba.conf starting with this rule:

local   all       postgres       peer

Meaning that the postgres Unix user has the right to connect locally to any database as the postgres database user, which suits the needs of administrating databases, including creating new ones.

In fact your pg_hba.conf does not look like a default one. The simplest way to proceed to immediately solve the problem of database creation would be to edit it, add the above line as the first rule, and reload the postgres service.

0

su postgres leaves you in a weird state where your are currently in a directory which you have no access to be in.

You probably want to do su - postgres, which will leave you in postgres's home directory.

  • This is the answer that I received: "[root@127 nemo]# su - postgres [postgres@127 ~]$ createdb mydb createdb: não pôde conectar ao banco de dados template1: FATAL: no pg_hba.conf entry for host "[local]", user "postgres", database "template1", SSL off [postgres@127 ~]$ " Just to understand, same message, but in Portuguese.. – Nemo Feb 6 at 15:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.