Postgres permission denied

Question

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?

Answer 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.

Answer 2

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.