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,
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
createuser commands should be executed as
'postgres' user account as shown below.
$ sudo su - postgres
$ 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
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
psql to enter PostgreSQL prompt.
$ psql psql (9.2.23) Type "help" for help. postgres=#
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