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?

• Mapperz: you need to be postgres root (superuser) to view all tables. Is a security feature of postgres. Use the GRANT feature - postgresql.org/docs/9.0/static/sql-grant.html

Answer 1

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

Answer 2

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.

• hapa: 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)

Answer 3

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;