Bug report #1011

No more access to postgis layer as non admin user

Added by Horst Düster over 12 years ago. Updated about 11 years ago.

Status:Closed
Priority:Low
Assignee:Jürgen Fischer
Category:Data Provider
Affected QGIS version: Regression?:No
Operating System:Linux Easy fix?:No
Pull Request or Patch supplied: Resolution:fixed
Crashes QGIS or corrupts data: Copied to github as #:11071

Description

No admin user don't have access to postgis layers. Open the postgres connection gui and define a postgres connection with a non admin user. Click test connection says that the connection is ok. After click connect no layers are shown. When I change the connection user to an admin user, I can see all schemas and layers.

Bildschirmphoto8.png (21.4 KB) Horst Düster, 2008-03-27 06:16 AM

History

#1 Updated by Jürgen Fischer over 12 years ago

  • Resolution set to fixed
  • Status changed from Open to Closed

I cannot reproduce that here. But I suspect that the user in question doesn't have select privilege on any PostGIS table with geometry and/or geometry_columns. I added a warning in 9a584ede (SVN r8287) that notifies the user when there were no accessible tables found.
I'm closing this bug, but feel free to reopen it, if my assumption isn't correct.

#2 Updated by Horst Düster over 12 years ago

  • Status changed from Closed to Feedback
  • Resolution deleted (fixed)

Replying to [comment:1 jef]:

I cannot reproduce that here. But I suspect that the user in question doesn't have select privilege on any PostGIS table with geometry and/or geometry_columns. I added a warning in 9a584ede (SVN r8287) that notifies the user when there were no accessible tables found.
I'm closing this bug, but feel free to reopen it, if my assumption isn't correct.

The user in question has all select rights he need. The only difference to users they can see tables is the SUPERUSER flag. All users defined with NOSUPERUSER flag don't have access to PostGIS tables and your warning appears. I reopen the ticket.

#3 Updated by Jürgen Fischer over 12 years ago

I still cannot reproduce that here.

If I create a fresh unprivileged user I also see not tables, but once I grant select on a geometry table and geometry_columns the user can see just that one table.

Is there something special about your configuration? I'm using PostgreSQL 8.2.4 and PostGIS 1.2

#4 Updated by Jürgen Fischer over 12 years ago

Replying to [comment:3 jef]:

Is there something special about your configuration? I'm using PostgreSQL 8.2.4 and PostGIS 1.2

That was on Windows. I just tried using PostgreSQL 8.1.1/PostGIS 1.1 on Debian which also works fine here.

#5 Updated by Horst Düster over 12 years ago

Replying to [comment:4 jef]:

Replying to [comment:3 jef]:

Is there something special about your configuration? I'm using PostgreSQL 8.2.4 and PostGIS 1.2

That was on Windows. I just tried using PostgreSQL 8.1.1/PostGIS 1.1 on Debian which also works fine here.

No there is nothing special with my configuration. The point seems to be the NOSUPERUSER flag. The user in question, mspublic, has in fact all neccessary rights.

Here the definition of schema public:

CREATE SCHEMA public
  AUTHORIZATION hdus;
GRANT ALL ON SCHEMA public TO hdus;
GRANT ALL ON SCHEMA public TO mspublic;
COMMENT ON SCHEMA public IS 'Standard public schema'; 

Definition of table geometry_columns:

CREATE TABLE geometry_columns
(
  f_table_catalog varchar(256) NOT NULL,
  f_table_schema varchar(256) NOT NULL,
  f_table_name varchar(256) NOT NULL,
  f_geometry_column varchar(256) NOT NULL,
  coord_dimension int4 NOT NULL,
  srid int4 NOT NULL,
  "type" varchar(30) NOT NULL,
  CONSTRAINT gc_pk PRIMARY KEY (f_table_catalog, f_table_schema, f_table_name, f_geometry_column)
) 
WITH OIDS;
ALTER TABLE geometry_columns OWNER TO hdus;
GRANT ALL ON TABLE geometry_columns TO hdus;
GRANT SELECT ON TABLE geometry_columns TO mspublic;

Definition of an existing geometry table:

CREATE TABLE testa
(
  gid int4 NOT NULL,
  myid int4,
  the_geom geometry,
  layer varchar(30),
  CONSTRAINT testa_pkey PRIMARY KEY (gid),
  CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2),
  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POLYGON'::text OR the_geom IS NULL),
  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = -1)
) 
WITHOUT OIDS;
ALTER TABLE testa OWNER TO hdus;
GRANT ALL ON TABLE testa TO hdus;
GRANT SELECT ON TABLE testa TO mspublic;

The user mspublic is defined with:

CREATE ROLE mspublic LOGIN
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;

When I change the definition of user mspublic to:

CREATE ROLE mspublic LOGIN
  SUPERUSER INHERIT NOCREATEDB NOCREATEROLE;

User mspublic can see all schemas and tables as I expect.

My system:
RedHat AS4
PostgreSQL 8.1.11

PostGIS 1.1.5

#6 Updated by Jürgen Fischer over 12 years ago

  • Status changed from Feedback to Open

#7 Updated by Jürgen Fischer over 12 years ago

I added another warning in 922cd7f0 (SVN r8288) in case the SELECT to fetch the geometry tables fails.

In case that doesn't return useful information please report what following SQL statement reports, when run as the user that currently cannot see any tables:

select
  f_table_schema,f_table_name
from
  geometry_columns,pg_class,pg_namespace
where
  relname=f_table_name and
  f_table_schema=nspname and
  pg_namespace.oid = pg_class.relnamespace and
has_table_privilege('"'||pg_namespace.nspname||'"."'||pg_class.relname||'"','select')
order by
   f_table_schema,f_table_name;

#8 Updated by Horst Düster over 12 years ago

Replying to [comment:7 jef]:

I added another warning in 922cd7f0 (SVN r8288) in case the SELECT to fetch the geometry tables fails.

In case that doesn't return useful information please report what following SQL statement reports, when run as the user that currently cannot see any tables:

> select
>   f_table_schema,f_table_name
> from
>   geometry_columns,pg_class,pg_namespace
> where
>   relname=f_table_name and
>   f_table_schema=nspname and
>   pg_namespace.oid = pg_class.relnamespace and
> has_table_privilege('"'||pg_namespace.nspname||'"."'||pg_class.relname||'"','select')
> order by
>    f_table_schema,f_table_name;

Take a look at the attached screenshot. The problem seems to be, that the defined DB user doesn't have access permissions to all DB schemas.

#9 Updated by Jürgen Fischer over 12 years ago

Replying to [comment:8 hdus]:

Take a look at the attached screenshot. The problem seems to be, that the defined DB user doesn't have access permissions to all DB schemas.

ah, this should be fixed in 68a41f67 (SVN r8290) by checking the schema usage privilege first.

#10 Updated by Horst Düster over 12 years ago

Replying to [comment:9 jef]:

Replying to [comment:8 hdus]:

Take a look at the attached screenshot. The problem seems to be, that the defined DB user doesn't have access permissions to all DB schemas.

ah, this should be fixed in 68a41f67 (SVN r8290) by checking the schema usage privilege first.

Yes! Now it works fine for me. It is much more better than before, because the user can only see
his privileged schmas and tables. I think you can close this ticket. Thank you very much for this helpful enhancement.

#11 Updated by Jürgen Fischer over 12 years ago

  • Status changed from Open to Closed
  • Resolution set to fixed

Replying to [comment:10 hdus]:

Yes! Now it works fine for me. It is much more better than before, because the user can only see
his privileged schmas and tables. I think you can close this ticket. Thank you very much for this helpful enhancement.

You're welcome. BTW most of the privilege handling is Steven Mizuno work (see #962/commit:2ea7615b (SVN r8214)).

#12 Updated by Anonymous about 11 years ago

Milestone Version 0.9.2 deleted

Also available in: Atom PDF