Trying to read app_tables with psql-anvil-app-server

Anvil Q&A
#1

What I’m trying to do:
I am trying to read app_tables in the bundled postresql database while running the open source anvil-app-server on my own vm. I am following the information here:

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

(env) :~$ psql-anvil-app-server
Looking for postmaster.opts in .anvil-data/ …
(Use ‘psql-anvil-app-server ’ to look somewhere else.)
psql ‘host=localhost port=45787 user=postgres dbname=postgres options=–search-path=app_tables’
psql (12.13 (Ubuntu 12.13-0ubuntu0.20.04.1), server 10.18)
Type “help” for help.
postgres=# \dn
List of schemas
Name | Owner
+++++++++++++++++
app_tables | postgres
data_tables | postgres
public | postgres
(3 rows)

postgres=# \dt public.
List of relations
Schema | Name | Type | Owner
++++++++++++++++++++++++++++++++++
public | anvil_config | table | postgres
public | app_storage_access | table | postgres
public | app_storage_data | table | postgres
public | app_storage_media | table | postgres
public | app_storage_tables | table | postgres
public | background_tasks | table | postgres
public | db_version | table | postgres
public | runtime_sessions | table | postgres
public | scheduled_tasks | table | postgres
(9 rows)
postgres=# \dt
Did not find any relations.
postgres=# \dt app_tables.
Did not find any relation named “app_tables.”.
postgres=# \dt “app_tables”.*
Did not find any relation named ““app_tables”.*”.
postgres=#

As seen above, when I connect, there is nothing seen in app_tables or data_tables. I can see tables in public.

I have gotten the same result while connecting from vanilla psql as well

#2

Putting this here as a hint for anyone else who might be looking for a solution using the open source app server to self-host.

Partial solution:
use: psql-anvil-app-server

If you do the following:
GRANT SELECT ON ALL TABLES IN SCHEMA app_tables TO postgres;

You’ll then be able to do things like:
SELECT COUNT(*) FROM your_table;

In short: It seems that by default, the postgres user does not have access to see the tables in app_tables (even though they are the owner???)

Note: If there is a mismatch in the schema vs the source code, it seems to not be able to find things.
For example: There was a mismatch in one of our tables, and I could not select from it (said it wasn’t a table or relation). When the mismatch was fixed, I could select from it.