[Qgis-user] Save projects to DB without creator's permissions

[Cliff Patterson]

Sorry for the confusion, by read-only I meant that the user only has the
ability to connect to the schema containing the layer and view it -- no
UPDATE, DELETE, etc. This is all set up in the DB and the "read-only" user
already has limited access to the layers. The problem is that if you forget
to uncheck "store password" in your connection parameters, or if you use
stored auth creds, and save a map to the DB, it saves with your
permissions. I agree, it is a security hole that could be fixed by simply
giving users the ability to strip the credentials on layers when saving to
the DB.

Cliff

On Fri, May 29, 2020 at 2:27 PM chris hermansen <clhermansen at gmail.com>
wrote:

> Cliff and list,
>
> On Fri, May 29, 2020 at 10:29 AM Cliff Patterson <cpatterson at psdrcs.com>
> wrote:
>
>> What is the best approach to save QGIS projects to PostgreSQL
>> without saving the project-creator's credentials/permissions? If the DB
>> admin creates a project and saves it to the DB, anyone opening that project
>> will attain the admin's permissions on layers in that map.
>>
>
> Sorry Cliff I don't have a solution, just some ideas presented below.  I
> have to say that this sounds like a huge security hole to me; I would
> expect that extra work would be required to allow others to read, and
> especially write, someone else's data.
>
>
>> To recreate:
>>
>> 1) Create a map containing PostGIS layers and save project to DB. All
>> layers should be editable by the admin. Admin is logged into DB with auth
>> config, not basic auth.
>>
>
> What are the access rights to the DB?  To the tables within the DB?
>
> 2) Create a new read-only user and new profile in QGIS and log in to DB.
>>
>
> A "read-only user"?  What is that? AFAIK the list of user restrictions in
> PostgreSQL is NOSUPERUSER, NOCREATEDB, NOCREATEROLE, NOINHERIT, NOLOGIN,
> NOREPLICATION, NOBYPASSRLS.  None of those mean "read-only".
>
> "log in to DB" implies that you have granted at least CONNECT on the DB to
> the new user.  That is, admin must have issued at least a GRANT CONNECT ON
> DATABASE db TO "new user".
>
> 3) Open the project and try to edit layers. Read-only user will be able to
>> see and edit all layers just like the DB Admin.
>>
>
> Leaving aside what a "read-only user" means, there are also the table
> privileges.  Presumably admin could limit the "new user"'s ability to alter
> tables in the database by ensuring that all change rights have been
> revoked, e.g. REVOKE INSERT, UPDATE, DELETE, TRUNCATE ON TABLE foo, bar,
> paz TO "new user".
>
> If some of the layers defined in the project are stored in the filesystem
> rather than as PostGIS/PostgreSQL tables then I presume one would need to
> change their accessibility as well.
>
>
>
> --
> Chris Hermansen · clhermansen "at" gmail "dot" com
>
> C'est ma façon de parler.
>


-- 

Cliff Patterson Ph.D.

*PSD* | Senior GIS Consultant
P: 519-690-2565 ext. 2616
www.psdrcs.com
London | 148 Fullarton St. 9th Floor
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20200529/2f17810b/attachment-0001.html>