module PgRls::Schema::UpStatements

Up Schema Statements

Public Instance Methods

add_rls_column(table_name) click to toggle source
# File lib/pg_rls/schema/up_statements.rb, line 70
      def add_rls_column(table_name)
        ActiveRecord::Migration.execute <<-SQL
          ALTER TABLE #{table_name}
            ADD COLUMN IF NOT EXISTS tenant_id uuid,
            ADD CONSTRAINT fk_#{PgRls.table_name}
              FOREIGN KEY (tenant_id)
              REFERENCES #{PgRls.table_name}(tenant_id)
              ON DELETE CASCADE;
        SQL
      end
add_rls_column_to_tenant_table(table_name) click to toggle source
# File lib/pg_rls/schema/up_statements.rb, line 62
      def add_rls_column_to_tenant_table(table_name)
        ActiveRecord::Migration.execute <<-SQL
          ALTER TABLE #{table_name}
            ADD COLUMN IF NOT EXISTS
              tenant_id uuid UNIQUE DEFAULT gen_random_uuid();
        SQL
      end
append_blocking_function(table_name) click to toggle source
# File lib/pg_rls/schema/up_statements.rb, line 46
      def append_blocking_function(table_name)
        ActiveRecord::Migration.execute <<-SQL
          CREATE TRIGGER id_safe_guard
            BEFORE UPDATE OF id ON #{table_name}
              FOR EACH ROW EXECUTE PROCEDURE id_safe_guard();
        SQL
      end
append_trigger_function(table_name) click to toggle source
# File lib/pg_rls/schema/up_statements.rb, line 54
      def append_trigger_function(table_name)
        ActiveRecord::Migration.execute <<-SQL
          CREATE TRIGGER tenant_id_setter
            BEFORE INSERT OR UPDATE ON #{table_name}
              FOR EACH ROW EXECUTE PROCEDURE tenant_id_setter();
        SQL
      end
create_rls_blocking_function() click to toggle source
# File lib/pg_rls/schema/up_statements.rb, line 25
      def create_rls_blocking_function
        ActiveRecord::Migration.execute <<-SQL
          CREATE OR REPLACE FUNCTION id_safe_guard ()
            RETURNS TRIGGER LANGUAGE plpgsql AS $$
              BEGIN
                RAISE EXCEPTION 'This column is guarded due to tenancy dependency';
              END $$;
        SQL
      end
create_rls_policy(table_name, user = PgRls::SECURE_USERNAME) click to toggle source
# File lib/pg_rls/schema/up_statements.rb, line 81
      def create_rls_policy(table_name, user = PgRls::SECURE_USERNAME)
        ActiveRecord::Migration.execute <<-SQL
          ALTER TABLE #{table_name} ENABLE ROW LEVEL SECURITY;
          CREATE POLICY #{table_name}_#{user}
            ON #{table_name}
            TO #{user}
            USING (tenant_id = NULLIF(current_setting('rls.tenant_id', TRUE), '')::uuid);
        SQL
      end
create_rls_setter_function() click to toggle source
# File lib/pg_rls/schema/up_statements.rb, line 35
      def create_rls_setter_function
        ActiveRecord::Migration.execute <<-SQL
          CREATE OR REPLACE FUNCTION tenant_id_setter ()
            RETURNS TRIGGER LANGUAGE plpgsql AS $$
              BEGIN
                new.tenant_id:= (current_setting('rls.tenant_id'));
                RETURN new;
              END $$;
        SQL
      end
create_rls_user(name: PgRls::SECURE_USERNAME, password: 'password') click to toggle source
# File lib/pg_rls/schema/up_statements.rb, line 7
      def create_rls_user(name: PgRls::SECURE_USERNAME, password: 'password')
        PgRls.execute <<-SQL
          DROP ROLE IF EXISTS #{name};
          CREATE USER #{name} WITH PASSWORD '#{password}';
          GRANT ALL PRIVILEGES ON TABLE schema_migrations TO #{name};
          GRANT USAGE ON SCHEMA public TO #{name};
          ALTER DEFAULT PRIVILEGES IN SCHEMA public
            GRANT SELECT, INSERT, UPDATE, DELETE
            ON TABLES TO #{name};
          GRANT SELECT, INSERT, UPDATE, DELETE
            ON ALL TABLES IN SCHEMA public
            TO #{name};
          GRANT USAGE, SELECT
            ON ALL SEQUENCES IN SCHEMA public
            TO #{name};
        SQL
      end