How to Solve Error Message ERROR: permission denied to create database in PostgreSQL Database

Introduction

This article’s focus is mainly on how to solve an error message. The error message appear when executing the command for creating an new database. So, the following is the appearance of the output of creating a database :

1. First of all, the most important part is accessing the PostgreSQL database server. In this context, accessing the PostgreSQL database is possible by using a command in the command line. The aim is to login to the PostgreSQL command console :

   [root@hostname ~]# psql -Uadmin 
   Password for user admin:
   psql (11.10)
   Type "help" for help.
   
   
   postgres=>
   

2. Create a new database in the PostgreSQL command console by executing the following query :

   postgres=> create database mydb;
   ERROR:  permission denied to create database
   postgres=> \q
   

   Sadly, but it is true, the command for creating a new database does not work as usual. So, where does it go wrong ?. So, there must be a solution to solve the problem above. As the error message is indicating a permission issue, there might be a connection with the role of the user executing the command.

Solution

The solution is quite simple. Since there is a slight connection with the permission issue, below are steps to solve the problem :

1. First of all, just check the permission of the user from the PostgreSQL command console by accessing the PostgreSQL command console as follows :

   [root@hostname ~]# psql -Uadmin postgres
   Password for user admin:
   psql (11.10)
   Type "help" for help.
   
   postgres=#
   

2. After that, type the follwoing command to change the permission of the user. Just execute the command to add a role for creating database. The role name is ‘createdb’. The query pattern for altering the user’s permission exist as follows :

   postgres=# \du
                                       List of roles
     Role name  |                         Attributes                         | Member of
   -------------+------------------------------------------------------------+-----------
    admin       |                                                            | {}
    centos      | Cannot login                                               | {}
    postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
    dbadmin     | Superuser                                                  | {}
   
   
   postgres=#
   

3. So, as in the above output, it is very clear that the user ‘admin’ does not have any role attributes at all. So, add a new role attribute to the user. Login first as postgres or as a superuser account by typing the following command :

   [root@hostname ~]#psql -Upostgres
   Password for user postgres:
   psql (11.10)
   Type "help" for help.
   
   postgres=#
   

4. Soon after, execute the command to add the createdb role as follows :

   postgres=# alter user admin createdb;
   ALTER ROLE
   postgres=# \q
   

5. Next, connect to the PostgreSQL command console once more using the first user. In this context, it is the user with the name of ‘admin’ as follows :

   [root@hostname ~]# psql -Uadmin postgres
   psql (11.10)
   Type "help" for help.
   
   postgres=>
   

6. Finally, create the database by executing the following query :

   postgres=> create database mydb;
   CREATE DATABASE
   postgres=> \q
   
   

7. Last but not least, list the database to check whether the new created database exist or not by typing the following query :

   postgres=> \l
                                                      List of databases
       Name     |  Owner   | Encoding |          Collate           |           Ctype            |    Access privileges
   -------------+----------+----------+----------------------------+----------------------------+--------------------------
    mydb        | admin    | UTF8     | English_United States.1252 | English_United States.1252 | 
    postgres    | postgres | UTF8     | English_United States.1252 | English_United States.1252 |
    simpeg      | postgres | UTF8     | English_United States.1252 | English_United States.1252 |
    template0   | postgres | UTF8     | English_United States.1252 | English_United States.1252 | =c/postgres             +
                |          |          |                            |                            | postgres=CTc/postgres
    template1   | postgres | UTF8     | English_United States.1252 | English_United States.1252 | =c/postgres             +
                |          |          |                            |                            | postgres=CTc/postgres
   (9 rows)
   
   
   postgres=>
   

At last, the new created database exist with the name of ‘mydb’.