0

I was following a tutorial to use PostgreSQL with a server application (both are running on the same computer). The tutorial was using SQL commands, but I used pgAdmin. I added a new user 'gitea' with "Can login?" checked and a password. I created a database 'giteadb' with the owner as 'gitea'. In the later step the tutorial says:

Allow the database user to access the database created above by adding the following authentication rule to pg_hba.conf.

For local database:

local    giteadb    gitea    scram-sha-256

Restart PostgreSQL to apply new authentication rules

If I set "Can login" and a password for the user, why do I have to add the user to the pg_hba.conf? Editing a conf file hidden somewhere in the system and restarting a service is pain in the lower back. Is the document correct that this process of adding the new user to the conf and restarting the DB is mandatory?

3 Answers 3

0

The document you followed is not wrong but can be improved. After editing the pg_hba.conf the database does not need to be restarted but it just needs to reload the configuration for this.

pg_ctl reload will do this.

You do need to edit pg_hba.conf to allow access. You can use wildcards so you don’t need to do this for every user. Since you will be using local connections you might consider using os authenticated accounts. Those accounts will use sockets. To do that use peer authentication. https://www.postgresql.org/docs/13/auth-peer.html This gives users that exist in the local OS and in the database, with the same name access without the need for an extra password to maintain. Remember, passwords can also be leaked …..

0

All the pg_hba that i have seen provided by default had 'all' lines, which match all users not already covered by earlier lines. Unless your mysterious tutorial had you remove those lines first, adding lines back in one by one would not be necessary. you also don't need to restart, a reload will work. You can also add lines for groups, with membership defined inside the database, using @

0

No , Its not required to add each users/host every time for new users or hosts. You can just specify the below:

host     all             all             0.0.0.0/0               md5

After specifying the above one on the pg_hba.conf file just have a reload of the system using below command by login into the postgres using psql:

postgres=> SELECT pg_reload_conf();

It's super easy.

Note: If you want to allow some specific IP/host then replace the 0.0.0.0 with the IP required to be whitelisted.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.