meter-reading/README

83 lines
2.5 KiB
Plaintext

* About
This is an overengineered utility I wrote for myself to record
utility meter readings. It uses postgres for the database and perl
for the UI. There should be a screen shot accompanying this to show
what it looks like in use.
Most of the following is about setting up postgres. The perl part
is in a script that you run.
Comments and questions welcome at <barnold@tilde.club>.
* One-off steps for the postgres cluster.
** What these steps are for
These are independent of the meter readings database (mrdb) and
precede its creation. They create database roles and authentication,
which exist at the cluster level and don't depend on the mrdb.
Want the mrdb owned by a postgres user other than my personal role.
Want the ability to connect to mrdb in my personal role and use mrdb
to save readings, etc.
Want to avoid routinely connecting to mrdb as its owner. I'm less
likely to inadvertently drop a table if I lack privilege and have to
go out of my way to connect as the owner. The owner should grant me
only the accesses I need for routine use.
The following sets up for that, for a local postgres only, not over
the network.
** Create the postgres user "mr_owner"
Create a db user to own the tables.
# su - postgres
$ createuser --connection-limit=2 --createdb --echo mr_owner
** Give "barnold" connect access as the "mr_owner" user.
To allow myself to connect as mr_owner, change some postgres
config in /etc/postgresql/12/main/.
pg_ident.conf: add two 'barnoldmap' lines (both are required) so that the
end of the file reads as follows.
# MAPNAME SYSTEM-USERNAME PG-USERNAME
barnoldmap barnold barnold
barnoldmap barnold mr_owner
pg_hba.conf: modify the second "local" line, the one following the
"postgres" line, so that it has a map option on the end. After
removing some of the whitespace, it reads:
local all all peer map=barnoldmap
** The mr_user role
Create it with
# su - postgres
postgres@argon:~$ createuser --connection-limit=4 --no-login mr_user
and add myself to it with
postgres@argon:~$ psql
postgres=# grant mr_user to barnold;
GRANT ROLE
* Repeatable steps for the meter reading database
These are done in the Makefile. Can do them as many times as wanted,
they don't perturb the cluster-level things described above.
* Dependencies
make
postgresql-12+
libdbd-pg-perl
libterm-readline-gnu-perl
libterm-extendedcolor-perl
libtry-tiny-perl