Question

How to create tables with App Platform Dev databases?

Posted on August 14, 2024
Development Databases Database Dev/Management App Platform
Asked by Matthew Narrell

I have a custom container, connecting to a dev PostgreSQL database in App Platform. I connect with the ${db.DATABASE_URL} connection string with the provided user/pass.

When my migrations attempt to create tables, I’m presented with “permission denied (SQLSTATE 42501)”.

How might I escalate the supplied user’s privileges, connect as the doadmin user, or otherwise allow my application to run its migrations on a dev database, in App Platform?

Submit an answer

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

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.

Bobby Iliev

Site Moderator

• August 16, 2024

Accepted Answer

Hi there,

There should be no need to escalate the user privileges in order to create tables.

I have a demo Laravel project that uses a Dev Postgres database and here is how I supply the credentials:

https://github.com/thedevdojo/wave/blob/main/.do/deploy.template.yaml#L34-L53

    # DATABASE
    - key: DB_CONNECTION
      scope: RUN_TIME
      value: "pgsql"
    - key: DB_HOST
      scope: RUN_TIME
      value: ${dev-db.HOSTNAME}
    - key: DB_PORT
      scope: RUN_TIME
      value: ${dev-db.PORT}
    - key: DB_DATABASE
      scope: RUN_TIME
      value: ${dev-db.DATABASE}
    - key: DB_USERNAME
      scope: RUN_TIME
      value: ${dev-db.USERNAME}
      type: SECRET
    - key: DB_PASSWORD
      scope: RUN_TIME
      value: ${dev-db.PASSWORD}
      type: SECRET

After that the database migrations work as expected.

Can you share how exactly are you utilizing the ${db.DATABASE_URL} connection string in your code?

The DATABASE_URL env var, already has the username and the password in there, so there should be no need to specify them again, eg.:

postgres://${db.USERNAME}:${db.PASSWORD}@${db.HOSTNAME}:${db.PORT}/${db.DATABASE}

UPDATE: The issue was that the dev database permissions will not allow you to create databases or schemas; instead, you will need to use the default database and the public schema.

You will get permissions denied if you were to try and create a new schema, eg:

dev-db=> create schema test;
ERROR:  permission denied for database dev-db

-- But creating tables will work as expected:

dev-db=> create table delete_me_just_testing (id int);
CREATE TABLE