Dear List,
i work with a PostgreSQL/PostGIS-database (version 9.1.14/1.5.3) to manage geodata and other data.
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.
GRANT CONNECT ON DATABASE the_db TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO readonly;
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.
My question is: Is it possible to GRANT Permissions for existing and new or changed Objects in the database.
I only find this link.
http://wiki.postgresql.org/images/d/d1/Managing_rights_in_postgresql.pdf
Part 7.2 shows something about a read-only user
CREATE ROLE readonly LOGIN PASSWORD 'some_pass';
-- Existing objects
GRANT CONNECT ON DATABASE the_db TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO readonly;
-- New objects
ALTER DEFAULT PRIVILEGES FOR ddl_user IN SCHEMA public GRANT SELECT ON TABLES TO
readonly;
ALTER DEFAULT PRIVILEGES FOR ddl_user IN SCHEMA public GRANT SELECT ON SEQUENCES
TO readonly;
ALTER DEFAULT PRIVILEGES FOR ddl_user IN SCHEMA public GRANT EXECUTE ON FUNCTIONS
TO readonly;
The second part (New objects) is very important from my point of view, but it does not work.
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?
Best regards
Stefan
