3

If a create this table:

CREATE TABLE streetmap.ways (
    pk bigserial not null,
    name character varying(150),
    geom geometry(LineString,32628),
    rowdate timestamp with time zone NOT NULL DEFAULT now(),
    username character varying(50) NOT NULL DEFAULT "current_user"(),
    CONSTRAINT ways_pkey PRIMARY KEY (pk)) WITH (
    OIDS = FALSE ) TABLESPACE pg_default;

And assign all permissions (GRANT ALL ON TABLE streetmap.ways TO qgis_editor) to qgis_editor user, everything works fine with QGIS 3.

But if instead I set:

GRANT SELECT(pk, rowdate, username) ON streetmap.ways TO qgis_editor;
GRANT ALL(name, geom) ON streetmap.ways TO qgis_editor;

Or:

GRANT SELECT ON TABLE streetmap.ways TO qgis_editor;
GRANT ALL(name, geom) ON streetmap.ways TO qgis_editor;

QGIS 3 can´t edit the table. Any suggestions?

  • 1
    So you're asking why, if you fail to grant write access to a table, a user can't write to it? Isn't that self-evident? – Vince Mar 29 '18 at 23:52
  • 1
    Hi @Vince if you set columns permissions as I did, you can execute "insert into streetmap.ways(name, geom) values ('Main St', wkt_geom)". You can try it using pgAdmin. So I didn´t fail granting permissions. It seems that QGIS use only table permissions. – Jorge Rosales Mar 30 '18 at 9:02
  • 1
    Maybe @user30184 but I think that supporting this uncommon use case avoid unwanted changes in a better way than marking controls as "non-editable". – Jorge Rosales Mar 30 '18 at 9:23
  • 1
    You've failed to grant access to the thing for which QGIS (or any other product, for that matter) is likely to ask -- After "List the tables to which I have SELECT access" comes, "Do I have INSERT/UPDATE/DELETE access on this table?" And the (correct) answer is "No". You'd need to make significant changes to the edit model to capture some columns being more editable than others (and implement that for half a dozen databases) – Vince Mar 30 '18 at 12:28
  • 1
    @Vince I think the short and easy answer is QGIS doesn´t support PostgreSQL column permissions. – Jorge Rosales Mar 30 '18 at 22:50
4

When QGIS issues a query, you'd have to know what it's sending. Consider enabling verbose logging on the server so you can see it.

My guess is that QGIS tries to update every column in the table. For instance,

CREATE TABLE foo ( id int, a int, b int );

Then an update to a where id=1 would be like this,

UPDATE foo SET a=?, b=? WHERE id = 1;

Even if you don't have b= in the update query, QGIS, and most applications will set it to whatever they think it is, like it's previous value. This is normally a good thing, as any update to an MVCC database requires rewriting the entire row.

In your case, it's not a good thing.

That's going to be a huge problem.

I doubt QGIS cares to implement column-level permissions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.