*** This version of Confluence is for testing only and contains a copy of content from June 29th 2026. No changes will be preserved. ***
-
Created by
Sharon Helen Markus, last updated on Oct 02, 2024
4 minute read
(saved slack messages from John Malconian, Index Data)
(Sharon) We have small groups of users who will need access to read and write to their own functional area local schemas.
(John) If these users have database accounts that were created via Metadb, then they will automatically have their own local schema to read and write to. The schema name will be the same as name of user/role.
(Sharon) We also need to create a schema for our legacy system data to which users only have read access
(John) This is outside the scope of Metadb, but can, of course, co-exist in the same database. In this case you could create a didicated role with ‘inherit’ which would function more or less as a group, create the schema, grant read-only privs to the role on the schema, and then add users to the role. Example:CREATE my_ro_schema_role INHERIT;
CREATE SCHEMA my_ro_schema;
GRANT USAGE ON SCHEMA my_ro_schema TO my_ro_schema_role;
GRANT SELECT ON ALL TABLES IN SCHEMA my_ro_schema to my_ro_schema_role;
GRANT my_ro_schema_role to $USER1;
GRANT my_ro_schema_role to $USER2;
etc.
(Sharon) In addition, we need to create a schema that only our small reporting team can write to, but allows for read access for all users.
(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:
