Can't create schema for a db with PostgreSQL

All we need is an easy explanation of the problem, so here it is.

Use psql -U postgres to login.

postgres=> CREATE DATABASE mydb;
postgres=> \c mydb;

You are now connected to database "mydb" as user "postgres".

When create schema, got

mydb=> CREATE SCHEMA main;
ERROR:  permission denied for database mydb

What will be the possible denied permission that didn’t set?

Edit

When I run \l, it showed

   Name    |        Owner         | Encoding |   Collate   |    Ctype    |               Access privileges
-----------+----------------------+----------+-------------+-------------+-----------------------------------------------
 mydb      | mydb_administrator   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/mydb_administrator                     +
           |                      |          |             |             | mydb_administrator=CTc/mydb_administrator
 postgres  | postgres             | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 ...

\du got

      Role name       |                         Attributes                         |                          Member of
----------------------+------------------------------------------------------------+-------------------------------------------------------------
 mydb_administrator   |                                                            | {rds_iam}
 mydb_application     | Cannot login                                               | {}
 mydb_user            |                                                            | {rds_iam,mydb_application}
 postgres             | Create role, Create DB                                    +| {rds_superuser}
                      | Password valid until infinity                              |
 ...

All the things were executed by postgres user.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

If you create the schema as user mydb_administrator, it will work.

But you messed up your installation by removing the SUPERUSER property from postgres. If that was not deliberate and you have no other superuser, you will have to start PostgreSQL in single-user mode to repair that. See the many existing anwers for details about that.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply