Hi Giuseppe,
Thanks for the response. I have provided the GRANTS and other PostgreSQL setup scripts below as it includes what you have suggested:
ROLE
---------
CREATE ROLE <role_name> WITH NOLOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
USER
----------
CREATE USER <username> WITH PASSWORD '<password>'
REVOKES
----------------
REVOKE ALL PRIVILEGES ON DATABASE <database_name> FROM PUBLIC;
REVOKE ALL PRIVILEGES ON DATABASE postgres FROM PUBLIC;
GRANTS
-------------
GRANT CONNECT ON DATABASE <database_name> TO <role_name>;
GRANT USAGE ON SCHEMA <schema_name> TO <role_name>;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <role_name>;
GRANT <role_name> TO <username>;
I cannot but wonder why these privileges are working when tested in pgAdmin/pgsql, but not in QGIS with the same user/schema/table/database.