No more access to postgis layer as non admin user

[hdus]

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.

[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 r8286 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.

[hdus]

Replying to 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 r8286 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.

[jef]

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

[jef]

Replying to 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.

[hdus]

Replying to jef:

Replying to 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

[jef]

I added another warning in r8287 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;

[hdus]

Replying to jef:

I added another warning in r8287 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.

[jef]

Replying to 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 r8289 by checking the schema usage privilege first.

[hdus]

Replying to jef:

Replying to 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 r8289 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.

[jef]

Replying to 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/r8213).

[(none)]

Milestone Version 0.9.2 deleted