Using pgweb with anvil-app-server

Anvil Q&A
1

What I’m trying to do:

Configure pgweb to provide an interface for data tables used in an anvil-app-server installation, using the built-in postgres database that comes with anvil-app-server.

What I’ve tried and what’s not working:

I have an Anvil app running in anvil-app-server on Ubuntu 20.04, using the built-in postgres database that ships with anvil-app-server. I can run psql-anvil-app-server successfully, and retrieve the port of the postgres server (and the username is the default ‘postgres’). The anvil-app-server docs say that the password for the default postgres database is stored in <data-dir>/postgres.password. I can read and edit that file with nano, but the default password (created by anvil) is encrypted. Is there a default password, or a way to retrieve the encrypted password?

If that’s not possible, I can edit the password in psql with:

ALTER USER user_name WITH PASSWORD ‘new_password’; (or \password postgres)

but that breaks the Anvil install. Is there a way to change the Anvil configuration to use the new password created in psql, for the built-in postgres database?

I’d like to be able to access data with GUI tools, using the built-in postgres database (without having to set up a separate postgres install).

Thank you!

2

I just use separate docker containers for the app server and the db. I wrote amoni basically to automate that.

3

Thank you for the suggestion Owen :slight_smile: I’m not currently using Docker, but installing and connecting to a separate postgres server is not a problem. I’d prefer to have the option to get into the built-in DB. I was hoping someone would know how the default password is generated, before I go digging into source code.

4

I found this in anvil-runtime/server/app-server/src/anvil/app_server/postgres.clj:

(let [password (random/base32 32)
      pg (.start pg-config)]

And the stored password appears to be in base64. Perhaps it’s not encrypted - I’ll have to look more at this when I get a few. If anyone has done this already I’d really appreciate any time saving suggestions!

(Wow, looking through Clojure code brings back memories - I studied Lisp in college - eek, more than 30 years ago :face_with_spiral_eyes:)

5

I think it may be preferred to change the Anvil configuration to use the new password created in psql (\password postgres), but I don’t see documented anywhere where the Anvil database password is stored for the embedded database (the docs say: * Password: stored in <data-dir>/postgres.password, but changing that text file to match \password postgres in psql breaks the app).

… as I write this, I think it may just be necessary to change \password postgres in psql (as plain text) and store that value as base64 in the postgres.password file.