Problem loading PostGIS (Postgres) View layer in QGIS

Question

Why is Postgres user XX via command „Add PostGIS Layers“ in QGIS enabled upload only View tables which owns? Or how to achieve it to the user XX via PostGIS can load View table which owns the user YY?

Main table from the database with different users are assigned by rolls, however the View tables we could not find that there is a possibility of assigning rolls.

Any idea how solve this problem?

you need to be postgres root (superuser) to view all tables. Is a security feature of postgres. Use the GRANT feature - http://www.postgresql.org/docs/9.0/static/sql-grant.html — Mapperz, Apr 17 '13 at 13:32

score 3 · Answer 1 · edited Apr 13 '17 at 12:34

3

The QGIS table browser has a well-hidden option that you need to toggle to tell it what column to use as a unique key before it will let you display a view.

How to make a spatial view in PostGIS and add it as a layer in QGIS?

Which means, also, your view has to include a unique column to use as a key.

edited Apr 13 '17 at 12:34

Community

1

answered Apr 17 '13 at 19:08

Paul Ramsey

19,865
1
47
57

brunci has a problem that he can not see any of View tabels that the owner of the table can see (There is no problem with unique column) – hapa Apr 18 '13 at 06:42

score 3 · Accepted Answer · answered May 07 '13 at 06:09

3

user XX must give privilege to user YY on that view table
in PgAdmin SQL editor user XX should write:

GRANT SELECT ON TABLE "view table" TO "ROLE XYZ"

and of course user YY must be member of ROLE XYZ

answered May 07 '13 at 06:09

hapa

421
3
9

score 0 · Answer 3 · edited Apr 23 '20 at 08:58

0

for me, typecasting worked, e.g.:

CREATE VIEW myview AS SELECT id,
                      ST_Buffer(geom_pt,2.6)::geometry(Polygon,25832) AS mybuffer
                      FROM myoriginaltable;

edited Apr 23 '20 at 08:58

Taras

32,823
4
66
137

answered Apr 23 '20 at 08:55

TimKlein

1
1

Problem loading PostGIS (Postgres) View layer in QGIS

3 Answers3