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

[chris hermansen]

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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20200529/4ad9ea88/attachment.html>