Search code examples
postgresqlovh

Can't create new schema in an OVH postgres

I have a PostgreSQL server on OVH's Cloud DB and have been using its databases for my web apps. So far so good. I got a project where It's a requirement to have schemas. Strangely enough, I am unable to create schemas on the user with "Administrator" privileges. I have prepared scripts using schemas, so I just need to run them on a prepared database but I need a database with schemas to run them.

Here is my process:

  1. Create a new database
  2. Select option "Create user"
  3. Select option for privilages: "Administrator"
  4. Commit configuration
  5. Wait for database creation
  6. Connect to database with the new config via PGAdmin
  7. Run command create schema if not exists "vMobile";
  8. Recieve following error:
ERROR:  permission denied for database my-database-dev
SQL state: 42501

I created a ticket for this but the wait is taking too long.

Solution

  • Support answer

    Ok, so I got a response from the OVH support and there is no option for the user to create new schemas as their CloudDB enables access only to schema public and mentioned privileges Administrator, Read/Write, Read, None are only applicable to the public schema.

    Workaround

    My solution to this is to create tables with schema name included in their names like so:

    • Desired outcome: "vCommon"."Route"
    • Workaround: "public"."vCommon_Route"