Create PostgreSQL user for QGIS project

Question

I have some data in a PostgreSQL database, I use pgAdmin4 for the organisation of the data. I want to create a QGIS project for other users with the data from my database. The users of the project should see the data, load own data to the project, but they should not be able to change my database files.

Therefore I create a user qgis in pgAdmin with this SQL statemant

CREATE ROLE "qgis" with
LOGIN
NOSUPERUSER
NOCREATEDB
NOCREATEROLE
NOINHERIT
NOREPLICATION
CONNECTION LIMIT -1
PASSWORD 'xxxx';

Aftre the creation of the user I give him the privilege SELECT for the different tables in my schema with the statement

GRANT SELECT ON TABLE example_1 TO qgis;

But with this privilege the user can't load the data in QGIS. So I give the user the privilege USAGE. Then the user can load the data in QGIS, but he can also delete data in the database and add new file. How can I create a user, that can connect to my database, read the data, but nothing more?

• JGH: PGadmin is just a tool to access the DB. There is no such thing as a PGAdmin user

Answer 1

By default, the public role (which is assigned to everybody in the DB) allows creating objects in the public schema. You can revoke this.

It is indeed a good practice to remove every rights from the public role, and to assign the desired privileges explicitly.

REVOKE ALL ON DATABASE myDB FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA public FROM PUBLIC;

The next step is to give the select privilege on the desired object(s), as you did.

Note that an extra level of protection is to use a different schema than public. Keep PostGIS in public and put your data in another schema, over which you have full (and clear!) control

• Bera: JGH can you recommend any litterature / website etc. which covers database basics / good practices. Im assign to set up a postresql+postgis database without really knowing what im doing :)
• JGH: @BERA this post is quite interesting but covers just part of the good practices. Sorry I don't have complete good ref at hand