Обсуждение: readonly user
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
ALTER DEFAULT PRIVILEGES FOR ddl_user IN SCHEMA public GRANT SELECT ON TABLES TOreadonly;ALTER DEFAULT PRIVILEGES FOR ddl_user IN SCHEMA public GRANT SELECT ON SEQUENCESTO readonly;ALTER DEFAULT PRIVILEGES FOR ddl_user IN SCHEMA public GRANT EXECUTE ON FUNCTIONSTO readonly;The second part (New objects) is very important from my point of view, but it does not work.
Doesn't work how? Query fails? Or the permissions don't get granted?
What's the ddl_user role? Have you created it? Does it work if you leave the target role as the default? ie just run the ALTER DEFAULT without the "FOR ddl_user" section?