You will add an access line for shop and apply it without stopping the server.
A narrow line opens exactly what is needed: its own database, its own role, a single address and a password login. The crm line is your model. The trust method admits anyone who matches the line without a password, so it does not belong on a server with data. To edit the file: sudo vim /etc/postgresql/16/main/pg_hba.conf.
A new line changes nothing by itself: the server reads pg_hba.conf at startup and when told to reload its configuration, sudo systemctl reload postgresql. A reload applies the rules to new connections and leaves open ones alone. restart stops the server, and every application's connections drop.
Let shop into its database
Make the shop role log in to the shop database from 127.0.0.1 with the password from ~/.pgpass, and to no other database. Do not restart the server: other applications are connected to it.
Goals · 0 of 3
- Let shop into the shop database with its password not done yet
- Keep shop out of other databases not done yet
- Apply the rules without restarting the server not done yet
A typo in pg_hba.conf does not show up right away. systemctl reload ends quietly with code 0, while the server writes pg_hba.conf was not reloaded to its log /var/log/postgresql/postgresql-16-main.log and keeps the old rules — all of them, without your new line. After a restart with such a typo the server does not start at all. The pg_hba_file_rules view shows the file's errors:
An empty answer means no errors. Otherwise you get the line number and what is wrong with it.
You added a line for shop and ran sudo systemctl reload postgresql, which exited with 0. psql still says no pg_hba.conf entry. Where do you start?
-
It never got to the password:
no pg_hba.conf entryis the rule stage. A new password will not change the server's answer. -
With an error in pg_hba.conf the server will not start at all after a restart, and every application is refused. Find the error first:
pg_hba_file_rules, theerrorcolumn. -
trustadmits any role into any database without a password, and in a file with an error the server would not apply it either: it keeps the old rules.
A reload with an error in the file ends silently, while the server logs pg_hba.conf was not reloaded and keeps the old rules. pg_hba_file_rules shows the line number and the error, such as invalid IP mask for an address without /32.