| From: | "Stefan Carl" <stefancarl89(at)web(dot)de> |
|---|---|
| To: | pgsql-admin(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org |
| Subject: | readonly user |
| Date: | 2014-09-24 14:45:53 |
| Message-ID: | trinity-644c5da0-0d1b-4cf4-9613-74dd4009279a-1411569953455@3capp-webde-bs38 |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-admin pgsql-general |
<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>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 2.8 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Geoff Winkless | 2014-09-24 15:25:08 | Re: [ADMIN] readonly user |
| Previous Message | jayknowsunix | 2014-09-24 14:17:39 | Re: Removing a Database Server |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Geoff Winkless | 2014-09-24 15:25:08 | Re: [ADMIN] readonly user |
| Previous Message | Emanuel Araújo | 2014-09-24 14:39:46 | How to clone CURRENT_DATE to SYSDATE ? |