Stefan Carl
2014-09-24 14:45:53 UTC
<html><head></head><body><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 create a login-role, that only enable readonly rights for the data. I easy find hints to the GRANT-command and i created a login-role "readonly" and modify the permissions of the role with this commands.</div>
<div> </div>
<div>GRANT CONNECT ON DATABASE the_db TO readonly;</div>
<div>GRANT USAGE ON SCHEMA public TO readonly;</div>
<div>GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;</div>
<div>GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;</div>
<div>GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO readonly;</div>
<div> </div>
<div>This works fine since i edit columns of tables or e.g. load new Shapefiles in the database. The readonly-role did not get the changes and i have to GRANT the Permissions again.</div>
<div> </div>
<div>My question is: Is it possible to GRANT Permissions for existing and new or changed Objects 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>Part 7.2 shows something about a read-only user</div>
<div>CREATE ROLE readonly LOGIN PASSWORD 'some_pass';</div>
<div>-- Existing objects</div>
<div>GRANT CONNECT ON DATABASE the_db TO readonly;</div>
<div>GRANT USAGE ON SCHEMA public TO readonly;</div>
<div>GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;</div>
<div>GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;</div>
<div>GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO readonly;</div>
<div>-- New objects</div>
<div>ALTER DEFAULT PRIVILEGES FOR ddl_user IN SCHEMA public GRANT SELECT ON TABLES TO</div>
<div>readonly;</div>
<div>ALTER DEFAULT PRIVILEGES FOR ddl_user IN SCHEMA public GRANT SELECT ON SEQUENCES</div>
<div>TO readonly;</div>
<div>ALTER DEFAULT PRIVILEGES FOR ddl_user IN SCHEMA public GRANT EXECUTE ON FUNCTIONS</div>
<div>TO readonly;</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>Best regards</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></body></html>
<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 create a login-role, that only enable readonly rights for the data. I easy find hints to the GRANT-command and i created a login-role "readonly" and modify the permissions of the role with this commands.</div>
<div> </div>
<div>GRANT CONNECT ON DATABASE the_db TO readonly;</div>
<div>GRANT USAGE ON SCHEMA public TO readonly;</div>
<div>GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;</div>
<div>GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;</div>
<div>GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO readonly;</div>
<div> </div>
<div>This works fine since i edit columns of tables or e.g. load new Shapefiles in the database. The readonly-role did not get the changes and i have to GRANT the Permissions again.</div>
<div> </div>
<div>My question is: Is it possible to GRANT Permissions for existing and new or changed Objects 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>Part 7.2 shows something about a read-only user</div>
<div>CREATE ROLE readonly LOGIN PASSWORD 'some_pass';</div>
<div>-- Existing objects</div>
<div>GRANT CONNECT ON DATABASE the_db TO readonly;</div>
<div>GRANT USAGE ON SCHEMA public TO readonly;</div>
<div>GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;</div>
<div>GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;</div>
<div>GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO readonly;</div>
<div>-- New objects</div>
<div>ALTER DEFAULT PRIVILEGES FOR ddl_user IN SCHEMA public GRANT SELECT ON TABLES TO</div>
<div>readonly;</div>
<div>ALTER DEFAULT PRIVILEGES FOR ddl_user IN SCHEMA public GRANT SELECT ON SEQUENCES</div>
<div>TO readonly;</div>
<div>ALTER DEFAULT PRIVILEGES FOR ddl_user IN SCHEMA public GRANT EXECUTE ON FUNCTIONS</div>
<div>TO readonly;</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>Best regards</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></body></html>