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