Add config to expose postgres db

[friedger]

Maybe it should be exposed by default or there should be a settings in the config

[wileyj]

The suggestion here being to expose port 5432 to the host network, and not just the docker network correct?

I thought about doing that at first, but I reverted to only accessible to the docker network since in most cases i don't think people are changing the DB password.

However, I think we can add some kind of flag in the .env to expose it that would allow users not to footgun themselves, while still giving people who want direct access to the DB data.

[friedger]

I am happy if there is a config that I can use. Indeed it would expose the port to the host network.

I want this for my home node and read its DB.

[wileyj]

https://github.com/stacks-network/stacks-blockchain-docker/commit/a03a6bfce0fab3ceb4b99de6f3f6b7df3e0fd7c1

change this to true https://github.com/stacks-network/stacks-blockchain-docker/blob/master/sample.env#L3 and restart using the above commit and postgres will be available on the host OS:

CONTAINER ID   IMAGE                                     COMMAND                  CREATED         STATUS         PORTS                                  NAMES
42b90c4d4229   blockstack/stacks-blockchain:2.05.0.3.0   "/bin/stacks-node st…"   5 seconds ago   Up 3 seconds   0.0.0.0:20443-20444->20443-20444/tcp   stacks-blockchain
a381a0eb727d   hirosystems/stacks-blockchain-api:5.0.0   "docker-entrypoint.s…"   5 seconds ago   Up 4 seconds   0.0.0.0:3999->3999/tcp                 stacks-blockchain-api
10ee0f0a2922   nginx:alpine                              "/docker-entrypoint.…"   5 seconds ago   Up 3 seconds   0.0.0.0:80->80/tcp                     nginx
6b4e590db1ca   postgres:14-alpine                        "docker-entrypoint.s…"   5 seconds ago   Up 4 seconds   0.0.0.0:5432->5432/tcp                 postgres

➭ psql -h localhost -p 5432 -U postgres postgres
Password for user postgres: 
psql (14.5)
Type "help" for help.

postgres=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
(1 row)

postgres=# \dt
                  List of relations
 Schema |          Name           | Type  |  Owner   
--------+-------------------------+-------+----------
 public | blocks                  | table | postgres
 public | burnchain_rewards       | table | postgres
 public | config_state            | table | postgres
 public | contract_logs           | table | postgres
 public | event_observer_requests | table | postgres
 public | faucet_requests         | table | postgres
 public | ft_events               | table | postgres
 public | ft_metadata             | table | postgres
 public | mempool_txs             | table | postgres
 public | microblocks             | table | postgres
 public | miner_rewards           | table | postgres
 public | names                   | table | postgres
 public | namespaces              | table | postgres
 public | nft_events              | table | postgres
 public | nft_metadata            | table | postgres
 public | pgmigrations            | table | postgres
 public | principal_stx_txs       | table | postgres
 public | reward_slot_holders     | table | postgres
 public | smart_contracts         | table | postgres
 public | stx_events              | table | postgres
 public | stx_lock_events         | table | postgres
 public | subdomains              | table | postgres
 public | token_metadata_queue    | table | postgres
 public | token_offering_locked   | table | postgres
 public | txs                     | table | postgres
 public | zonefiles               | table | postgres
(26 rows)
postgres=# select index_block_hash,block_hash from blocks limit 1;
                          index_block_hash                          |                             block_hash                             
--------------------------------------------------------------------+--------------------------------------------------------------------
 \x918697ef63f9d8bdf844c3312b299e72a231cde542f3173f7755bb8c1cdaf3a7 | \x6b2c809627f2fd19991d8eb6ae034cb4cce1e1fc714aa77351506b5af1f8248e
(1 row)

[friedger]

Thank you