Module: PgRls::Schema::UpStatements

Included in:
Statements
Defined in:
lib/pg_rls/schema/up_statements.rb

Overview

Up Schema Statements

Instance Method Summary collapse

Instance Method Details

#add_rls_column(table_name) ⇒ Object 



82
83
84
85
86
87
88
89
90
91
 
# File 'lib/pg_rls/schema/up_statements.rb', line 82

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) ⇒ Object 



74
75
76
77
78
79
80
 
# File 'lib/pg_rls/schema/up_statements.rb', line 74

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) ⇒ Object 



58
59
60
61
62
63
64
 
# File 'lib/pg_rls/schema/up_statements.rb', line 58

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) ⇒ Object 



66
67
68
69
70
71
72
 
# File 'lib/pg_rls/schema/up_statements.rb', line 66

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 ⇒ Object 



37
38
39
40
41
42
43
44
45
 
# File 'lib/pg_rls/schema/up_statements.rb', line 37

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.username) ⇒ Object 



93
94
95
96
97
98
99
100
101
 
# File 'lib/pg_rls/schema/up_statements.rb', line 93

def create_rls_policy(table_name, user = PgRls.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 ⇒ Object 



47
48
49
50
51
52
53
54
55
56
 
# File 'lib/pg_rls/schema/up_statements.rb', line 47

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.username, password: PgRls.password, schema: 'public') ⇒ Object 



7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
# File 'lib/pg_rls/schema/up_statements.rb', line 7

def create_rls_user(name: PgRls.username, password: PgRls.password, schema: 'public')
  PgRls.execute <<-SQL
    DO
    $do$
    BEGIN
      IF NOT EXISTS (
        SELECT FROM pg_catalog.pg_roles  -- SELECT list can be empty for this
        WHERE  rolname = '#{name}') THEN

        CREATE USER #{name} WITH PASSWORD '#{password}';
      END IF;
      GRANT ALL PRIVILEGES ON TABLE schema_migrations TO #{name};
      GRANT USAGE ON SCHEMA #{schema} TO #{name};
      ALTER DEFAULT PRIVILEGES IN SCHEMA #{schema}
        GRANT USAGE, SELECT
        ON SEQUENCES TO #{name};
      ALTER DEFAULT PRIVILEGES IN SCHEMA #{schema}
        GRANT SELECT, INSERT, UPDATE, DELETE
        ON TABLES TO #{name};
      GRANT SELECT, INSERT, UPDATE, DELETE
        ON ALL TABLES IN SCHEMA #{schema}
        TO #{name};
      GRANT USAGE, SELECT
        ON ALL SEQUENCES IN SCHEMA #{schema}
        TO #{name};
    END;
    $do$;
  SQL
end