Creating views to show specific spatial data [POSGIS POSTGRES QGIS VIEWS]

Hello everyone,

Sometimes as database managers we need to show only specific data for security or for whetever reason . In this case we are talking about showing geometry that are stored in two (relational) tables the problem remains in not showing the area, descripcion and id_user fields of state table .

Fig 1. Tables

Solution:  1) Create view,  2) Create role, 3)Setup in QGIS

First thing is to create a view, note that is important to add the objectid column otherwise QGIS is going to get an error.

CREATE VIEW view_state AS

        SELECT  sg.id_state_geom AS objectid,

        COALESCE(s.name, 'NO NAME'::character varying) AS name,

   FROM (state_geom sg

       LEFT JOIN state s ON ((sg.id_state = s.id_state)))

       ORDER BY sg.id_state

Second step is create a role wich be attached to the view and only to the view.

CREATE ROLE xxx WITH LOGIN PASSWORD 's3cr3t' 

NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';

GRANT CONNECT ON DATABASE "mydatabase" TO xxx;

GRANT USAGE ON SCHEMA public TO xxx;

GRANT SELECT ON view_state TO xxx;

Finally need to setup the QGIS connection and show the geometry.

That's it.