Problem Statement
We have a PostgreSQL database with multiple schemas and tables. Some users have read-only access to the database and and they relay on Devops/Support team to refresh their access to view any new schemas or tables added to the database. We need to provide a solution to allow read-only users to refresh their access so they can view new schemas and tables as they are added.
Named Read-only User Group
Function 1: Will create a user and create a read_only group not available. If the group is available, it will create the user and password, attach it to the read_only group, and add all existing schema read-only access.
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE or replace FUNCTION create_users_and_grant_access(users text[]) RETURNS void AS $$
DECLARE
READONLY_GROUP text := 'readonly';
password text;
user_name text;
schemata text;
BEGIN
FOREACH user_name IN ARRAY users LOOP
-- Check if the user already exists
PERFORM 1 FROM pg_user WHERE usename = user_name;
IF NOT FOUND THEN
-- Generate a random password for the new user
password := encode(gen_random_bytes(12), 'base64');
-- Create the database user with the hashed password
RAISE NOTICE 'Creating database user: %', user_name;
RAISE NOTICE 'Password: %', password;
EXECUTE format('CREATE USER %I WITH PASSWORD %L', user_name, password);
-- Create the read-only group if it does not exist
PERFORM 1 FROM pg_roles WHERE rolname = READONLY_GROUP;
IF NOT FOUND THEN
RAISE NOTICE 'Creating read-only group: %', READONLY_GROUP;
EXECUTE format('CREATE ROLE %I', READONLY_GROUP);
END IF;
-- Add the user to the read-only group
RAISE NOTICE 'Adding user to read-only group: %', READONLY_GROUP;
EXECUTE format('GRANT %I TO %I', READONLY_GROUP, user_name);
ELSE
RAISE NOTICE 'User already exists: %', user_name;
END IF;
END LOOP;
-- Grant read-only access to all schemas for the read-only group
FOR schemata IN SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT LIKE 'pg_%' AND schema_name != 'information_schema' LOOP
-- Check if the read-only group already has access to the schema
PERFORM 1 FROM information_schema.role_table_grants WHERE grantee = READONLY_GROUP AND table_schema = schemata;
IF NOT FOUND THEN
-- Grant read-only access to the schema for the read-only group
RAISE NOTICE 'Granting read-only access to schema: %', schemata;
EXECUTE format('GRANT USAGE ON SCHEMA %I TO %I', schemata, READONLY_GROUP);
EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO %I', schemata, READONLY_GROUP);
EXECUTE format('GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA %I TO %I', schemata, READONLY_GROUP);
ELSE
RAISE NOTICE 'Read-only access already granted to schema: %', schemata;
END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;
Function 2:
This function will enable users to refresh read_only permissions, so they don’t have to rely on DevOps
CREATE OR REPLACE FUNCTION grant_readonly_access(schematabe text DEFAULT NULL)
RETURNS void
SECURITY DEFINER
AS $$
DECLARE
READONLY_GROUP text := 'readonly';
BEGIN
IF schematabe IS NOT NULL THEN
-- Grant read-only access to specified schema for the user and read-only group
PERFORM 1 FROM information_schema.schemata WHERE schema_name = schematabe;
IF FOUND THEN
RAISE NOTICE 'Granting read-only access to schema: % for user: %', schematabe, READONLY_GROUP;
EXECUTE format('GRANT USAGE ON SCHEMA %I TO %I', schematabe, readonly_group);
EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO %I', schematabe, readonly_group);
EXECUTE format('GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA %I TO %I', schematabe, readonly_group);
EXECUTE format('GRANT USAGE ON SCHEMA %I TO %I', schematabe, READONLY_GROUP);
EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO %I', schematabe, READONLY_GROUP);
EXECUTE format('GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA %I TO %I', schematabe, READONLY_GROUP);
ELSE
RAISE EXCEPTION 'Schema not found: %', schematabe;
END IF;
ELSE
-- Grant read-only access to all schemas for the user and read-only group
FOR schematabe IN SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT LIKE 'pg_%' AND schema_name != 'information_schema' LOOP
-- Check if the read-only group already has access to the schema
PERFORM 1 FROM information_schema.role_table_grants WHERE grantee = readonly_group AND table_schema = schematabe;
IF NOT FOUND THEN
-- Grant read-only access to the schema for the read-only group
RAISE NOTICE 'Granting read-only access to schema: % for user: %', schematabe, READONLY_GROUP;
EXECUTE format('GRANT USAGE ON SCHEMA %I TO %I', schematabe, readonly_group);
EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO %I', schematabe, readonly_group);
EXECUTE format('GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA %I TO %I', schematabe, readonly_group);
EXECUTE format('GRANT USAGE ON SCHEMA %I TO %I', schematabe, READONLY_GROUP);
EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO %I', schematabe, READONLY_GROUP);
EXECUTE format('GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA %I TO %I', schematabe, READONLY_GROUP);
ELSE
RAISE NOTICE 'Read-only access already granted to schema: % for user: %', schematabe, READONLY_GROUP;
END IF;
END LOOP;
END IF;
END;
$$ LANGUAGE plpgsql;
Top comments (1)
Thanks for the good solution.