Osahon Oduware wrote:
> I created a "Read-only" User in PostgreSQL via a Role with "SELECT" ONLY privilege on all
> tables in a schema as shown below:
>
> GRANT SELECT ON ALL TABLES IN SCHEMA [schema_name] TO [role_name]
> GRANT [role_name] TO [user_name]
>
> Next, I test this by trying to UPDATE a column in a table (same schema as above) with
> pgAdmin/psql and this works fine by giving a response that the user has no permission -
> 'ERROR: permission denied for relation <table_name>.'
>
> Next, I connect with the same user in QGIS and add a layer from the same table (same
> schema as above). I open the attribute table for the layer, turn on editing mode (by
> clicking on the pencil-like icon), and edit the same field/column above. To my surprise,
> the edit was saved successfully without any permission error prompt.
>
> Next, I check the value of the field/column (same table/schema as above) in pgAdmin/psql
> and it is having the new (edited) value from QGIS. This is rather strange as it seems QGIS
> is bypassing the permissions set for the same user in the PostgreSQL/PostGIS database.
The most likely explanation is that the QGIS application connects with
A database user different from [user_name].
Set "log_statement = 'all'" in postgresql.conf, reload and the database
modifications will be logged. With an appropriate setting for "log_line_prefix"
you will be able to see which database user performs the operation.
Yours,
Laurenz Albe