readonly user
От | Stefan Carl |
---|---|
Тема | readonly user |
Дата | |
Msg-id | trinity-644c5da0-0d1b-4cf4-9613-74dd4009279a-1411569953455@3capp-webde-bs38 обсуждение исходный текст |
Ответы |
Re: [ADMIN] readonly user
(Geoff Winkless <pgsqladmin@geoff.dj>)
|
Список | pgsql-general |
<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>
В списке pgsql-general по дате отправления: