[postgis-users] setting up a read only user / group for AutoCAD, ArcGIS, QGIS, and MapServer.

[Richard Greenwood]

On Mon, Apr 14, 2014 at 8:01 AM, Andy Colson <andy at squeakycode.net> wrote:

> On 4/14/2014 7:25 AM, Mark Volz wrote:
>
>> Hello,
>>
>> I would like to set up a user account in PostGIS / PostGRES with the
>> following:
>>
>> ·The user has read only access to all of the layers in a particular
>> database.
>>
>> ·The user also have read only access to any layers added or updated
>> through the shapefile uploader.
>>
>> ·The client software may be ArcGIS*, AutoCAD (Map), QGIS, MapServer, etc.
>>
>> *ArcGIS will use “query layers”, not SDE.
>>
>> If anyone has any cliff notes on how to properly  set up read only
>> permissions please let me know.
>>
>> Thank You
>>
>> Sincerely,
>>
>> Mark Volz, GISP
>>
>>
>
> You can think of users and groups pretty much the same.  "user" and "role"
> are mostly interchangeable.
>
> Doesn't really matter who the owner of the db is, that can stay as-is.
>
> We'll create a new role:
>
> create user unwashed with password 'notpassword';
> -- the difference between role and user is the "can login" right.  For me
> I was gonna grant them all login rights anyway.  You should be able to
> change the above to "create role" if you wanted to tighten it down.
>
> -- grant it select
> grant select on maintable to unwashed;
>
> -- if you use sequences, they need rights
> grant all on sequence maintable_id_seq to unwashed;
>
> -- func's need exec:
> grant execute on function update(userid integer) to unwashed;
>
>
> -- There might be other's I'm missing.
> -- Then create a new user in the unwashed group:
>
> create user bob with nocreaterole password 'notpassword' in role unwashed;
>
> Its simple to add/remove users now.  When you create new stuff, remember
> to grant the unwashed select rights. :-)
>

There is also ALTER DEFAULT PRIVILEGES at the database level and at the
schema level. It is handy when you are adding stuff so that you don't have
to explicitly grant privileges every time.

ALTER DEFAULT PRIVILEGES IN SCHEMA some_schema
    GRANT SELECT ON TABLES
    TO unwashed;

-- 
Richard Greenwood
richard.greenwood at gmail.com
www.greenwoodmap.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140414/a26e6194/attachment.html>