Question

Permission denied for database postgres when creating schemas in dev database

Posted February 13, 2021 689 views
PostgreSQLDatabasesDigitalOcean App Platform

I was able to successfully connect to my app’s dev database, however when I try to create a schema with this script:

client.query(
  `CREATE SCHEMA IF NOT EXISTS ${team};`, (err, res) => {
  if (err) console.log(err.stack);
});

I get the following error:

digitalocean app error: permission denied for database db

I understand that the dev database doesn’t allow me to create databases, but nothing is said about creating schemas which is what I tried to do. It turns out that the default user db doesn’t have that privilege and it seems that there’s no way for me to change it.

What are the default user permissions? Should I be allowed to create PostgreSQL schemas in the dev database? This is what I get, and it looks like the user I have access to has no permissions whatsoever.

Role name Attributes Member of
_dodb Superuser, Replication {}
db {}
doadmin Create role, Create DB, Replication, Bypass RLS {db}
postgres Superuser, Create role, Create DB, Replication, Bypass RLS {}

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
Submit an Answer
4 answers

I am wondering the same thing. Did you ever get a response?

  • No, not yet. I suspect that it the only permission available is to create tables inside the schema available and that’s it, but I haven’t had the chance to test it yet, esp. because it wouldn’t serve my purpose anyway.

@kamaln7, would you mind chiming in?

  • Hi @mateust, sorry we missed this. Your suspicion is correct, Dev Databases have limited functionality and that includes not being able to create additional databases, or schemas it appears. Currently it is not possible to adjust these permissions, but I’ll mention this internally.

    /cc @felipecoury

I am also experiencing this issue, and it’s preventing me from moving forward with a project; I’d love to use hosted Postgres instead of maintaining my own, but I need multiple schema to do it.

Could anyone solve this?

@kamaln7 any news? I’m trying to develop a new API, but I cannot create a new schema on my dev database.

  • @ianwalls @sinanoezdemir @danieloliveiravas

    Hi - dev databases do not support creating schemas. Currently there are no plans for adding this functionality. Production managed databases offer full functionality however.

    • I upgraded now to a production managed database and have the same issue. Do you have any idea what I could have done wrong?

      Still: [42501] ERROR: permission denied for database *****
      I tried it with the a database i have created and the defaultdb one.

      • Hmm I wonder if your user didn’t get expanded permissions during the upgrade. Can you please open a support ticket so the team can look into this more closely? Also, try using the doadmin user or creating a new user from the DBaaS control panel—maybe that will have sufficient permissions.