0

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.

2
  • 2
    There is something weird. First, postgres does not seem to be a superuser (unless you changed the prompt). What do you get for \du? Then it seems like either the database belongs to someone else (can't be if you created it), or the permissions are hosed. What do you get for \l? Commented May 18, 2021 at 10:48
  • @LaurenzAlbe Thank you. I edited the question and added the results of \l and \du.
    – Miantian
    Commented May 19, 2021 at 0:29

1 Answer 1

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.

1
  • You are right. Thank you. I changed the db's owner to postgres by postgres user, it works.
    – Miantian
    Commented May 19, 2021 at 6:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.