Createuser could not connect to database postgres [Fix]

Here’s a question from one of our regular reader Sam. He says that PostgreSQL does not allow to create user or database and fails with an error message “Createuser could not connect to database postgres” while issuing createuser command and “createdb: could not connect to database template1” while executing createdb command.  The command fails when executed as privileged user as well. Here’s the solution for this error.

Before we see the solution, have a look at the error message:

[sam@openca ]$ createdb openca
createdb: could not connect to database template1: FATAL: role "sam" does not exist

[sam@openca ]$ su -
[root@ra openca ]# createuser openca
createuser: could not connect to database postgres: FATAL: role "root" does not exist

How to fix the error – createuser could not connect to database postgres

According to the snapshot, createuser and createdb commands were executed as ‘sam’ and ‘root’ user. It means, the PostgreSQL administrator has not created a PostgreSQL user account for ‘sam’ & ‘root’. Note, the PostgreSQL user accounts are different from the regular UNIX user accounts. So, even if you have valid UNIX accounts, it’s not a valid PostgreSQL user account until administrator creates one. That’s the reason, PostgreSQL denied users ‘sam’ & ‘root’ from creating user or database. In order to fix this error, you need to switch to a user that’s running PostgreSQL server. By default, UNIX user 'postgres' is the one that will be running PostgreSQL server. So the createdb & createuser commands should be executed as 'postgres' user account as shown below.

$ sudo su - postgres

(or)

$ sudo -u postgres -i

You can now create database as ‘postgres’ user as shown below:

$ createdb openca

Create PostgreSQL user account as below:

$ createuser sam

Verify, if PostgreSQL user is created properly by logging-in to ‘sam’ user and type psql as shown below.

# sudo su - sam

[sam@openca ]$ psql
psql (9.2.23)
Type "help" for help.

postgres=# \q

That’s it!

How to allow users to create PostgreSQL database?

By default, PostgreSQL does not allow users to create or drop database. If you ever want to allow users to create PostgreSQL database, then the administrator has to provide necessary privileges.

To do that, switch to the user that is running PostgreSQL server.

$ sudo su - postgres

Type psql to enter PostgreSQL prompt.

$ psql
psql (9.2.23)
Type "help" for help.

postgres=#

Grant CREATEDB privilege using ALTER statement as shown below:

postgres=# ALTER USER sam CREATEDB;
ALTER ROLE

Test if user ‘sam’ has enough privileges to create a database.

[sam@openca ]$ createdb test

[sam@openca ]$ psql -d test
psql (9.2.23)
Type "help" for help.

test=>

In case, if you don’t have privilege to create database in PostgreSQL, then you will see an error as shown below:

# createdb
createdb: could not connect to database template1: FATAL: role "sam" does not exist