[postgis-users] MapServer postgis connection with user other than postgres, more problems

[Stefan Overkamp]

Sorry,

but MapServer refuses to draw the layer
as user mapserver, whereas he draws the layer as user
postgres.

Maybe there are other configurations to mention?

Stefan

snippet of my mapfile:

LAYER
   NAME 'Gleisnetz'
   GROUP 'Netz'
   CONNECTIONTYPE postgis
   #CONNECTION "host=192.168.0.13 port=5432 dbname=gleisinfo 
password=postgres user=postgres"
   CONNECTION "host=192.168.0.13 port=5432 dbname=gleisinfo 
password=postgis user=mapserver"
   DATA "the_geom from public.gleisnetz"
   STATUS ON
   TYPE Line

errormessage of the Flash-MapServer-Client:

[MapServer Error]: msDrawMap(): Failed to draw layer named 'Gleisnetz'



Stefan



In addition to creating the user in postgres itself, you will need to 
grant permissions for that user, e.g.

CREATE USER mms_user;  -- other options available -- see documentation
REVOKE ALL ON table_foo FROM PUBLIC;  -- now only postgres and other DB 
users can read/insert etc. on this table
GRANT SELECT ON table_foo TO mms_user;  -- let the new mms_user select 
from this table; see documentation for other options.

You will also want to add the new user to the pg_hba.conf file so that 
user can connect from the expected IP addresses to your database.

And finally, of course, the MMS connect string will want to have 
new_user instead of postgres.

If you have lots of users the GROUP can be useful in saving typing. It's 
best to do the REVOKE ALL FROM PUBLIC on all tables in your database 
(alas this needs to be done manually -- there is no command to do it 
magically to all tables and views) and then selectively restore 
permissions; make those permissions part of the process of updating the 
DB structure. You can also use schemas to partition information and 
selectively grant access to a given schema to certain users.

If you need to have a user do inserts and updates don't forget to add 
permissions to any underlying sequences -- that's bitten me more than once.

HTH,

Greg WIlliamson
DBA
GlobeXplorer LLC