[Qgis-user] sharing project with pg user and ssl connection

[Greg Troxel]

Namik SCHERZL - SLA via QGIS-User <qgis-user at lists.osgeo.org> writes:

> In my structure we are currently migrate our GIS architecture from
> ESRI to Qgis & Postgres.  We would like to know the most recommended
> method for authenticating PG via Qgis knowing that :

Stepping back, the issue here is that your requirements are not at all
defined.  I've reordered your message.

> * Securing the data flow.

"Securing the data flow" doesn't really say anything.  My own view is
that there is little justification for transmitting (nonpublic) data
across computers, unless protected by ssh, TLS, or similar.  CPUs are
fast, and the cost of encryption/decryption and authentication is surely
small compared to the processing of that data.  If you see it
differently, please run tests and post a description of them and
results.
 
In particular, if you are controlling access to a resource with a
password, I do not see it has responsible to transmit that password in
cleartext connections even on your LAN that you think is ok, but surely
is not reliably ok, in that proving that no device is compromised
(including thermostats) is nearly impossible.  Thus, I advise you to
forget the concept of non-TLS cross-machine database access.

> * We want to avoid transmitting login / password information between
> users of the same project.

I can't understand this.

I think you are blurring granting database access to particular people,
and granting database access to QGIS projects.  Standard security
practice is concerned about people (or some machine notion of principal,
also), and QGIS projects are data files.  You further should be
controlling access to the QGIS project in its own right, and embedding
credentials in projects and then relying on project control sounds
unreasonably risky.

Once you decide to control access by people, you can either create a
database account for each authorized user and assign them separate
passwords, or you can create a group account with one username/password
and give that to all the authorized people.  I think you said that you
don't want shared credentials.

If you have obligations to anyone else about the data -- non-disclosure
agreements, information subject to GDPR, a requirement that data only be
modified by authorized persons, etc., then I don't see shared
credentials as responsible.  (Yes, I know doing it right is harder; that
doesn't change the analysis of what is ok.)

> * we are keeping a few Arcgis licences, and they should have access to the DB

The licenses, or people who are using Arggis?  Surely arcgis can access
postgis and the user can have a username/password.

> I've seen that Qgis can store PG login and password in it's own local
> storage and in the case we define a common authcfg ID for all our
> users it's possible for several users to share the same project
> without transferring any information/ rights.

I'm having trouble following.  If you mean that qgis can separate the
config of "where is the datbase" keeping that in the project, while
using other means (OS credentials storage) to store "for this databsae,
use this username and that password", that makes sense.

But the first thing to be clear on is whether you are using individual
username/password, and if you have a security or integrity requirement
to avoid group credentials.