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 "    ALTER TABLE \#{table_name}\n      ADD COLUMN IF NOT EXISTS tenant_id uuid,\n      ADD CONSTRAINT fk_\#{PgRls.table_name}\n        FOREIGN KEY (tenant_id)\n        REFERENCES \#{PgRls.table_name}(tenant_id)\n        ON DELETE CASCADE;\n  SQL\nend\n".squish

#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 "    ALTER TABLE \#{table_name}\n      ADD COLUMN IF NOT EXISTS\n        tenant_id uuid UNIQUE DEFAULT gen_random_uuid();\n  SQL\nend\n".squish

#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 "    CREATE TRIGGER id_safe_guard\n      BEFORE UPDATE OF id ON \#{table_name}\n        FOR EACH ROW EXECUTE PROCEDURE id_safe_guard();\n  SQL\nend\n".squish

#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 "    CREATE TRIGGER tenant_id_setter\n      BEFORE INSERT OR UPDATE ON \#{table_name}\n        FOR EACH ROW EXECUTE PROCEDURE tenant_id_setter();\n  SQL\nend\n".squish

#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 "    CREATE OR REPLACE FUNCTION id_safe_guard ()\n      RETURNS TRIGGER LANGUAGE plpgsql AS $$\n        BEGIN\n          RAISE EXCEPTION 'This column is guarded due to tenancy dependency';\n        END $$;\n  SQL\nend\n".squish

#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 "    ALTER TABLE \#{table_name} ENABLE ROW LEVEL SECURITY;\n    CREATE POLICY \#{table_name}_\#{user}\n      ON \#{table_name}\n      TO \#{user}\n      USING (tenant_id = NULLIF(current_setting('rls.tenant_id', TRUE), '')::uuid);\n  SQL\nend\n".squish

#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 "    CREATE OR REPLACE FUNCTION tenant_id_setter ()\n      RETURNS TRIGGER LANGUAGE plpgsql AS $$\n        BEGIN\n          new.tenant_id:= (current_setting('rls.tenant_id'));\n          RETURN new;\n        END $$;\n  SQL\nend\n".squish

#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')
  ActiveRecord::Migration.execute "    DO\n    $do$\n    BEGIN\n      IF NOT EXISTS (\n        SELECT FROM pg_catalog.pg_roles  -- SELECT list can be empty for this\n        WHERE  rolname = '\#{name}') THEN\n\n        CREATE USER \#{name} WITH PASSWORD '\#{password}';\n      END IF;\n      GRANT ALL PRIVILEGES ON TABLE schema_migrations TO \#{name};\n      GRANT USAGE ON SCHEMA \#{schema} TO \#{name};\n      ALTER DEFAULT PRIVILEGES IN SCHEMA \#{schema}\n        GRANT USAGE, SELECT\n        ON SEQUENCES TO \#{name};\n      ALTER DEFAULT PRIVILEGES IN SCHEMA \#{schema}\n        GRANT SELECT, INSERT, UPDATE, DELETE\n        ON TABLES TO \#{name};\n      GRANT SELECT, INSERT, UPDATE, DELETE\n        ON ALL TABLES IN SCHEMA \#{schema}\n        TO \#{name};\n      GRANT USAGE, SELECT\n        ON ALL SEQUENCES IN SCHEMA \#{schema}\n        TO \#{name};\n    END;\n    $do$;\n  SQL\nend\n"