-- First, create the database if it doesn't exist
CREATE DATABASE IF NOT EXISTS your_database;
-- Create the three roles
CREATE ROLE power_role;
CREATE ROLE read_write_role;
CREATE ROLE read_role;
-- Grant privileges to the power role (highest level)
GRANT ALL PRIVILEGES ON DATABASE your_database TO ROLE power_role;
GRANT ALL PRIVILEGES ON ALL SCHEMAS IN DATABASE your_database TO ROLE power_role;
GRANT ALL PRIVILEGES ON FUTURE SCHEMAS IN DATABASE your_database TO ROLE power_role;
GRANT ALL PRIVILEGES ON ALL TABLES IN DATABASE your_database TO ROLE power_role;
GRANT ALL PRIVILEGES ON FUTURE TABLES IN DATABASE your_database TO ROLE power_role;
-- Grant privileges to the read_write role (middle level)
GRANT USAGE ON DATABASE your_database TO ROLE read_write_role;
GRANT USAGE ON ALL SCHEMAS IN DATABASE your_database TO ROLE read_write_role;
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE your_database TO ROLE read_write_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN DATABASE your_database TO ROLE read_write_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON FUTURE TABLES IN DATABASE your_database TO ROLE read_write_role;
-- Grant privileges to the read role (lowest level)
GRANT USAGE ON DATABASE your_database TO ROLE read_role;
GRANT USAGE ON ALL SCHEMAS IN DATABASE your_database TO ROLE read_role;
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE your_database TO ROLE read_role;
GRANT SELECT ON ALL TABLES IN DATABASE your_database TO ROLE read_role;
GRANT SELECT ON FUTURE TABLES IN DATABASE your_database TO ROLE read_role;
-- Establish the role hierarchy
GRANT ROLE read_role TO ROLE read_write_role;
GRANT ROLE read_write_role TO ROLE power_role;