PostgreSQL Schema Creation – Permission Issues

permissionspostgresqlroleschemausers

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.

Best Answer

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.

Related Question