*** This version of Confluence is for testing only and contains a copy of content from June 29th 2026. No changes will be preserved. ***
...
(John) Similar to above, but, in addition, create a role for read-write:CREATE my_rw_schema_role INHERIT;
CREATE SCHEMA my_rw_schema;
GRANT USAGE ON SCHEMA my_rw_schema TO my_rw_schema_role;
GRANT USAGE ON SCHEMA my_rw_schema TO my_ro_schema_role;
GRANT SELECT ON ALL TABLES IN SCHEMA my_rw_schema TO my_rw_schema_role;
GRANT SELECT ON ALL TABLES IN SCHEMA my_rw_schema TO my_ro_schema_role;;
GRANT CREATE ON SCHEMA my_rw_schema TO my_rw_schema_role;
GRANT ALL ON ALL TABLES IN SCHEMA my_rw_schema TO my_rw_schema_role;GRANT my_rw_schema_role to $USER1;
GRANT my_rw_schema_role to $USER2;
etc.Note that when a user with rw privs creates a table in the rw schema, they will need to explicitly grant privs on their table in order for others to use it. For example,GRANT SELECT ON my_rw_schema.mytable TO my_ro_schema_role;GRANT SELECT, INSERT, UPDATE, DELETE ON my_rw_schema.mytable TO my_rw_schema_role;Nassib might have other suggestions, but that’s how I’ve done it.
------------------------------------------
Hi @Sharon MarkusWanted to reach out to you regarding SI#3411429: Request for additional user role on Metadb Test - CORNELL UNIV
We have added 2 roles for schema local_shared -- following same guidelines as noted -- https://confluence.cornell.edu/display/folioreporting/Creating+Additional+Metadb+Schemas
Read only role is ro_local_shared
Read Write Role is rw_local shared
Roles have been defined and granted as in the SQL at end of message.
Please note (similar to what is noted in Johns slack conversation) - you will need to explicitly grant privs in order for other users to use newly added tables.
Note that when a user with rw privs creates a table in the rw schema, they will need to explicitly grant privs on their table in order for others to use it.
For example, GRANT SELECT ON my_rw_schema.mytable TO my_ro_schema_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_rw_schema.mytable TO my_rw_schema_role;`
In your case - for example - if a new heartbeat table is added to local_shared shared:
GRANT SELECT ON local_shared.heartbeat TO ro_local_shared;
GRANT SELECT, INSERT, UPDATE, DELETE ON local_shared.heartbeat TO rw_local_shared;`
-- Read-only role CREATE ROLE ro_local_shared INHERIT; GRANT USAGE ON SCHEMA local_shared TO ro_local_shared; GRANT SELECT ON ALL TABLES IN SCHEMA local_shared TO ro_local_shared; ALTER DEFAULT PRIVILEGES IN SCHEMA local_shared GRANT SELECT ON TABLES TO ro_local_shared;
-- Read/write role CREATE ROLE rw_local_shared INHERIT; GRANT USAGE, CREATE ON SCHEMA local_shared TO rw_local_shared; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA local_shared TO rw_local_shared; ALTER DEFAULT PRIVILEGES IN SCHEMA local_shared GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO rw_local_shared; GRANT USAGE ON ALL SEQUENCES IN SCHEMA local_shared TO rw_local_shared; ALTER DEFAULT PRIVILEGES IN SCHEMA local_shared GRANT USAGE ON SEQUENCES TO rw_local_shared;
-- Grant r/w privileges to users GRANT rw_local_shared TO z_slm5; GRANT rw_local_shared TO z_ama8; GRANT rw_local_shared TO z_jl41; GRANT rw_local_shared TO z_lm15; GRANT rw_local_shared TO z_jmp8; GRANT rw_local_shared TO z_np55; GRANT rw_local_shared TO z_vp25; -- Grant r/o privileges to users GRANT ro_local_shared TO z_ab18; GRANT ro_local_shared TO z_alc28; GRANT ro_local_shared TO z_dlh19; GRANT ro_local_shared TO z_fbw4; GRANT ro_local_shared TO z_jrc88; GRANT ro_local_shared TO z_map6; GRANT ro_local_shared TO z_mb327; GRANT ro_local_shared TO z_pd36; GRANT ro_local_shared TO z_pm66; GRANT ro_local_shared TO z_tit1;
Please let us know how testing goes and if any adjustments are needed
Carole Godfrey
I also agree with trying to keep permissions simple and having users use the z_metadbuser_cornell account where possible. Wanted to share a sql statement that you may find useful.For the local_statistics schema (where only 3 users have write access)
z_slm5, z_lm15, z_vp25
ALTER DEFAULT PRIVILEGES for user z_slm5 IN SCHEMA local_statistics GRANT SELECT ON TABLES TO z_lm15, z_vp25; ALTER DEFAULT PRIVILEGES for user z_slm5 IN SCHEMA local_statistics GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO z_lm15, z_vp25;
See reference below:
