Can't create tables in Flexible PostgreSQL, even with CTc privileges

Question

Hi there,

I'm not able to create tables in a PostgreSQL database I created myself, even though I've given the user all privileges to the database ("CTc").

Here are the privileges (notice the "metabase" role/user):

metabase=> \l
                                                                   List of databases
       Name        |     Owner      | Encoding | Locale Provider |  Collate   |   Ctype    | ICU Locale | ICU Rules |         Access privileges
-------------------+----------------+----------+-----------------+------------+------------+------------+-----------+-----------------------------------
 azure_maintenance | azuresu        | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           |
 azure_sys         | azuresu        | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           |
 metabase          | postgres       | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =Tc/postgres                     +
                   |                |          |                 |            |            |            |           | postgres=CTc/postgres            +
                   |                |          |                 |            |            |            |           | metabase=CTc/postgres

And here's the error I get:

metabase-7b976b47cc-wvfn4 metabase Caused by: liquibase.exception.DatabaseException: ERROR: permission denied for schema public
metabase-7b976b47cc-wvfn4 metabase   Position: 14 [Failed SQL: (0) CREATE TABLE public.databasechangelog (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED TIMESTAMP WITHOUT TIME ZONE NOT NULL, ORDEREXECUTED INTEGER NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONTEXTS VARCHAR(255), LABELS VARCHAR(255), DEPLOYMENT_ID VARCHAR(10))]

Any ideas what might be going on? Is there something that impedes a role with all privileges from creating tables?

Thanks!

Answer 1

Hi @Diogo Baeder,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

As I understand it, the issue is related to permissions on the public schema, even though the metabase role has all privileges on the database.

Starting with PostgreSQL 15, the default permissions for the public schema have changed. By default, only the database owner can create objects in the public schema. This change was made to enhance security and prevent unauthorized users from creating objects in the public schema.

To resolve this issue, you need to explicitly grant the necessary permissions to the metabase role for the public schema. You can do this by running the following SQL commands:

GRANT USAGE ON SCHEMA public TO metabase;
GRANT CREATE ON SCHEMA public TO metabase;

These commands will grant the metabase role the ability to use and create objects in the public schema.

For more information, please refer the documents:

https://www.cybertec-postgresql.com/en/error-permission-denied-schema-public/

https://www.postgresql.org/docs/15/sql-createuser.html

https://stackoverflow.com/questions/79216617/liquibase-exception-databaseexception-error-permission-denied-for-schema-publi

Hope this helps. Do let us know if you have any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.