83 lines
2.5 KiB
Plaintext
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
|