Module: PgRls::Schema::Solo::UpStatements

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

Overview

Up Schema Solo Statements

Instance Method Summary collapse

Instance Method Details

#append_blocking_function ⇒ Object 



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

def append_blocking_function
  <<~SQL
    -- Append Blocking Function
    CREATE TRIGGER id_safe_guard
      BEFORE UPDATE OF tenant_id ON #{PgRls.table_name}
        FOR EACH ROW EXECUTE PROCEDURE id_safe_guard();
  SQL
end

#create_rls_blocking_function ⇒ Object 



75
76
77
78
79
80
81
82
83
84
 
# File 'lib/pg_rls/schema/solo/up_statements.rb', line 75

def create_rls_blocking_function
  <<~SQL
    -- Create RLS Blocking Function
    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_setter_function ⇒ Object 



55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
 
# File 'lib/pg_rls/schema/solo/up_statements.rb', line 55

def create_rls_setter_function
  <<~SQL
    -- Create RLS Setter Function
    CREATE OR REPLACE FUNCTION tenant_id_setter ()
      RETURNS TRIGGER LANGUAGE plpgsql AS $$
        BEGIN
          IF NOT EXISTS (
            SELECT FROM #{PgRls.table_name}
              WHERE tenant_id = (current_setting('rls.tenant_id'))::uuid
          ) THEN
            INSERT INTO #{PgRls.table_name} (tenant_id)
              VALUES ((current_setting('rls.tenant_id'))::uuid);
          END IF;

          NEW.tenant_id:= (current_setting('rls.tenant_id'));
          RETURN NEW;
        END $$;
  SQL
end

#create_rls_solo_tenant_table ⇒ Object 



86
87
88
89
90
91
92
93
 
# File 'lib/pg_rls/schema/solo/up_statements.rb', line 86

def create_rls_solo_tenant_table
  <<~SQL
    -- Create Tenant Table
    CREATE TABLE #{PgRls.table_name} (
      tenant_id uuid PRIMARY KEY
    );
  SQL
end

#create_rls_user(name: PgRls.username, password: PgRls.password, schema: 'public') ⇒ Object 



27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
 
# File 'lib/pg_rls/schema/solo/up_statements.rb', line 27

def create_rls_user(name: PgRls.username, password: PgRls.password, schema: 'public')
  <<~SQL
    -- Grant Role Permissions
    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;
  SQL
end

#setup_rls_tenant_table ⇒ Object 



8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
# File 'lib/pg_rls/schema/solo/up_statements.rb', line 8

def setup_rls_tenant_table
  ActiveRecord::Migration.execute <<-SQL
    DO
    $do$
      BEGIN
        IF NOT EXISTS (
          SELECT FROM pg_tables
          WHERE schemaname = 'public' AND tablename = '#{PgRls.table_name}') THEN
            #{create_rls_user}
            #{create_rls_setter_function}
            #{create_rls_blocking_function}
            #{create_rls_solo_tenant_table}
            #{append_blocking_function}
        END IF;
      END;
    $do$;
  SQL
end