Thread: readonly user

<div style="font-family: Verdana;font-size: 12.0px;"><div><div>Dear List,</div><div> </div><div>i work with a
PostgreSQL/PostGIS-database(version 9.1.14/1.5.3) to manage geodata and other data.</div><div> </div><div>Now i want to
createa login-role, that only enable readonly rights for the data. I easy find hints to the GRANT-command and i created
alogin-role "readonly" and modify the permissions of the role with this commands.</div><div> </div><div>GRANT CONNECT
ONDATABASE the_db TO readonly;</div><div>GRANT USAGE ON SCHEMA public TO readonly;</div><div>GRANT SELECT ON ALL TABLES
INSCHEMA public TO readonly;</div><div>GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;</div><div>GRANT
EXECUTEON ALL FUNCTIONS IN SCHEMA public TO readonly;</div><div> </div><div>This works fine since i edit columns of
tablesor e.g. load new Shapefiles in the database. The readonly-role did not get the changes and i have to GRANT the
Permissionsagain.</div><div> </div><div>My question is: Is it possible to GRANT Permissions for existing and new or
changedObjects in the database.</div><div> </div><div>I only find this
link.</div><div>http://wiki.postgresql.org/images/d/d1/Managing_rights_in_postgresql.pdf</div><div> </div><div>Part7.2
showssomething about a read-only user</div><div>CREATE ROLE readonly LOGIN PASSWORD 'some_pass';</div><div>-- Existing
objects</div><div>GRANTCONNECT ON DATABASE the_db TO readonly;</div><div>GRANT USAGE ON SCHEMA public TO
readonly;</div><div>GRANTSELECT ON ALL TABLES IN SCHEMA public TO readonly;</div><div>GRANT SELECT ON ALL SEQUENCES IN
SCHEMApublic TO readonly;</div><div>GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO readonly;</div><div>-- New
objects</div><div>ALTERDEFAULT PRIVILEGES FOR ddl_user IN SCHEMA public GRANT SELECT ON TABLES
TO</div><div>readonly;</div><div>ALTERDEFAULT PRIVILEGES FOR ddl_user IN SCHEMA public GRANT SELECT ON
SEQUENCES</div><div>TOreadonly;</div><div>ALTER DEFAULT PRIVILEGES FOR ddl_user IN SCHEMA public GRANT EXECUTE ON
FUNCTIONS</div><div>TOreadonly;</div><div> </div><div>The second part (New objects) is very important from my point of
view,but it does not work.</div><div> </div><div>I also have a look in some manuals but i dont find any solution for my
problem.Is there a easy solution for that problem? Is the development of a Trigger
necessary?</div><div> </div><div>Bestregards</div><div>Stefan</div><div data-angle="0"
data-canvas-width="481.46665518760716"data-font-name="g_font_90_0" dir="ltr" style="font-size: 13.3333px; font-family:
monospace;left: 222.167px; top: 741.627px; transform: rotate(0deg) scale(1.00306, 1); transform-origin: 0% 0%
0px;"> </div></div></div>