Introduction

First, set up access rules into the database server. PostgreSQL allows you to control access based upon the host that is trying to connect, using the pg_hba.conf file. You can specify SSL connections if needed or skip that if the network is secure. You can specify the use of SCRAM authentication using 256 bit keys, as well as many other mechanisms.

Next, set up the role and privileges for accessing your data. Databases are mostly used to store data, with several restrictions on how it can be used. Some records or tables can only be seen by certain users, and even those tables that are visible to everyone can have restrictions in terms of who can insert new data or change the existing data. All of this is managed by a privilege system, where users are granted different privileges for different tables or other database objects, such as schemas or functions.

It is good practice not to grant these privileges directly to users, but to use an intermediate role to collect a set of privileges...