Got "permission denied for public schema" error in database but can't get the connection string in a provisioned database inside an app

Question

I used a yaml file to create an app that runs my app and has a PostgreSQL database. However, during the deployment I got the error “permission denied for public schema”. I did some research and found this [page](How do I fix a “permission denied for schema public” error in PostgreSQL? | DigitalOcean Documentation ) that suggests connecting to the database using psql to change the user privileges. But When I tried to get the connection parameters in the app panel it says "Connection details are unavailable while the database is being provisioned."What could I do now? I have no way to connect to the database to change the privileges to allow my app to connect to it. I did not see any way to set the privileges in the [app spec reference page](Reference for App Specification | DigitalOcean Documentation ) eitherimage alt text

Bobby · Accepted Answer

Hi there, It sounds like you’re using a dev database in the App Platform, which has restricted permissions. Specifically, you can’t create additional databases, you must use the default one that comes with the cluster. This is likely why you’re seeing the “permission denied for public schema” error. To resolve this, make sure your app is connecting to the default database and not trying to create a new one. Unfortunately, dev databases don’t support modifying privileges, so if you need more control over roles and permissions, you might want to switch to a DigitalOcean Managed Database instead. More details here:🔗 https://docs.digitalocean.com/products/app-platform/details/limits/#database-limits Let me know how it goes! - Bobby

jkarneges · Answer

I got a similar error when using Django and running “./manage.py migrate” on a fresh app, and I fixed it by recreating the dev database. For one app, this worked after recreating the DB once. For another, I had to recreate the DB twice. Upgrading to a managed database did not fix it. It seems like some part of the database initialization process doesn’t work all the time, but if you try enough times it’ll eventually work.

alexdo · Answer

Hi there, Bobby’s dev database explanation is worth double checking against jkarneges’s experience though, since they reported hitting the same error on a full managed database too, and recreating the database, sometimes more than once, was what actually fixed it for them, not the upgrade itself. That points to something more specific than a dev database limitation, and there is a well documented reason this error has become far more common recently regardless of database tier. Since PostgreSQL 15, the default privileges on the public schema changed. Previously any connected user could create objects in the public schema, but PostgreSQL 15 revokes CREATE on public schema from everyone except the database owner by default. If your app’s database user isn’t the owner, which is the normal setup when App Platform provisions a database for you, migrations or ORM setup steps that try to create tables in the public schema will hit exactly this permission denied error, not because anything is misconfigured, but because the default itself changed. This is likely also why recreating the database sometimes worked for jkarneges, if the fresh initialization happened to land with different ownership. Once you can connect, the fix is a straightforward grant from whichever role does own the schema: GRANT CREATE ON SCHEMA public TO your_app_user; For the immediate blocker of not being able to get connection details while it says “being provisioned,” that’s usually just a timing issue on the panel’s side rather than the database actually being unready, worth waiting a few minutes and refreshing rather than assuming something failed. If it’s still stuck after that, pulling the connection details through the API or doctl instead of the control panel is worth trying, the panel UI and the underlying API don’t always update in lockstep. Regards

Become a contributor for community

DigitalOcean Documentation

Resources for startups and AI-native businesses

The developer cloud

Start building today

Report this

Got "permission denied for public schema" error in database but can't get the connection string in a provisioned database inside an app

Related questions

Database does not respond and cannot be restored. What can I do?

When will OpenSearch v3 be available on DigitalOcean Managed Databases?

When can we expect Postgres 15.14 Managed Database support to be available?

Knowledgebases indexing 0 tokens (or far less than the data provided)

How to determine how memory and CPU is needed for knowledge bases

How to restrict connections to a managed database

Citing Knowledgebase sources when using a large .csv file as a source instead of many individual .md files

Multiple issues with Knowledgebases and Agents

How to update roles/permissions for Postgres "Dev" Database in App Platform

Problems accessing a managed database. Also cannot contact support

How do I specify the managed database tier in the app.yaml file when using doctl for deployment?

Database Bandwidth Billing

Report this

Got "permission denied for public schema" error in database but can't get the connection string in a provisioned database inside an app

Related questions

Database does not respond and cannot be restored. What can I do?

When will OpenSearch v3 be available on DigitalOcean Managed Databases?

When can we expect Postgres 15.14 Managed Database support to be available?

Knowledgebases indexing 0 tokens (or far less than the data provided)

How to determine how memory and CPU is needed for knowledge bases

How to restrict connections to a managed database

Citing Knowledgebase sources when using a large .csv file as a source instead of many individual .md files

Multiple issues with Knowledgebases and Agents

How to update roles/permissions for Postgres "Dev" Database in App Platform

Problems accessing a managed database. Also cannot contact support

How do I specify the managed database tier in the app.yaml file when using doctl for deployment?

Database Bandwidth Billing