2

I'm trying to create a database user (postgres) to access PostGIS data via QGIS.

I've applied the following commands:

GRANT ALL ON SCHEMA PUBLIC TO ene;
GRANT SELECT ON ALL TABLES IN SCHEMA public to ene;
GRANT ALL PRIVILEGES ON DATABASE "raiateaBD" TO ene;

The access tab for this database looks like this: enter image description here

However, when creating a connection for this user in QGIS, the tables are not available.

enter image description here

Logging in as postgres user, the tables are visible and available to be loaded: enter image description here

Furthermore, a project previously created with data loaded by postgres user (data owner) provokes the handle bad layers window when logged in as ene user.

enter image description here

The QGIS log has this error for each of the layers that are in the database: enter image description here

I added the following, and the user 'ene' still does not have access:

GRANT ALL ON TABLE public.spatial_ref_sys TO ene;
GRANT ALL ON TABLE public.geography_columns TO ene;
GRANT ALL ON TABLE public.geometry_columns TO ene;

BREAKTHROUGH

Layer is accessible if I call:

GRANT SELECT ON TABLE public."table_name" TO ene;

However, I'd like to not calling it one by one, but the following command is not doing it:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO ene;

What am I missing?

  • 1
    You're providing some stratospheric permissions when something more plain vanilla would suffice . I loathe public as a security breach in waiting, but a GRANT usage ON SCHEMA public TO username combined with a GRANT select ON tablename TO username as the table owner ought to get you by. – Vince May 7 at 22:50
  • can you double check that that user also has SELECT privileges to the public.geography_columns, public.geometry_columns and public.spatial_ref_sys tables? Assuming they are in the public schema. And at a minimum, usually only need to grant CONNECT privilege on the database, USAGE on the SCHEMA, and SELECT on the tables in question. – SaultDon May 7 at 22:51
  • @SaultDon It appears as if they don't (ACL value doesn't show the extra user). This must be my problem... – user25976 May 8 at 0:39
  • @Vince So GRANT select ON tablename TO username; will work differently from what I've done with GRANT SELECT ON ALL TABLES IN SCHEMA public to ene;? – user25976 May 9 at 19:20
  • Sorry, no idea whatsoever. I would never use either the public schema or the ALL TABLES construct. – Vince May 9 at 20:13

Your Answer

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

Browse other questions tagged or ask your own question.