Remaining steps for UA bety setup

[dlebauer]

@Chris-Schnaufer a few things to finish up the production BETYdb at UA:

• [x] Set the primary keys to 9 billion range
  • create a .env file where you launched pecan from and add BETY_LOCAL_SERVER=9 and then restart pecan, and initialize bety.
• [x] expose database to localhost for ease of access
  • From Rob "You can export the port, using docker override as posted in my gist and then connect to localhost:5432
  • I think he means to just use postgres bit in a separate yml and run:

    curl https://gist.githubusercontent.com/dlebauer/4f32034e5e1e455453c5b8060319b120/raw/ba05a6c401bd355a3cb02e121cc8b141396cc566/docker-compose.psqlports.yml
    docker-compose -p pecan -f docker-compose.psqlports.yml up

• [x] Then I should be able to get into psql like psql -d bety -U bety -h postgres and change my access levels in the database. (update users set access_level = 1, page_access_level=1 where login = ...???

[serbinsh]

And then we need to make sure that anything that needed to be done that wasn’t in the documentation makes it into the docs. Might be good to regularly try to build the dockerized PEcAn+BETY to make sure the instructions make sense. Perhaps we can try building some automated build environments?

S

[dlebauer]

For documentation:

• need to add record to machines table
  • .e.g https://www.betydb.org/machines/25
• need to set up dump.bety.sh cron job and dump_url
  • e.g. https://welsch.cyverse.org/pecan/dump/bety.tar.gz

[gsrohde]

Also for documentation: Fill in the remaining columns in the table at https://github.com/PecanProject/betydb-documentation/blob/bookdown/distributed_betydb.md.

[Chris-Schnaufer]

Where in the documentation would the '... machines table' and '... cron job ..' additions go? The References section appears to be a good place since it has the docker information, but none of the sub categories look right.

[dlebauer]

@Chris-Schnaufer this gets a bit confusing, but the documentation for how to sync instances of betydb is in the PEcAn documentation.

I'm not sure if this should be combined with or linked to from the BETYdb distributed_betydb.md documentation (@robkooper ?).

[dlebauer]

@robkooper do you have a recommended set of steps for making the database secure?

E.g.

• creating new psql users that have more restricted access than the default postgres and bety users?
• removing the default carya:Illinois user from the underlying system

[dlebauer]

Also customize web home pages

• create site-specific application.yml for BETYdb
• create local config.php for PEcAn from config.example.php

[gsrohde]

@dlebauer , @robkooper Regarding the carya:illinois user: This is created when the -u flag is used with load.bety.sh. To my understanding, this flag should never be used on production systems. It was intended for developers so that users of all combinations of permission levels would be created so that permission-related issues could more easily be tested.

[Chris-Schnaufer]

Thank you @gsrohde. Are you aware of procedures for handling the postgresql administrator accounts of 'postgres' and 'bety' as well?

[gsrohde]

@Chris-Schnaufer I'm not aware of any set procedures regarding PostgreSQL administrator accounts. But here are a few things to keep in mind: The PostgreSQL account that the BETYdb app uses (often called bety but sometimes called something else—bety_ebi, for example) needs to have Create DB permission, I think. The password for this account is usually listed in the config/database.yml configuration file, so if data security is a concern, access to this file should be restricted. (Alternatively, I think it should be possible to give password-less access to this PostgreSQL account to the machine account that Rails runs as.) Access to the postgres account should of course be tightly controlled as well.

[Chris-Schnaufer]

This is great information! Thanks

[robkooper]

Some notes of the top of my head:

• As @gsrohde said, for production instances don't create the list of extra users (-u flag in initialize).
• Change the default username/password.
• Make sure that the database only accessible from inside the docker containers
• Remove superuser from bety user and add as few options as possible (create database and crud)

[Chris-Schnaufer]

@gsrohde @robkooper @dlebauer Rob or Scott, it looks like the instance of postgres used in the Pecan installation allows all hosts from anywhere to connect as trusted users. It is the last line in the pg_hba.conf file is host all all all trust which allows this. One command that shows this is psql -U postgres -qAt -c "show hba_file" | xargs grep -v -E '^[[:space:]]*#'. This means that setting passwords on any database account is useless as that check is bypassed; their permissions are still in effect fortunately. Note that the postgres user is available on all postgresql instances as a superuser and in this case a password is irrelevant.

Is this something that would be desirable to change for all instances, not just in our case (at UA)?

In the same vein, he local access allows all users as well. Perhaps this can be restricted to only bety and postgres users?

More information on the pg_hbe.config file can be found on the Postgresql documentation site (for example, https://www.postgresql.org/docs/10/auth-pg-hba-conf.html)