I have created some postgres databases and I want given users to have access only to them (and no others) from any network. For that purpose, in the pg_hba.conf configuration file, under the IPv4 local connections header, I have created the following entries:
host all postgres 0.0.0.0/0 password
host all cjgb 0.0.0.0/0 password
These are the entries that provide unrestricted access to myself (impersonated by either the postgres or the cjgb user) as administrator of the database to connect to "all" databases from "0.0.0.0/0", this is, any host, provided I use the right password.
Access for the other users is restricted as follows:
host varios varios 0.0.0.0/0 password
host postgres varios 0.0.0.0/0 password
host riesgo_desviacion riesgo_desviacion 0.0.0.0/0 password
host postgres riesgo_desviacion 0.0.0.0/0 password
Users varios and riesgo_desviacion can only access tables varios and riesgo_desviacion (respectively) from any network if they provide the right password. I also had to grant them access to the postgres database (for reasons I still have to investigate, but that I am afraid stem from the fact that I mistakenly used the postgres database as template to create the new ones).
Have you managed to figure out why you had to give privileges to postgres for varios user?
ReplyDeleteI tried creating the new database with template1 as template, but still had to give access to postgres db in pg_hba.conf.
thx
tomislav