QGIS » QGIS Application

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

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.

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

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;

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.

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.

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.

Milestone Version 0.9.2 deleted