Class: Webhookdb::Organization::DbBuilder

Inherits:
Object
  • Object
show all
Extended by:
MethodUtilities
Includes:
Appydays::Configurable, Appydays::Loggable, Dbutil
Defined in:
lib/webhookdb/organization/db_builder.rb

Overview

When an org is created, it gets its own database containing the service integration tables exclusively for that org. This ensures orgs can be easily isolated from each other.

Each org has two connections:

  • admin, which can modify tables. Used to create service integration tables. We generally want to avoid using this connection. Should never be exposed.

  • readonly, used for reading only the service integration tables (and not additional PG info tables). Can safely be exposed to members of the org.

Defined Under Namespace

Classes: IsolatedOperationError

Constant Summary collapse

DATABASE = 
"database"
SCHEMA = 
"schema"
USER = 
"user"
NONE = 
"none"
VALID_ISOLATION_MODES = 
[
  "#{DATABASE}+#{USER}",
  "#{DATABASE}+#{SCHEMA}+#{USER}",
  SCHEMA,
  "#{SCHEMA}+#{USER}",
  NONE,
].freeze

Constants included from Dbutil

Dbutil::MOCK_CONN

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Methods included from MethodUtilities

attr_predicate, attr_predicate_accessor, singleton_attr_accessor, singleton_attr_reader, singleton_attr_writer, singleton_method_alias, singleton_predicate_accessor, singleton_predicate_reader

Methods included from Dbutil

borrow_conn, configured_connection_options, conn_opts, displaysafe_url, reduce_expr, take_conn

Constructor Details

#initialize(org) ⇒ DbBuilder

Returns a new instance of DbBuilder.



85
86
87
 
# File 'lib/webhookdb/organization/db_builder.rb', line 85

def initialize(org)
  @org = org
end

Instance Attribute Details

#admin_url ⇒ Object (readonly)

Returns the value of attribute admin_url.



83
84
85
 
# File 'lib/webhookdb/organization/db_builder.rb', line 83

def admin_url
  @admin_url
end

#readonly_url ⇒ Object (readonly)

Returns the value of attribute readonly_url.



83
84
85
 
# File 'lib/webhookdb/organization/db_builder.rb', line 83

def readonly_url
  @readonly_url
end

Class Method Details

.isolate?(type) ⇒ Boolean

Returns:

  • (Boolean) 


79
80
81
 
# File 'lib/webhookdb/organization/db_builder.rb', line 79

def self.isolate?(type)
  return self.isolation_mode.include?(type)
end

Instance Method Details

#_create_conn_url(username, password, uri, dbname) ⇒ Object 



239
240
241
 
# File 'lib/webhookdb/organization/db_builder.rb', line 239

def _create_conn_url(username, password, uri, dbname)
  return "postgres://#{username}:#{password}@#{uri.host}:#{uri.port}/#{dbname}"
end

#_prepare_database_connections_database_schema_user(superuser_url_str) ⇒ Object 



174
175
176
177
178
179
180
 
# File 'lib/webhookdb/organization/db_builder.rb', line 174

def _prepare_database_connections_database_schema_user(superuser_url_str)
  self._prepare_database_connections_database_user(superuser_url_str)
  # Revoke everything on public schema, so our readonly user cannot access it.
  borrow_conn(@admin_url) do |conn|
    conn << "REVOKE ALL ON SCHEMA public FROM public"
  end
end

#_prepare_database_connections_database_user(superuser_url_str) ⇒ Object 



116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
 
# File 'lib/webhookdb/organization/db_builder.rb', line 116

def _prepare_database_connections_database_user(superuser_url_str)
  superuser_url = URI.parse(superuser_url_str)
  # Use this superuser connection to create the admin role,
  # which will be responsible for the database.
  # While connected as the superuser, we can go ahead and create both roles.
  admin_user = self.randident("ad")
  admin_pwd = self.randident
  ro_user = self.randident("ro")
  ro_pwd = self.randident
  dbname = self.randident("db")
  # Do not log this
  borrow_conn(superuser_url_str, loggers: []) do |conn|
    conn << <<~SQL
      CREATE ROLE #{admin_user} PASSWORD '#{admin_pwd}' NOSUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;
      CREATE ROLE #{ro_user} PASSWORD '#{ro_pwd}' NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN;
      ALTER USER #{ro_user} WITH CONNECTION LIMIT #{self.class.readonly_connection_limit};
      ALTER ROLE #{ro_user} SET statement_timeout = '#{self.class.readonly_connection_timeout}';
      GRANT #{admin_user} TO CURRENT_USER;
    SQL
    # Cannot be in the same statement as above since that's one transaction.
    conn << "CREATE DATABASE #{dbname} OWNER #{admin_user};"
    conn << "REVOKE ALL PRIVILEGES ON DATABASE #{dbname} FROM public;"
  end

  # Now that we've created the admin role and have a database,
  # we must disconnect and connect to the new database.
  # This MUST be done as superuser; for some reason,
  # the public schema (which we need to revoke on) belongs to the superuser,
  # NOT the DB owner: https://pgsql-general.postgresql.narkive.com/X9VKOPIW
  superuser_in_db_str = self._create_conn_url(superuser_url.user, superuser_url.password, superuser_url, dbname)
  schema = self._org_schema
  borrow_conn(superuser_in_db_str) do |conn|
    conn << <<~SQL
      -- Revoke all rights from readonly user, and public role, which all users have.
      REVOKE ALL ON DATABASE #{dbname} FROM PUBLIC, #{ro_user};
      REVOKE ALL ON SCHEMA public FROM PUBLIC, #{ro_user};
      REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC, #{ro_user};
      -- Create the schema if needed. In most cases this is 'public' so it isn't.
      CREATE SCHEMA IF NOT EXISTS #{schema};
      -- Allow the readonly user to select stuff
      GRANT CONNECT ON DATABASE #{dbname} TO #{ro_user};
      GRANT USAGE ON SCHEMA #{schema} TO #{ro_user};
      GRANT SELECT ON ALL TABLES IN SCHEMA #{schema} TO #{ro_user};
      -- Now that we have modified public/replication schema as superuser,
      -- we can grant ownership to the admin user, so they can do modification in the future.
      ALTER SCHEMA public OWNER TO #{admin_user};
      ALTER SCHEMA #{schema} OWNER TO #{admin_user};
    SQL
  end
  @admin_url = self._create_conn_url(admin_user, admin_pwd, superuser_url, dbname)
  # We MUST modify the default privs AFTER changing ownership.
  # Changing ownership seems to reset default piv grants (and it cannot be done after transferring ownership)
  borrow_conn(@admin_url) do |conn|
    conn << "ALTER DEFAULT PRIVILEGES IN SCHEMA #{schema} GRANT SELECT ON TABLES TO #{ro_user};"
  end
  @readonly_url = self._create_conn_url(ro_user, ro_pwd, superuser_url, dbname)
end

#_prepare_database_connections_none(superuser_url_str) ⇒ Object 



217
218
219
220
 
# File 'lib/webhookdb/organization/db_builder.rb', line 217

def _prepare_database_connections_none(superuser_url_str)
  @admin_url = superuser_url_str
  @readonly_url = superuser_url_str
end

#_prepare_database_connections_schema(superuser_url_str) ⇒ Object 



182
183
184
185
186
187
188
 
# File 'lib/webhookdb/organization/db_builder.rb', line 182

def _prepare_database_connections_schema(superuser_url_str)
  borrow_conn(superuser_url_str) do |conn|
    conn << "CREATE SCHEMA IF NOT EXISTS #{self._org_schema};"
  end
  @admin_url = superuser_url_str
  @readonly_url = superuser_url_str
end

#_prepare_database_connections_schema_user(superuser_url_str) ⇒ Object 



190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
 
# File 'lib/webhookdb/organization/db_builder.rb', line 190

def _prepare_database_connections_schema_user(superuser_url_str)
  ro_user = self.randident("ro")
  ro_pwd = self.randident
  schema = self._org_schema
  borrow_conn(superuser_url_str) do |conn|
    conn << <<~SQL
      -- Create readonly role and make sure it cannot access public stuff
      CREATE ROLE #{ro_user} PASSWORD '#{ro_pwd}' NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN;
      ALTER USER #{ro_user} WITH CONNECTION LIMIT #{self.class.readonly_connection_limit};
      ALTER ROLE #{ro_user} SET statement_timeout = '#{self.class.readonly_connection_timeout}';
      REVOKE ALL ON SCHEMA public FROM #{ro_user};
      REVOKE CREATE ON SCHEMA public FROM #{ro_user};
      REVOKE ALL ON ALL TABLES IN SCHEMA public FROM #{ro_user};
      -- Create the schema and ensure readonly user can access it
      -- Also remove public role access so other readonly users cannot access it.
      CREATE SCHEMA IF NOT EXISTS #{schema};
      REVOKE ALL ON SCHEMA #{schema} FROM PUBLIC, #{ro_user};
      REVOKE ALL ON ALL TABLES IN SCHEMA #{schema} FROM PUBLIC, #{ro_user};
      GRANT USAGE ON SCHEMA #{schema} TO #{ro_user};
      GRANT SELECT ON ALL TABLES IN SCHEMA #{schema} TO #{ro_user};
      ALTER DEFAULT PRIVILEGES IN SCHEMA #{schema} GRANT SELECT ON TABLES TO #{ro_user};
    SQL
  end
  @admin_url = superuser_url_str
  @readonly_url = self._replace_url_auth(superuser_url_str, ro_user, ro_pwd)
end

#_replace_url_auth(url, user, pass) ⇒ Object 



243
244
245
246
247
248
 
# File 'lib/webhookdb/organization/db_builder.rb', line 243

def _replace_url_auth(url, user, pass)
  uri = URI(url)
  uri.user = user
  uri.password = pass
  return uri.to_s
end

#create_public_host_cname(conn_url) ⇒ Object

Create the CNAME record specific to this org, so that the DB it is hosted on is reachable via a nice URL, rather than connecting directly to the host.

If create_cnames is false, this method no-ops, so we don’t spam cloudflare during integration tests, or need to httpmock everything during unit tests.

Otherwise, create the CNAME like “myorg.db node.rds.amazonaws.com”, and set org.public_host to “myorg.db.webhookdb.dev”. The “webhookdb.dev” value comes from the Cloudflare DNS response, and corresponds to the zone that ‘cloudflare_dns_zone_id` identifies.



262
263
264
265
266
267
268
269
270
271
272
273
274
 
# File 'lib/webhookdb/organization/db_builder.rb', line 262

def create_public_host_cname(conn_url)
  return nil unless self.class.create_cname_for_connection_urls
  db_host = URI.parse(conn_url).host
  cname = Webhookdb::Cloudflare.create_zone_dns_record(
    type: "CNAME",
    zone_id: self.class.cloudflare_dns_zone_id,
    name: "#{@org.key}.db",
    content: db_host,
  )
  @org.public_host = cname["result"]["name"]
  @org.cloudflare_dns_record_json = cname
  return self
end

#default_replication_schema ⇒ Object

Raises:



89
90
91
92
93
 
# File 'lib/webhookdb/organization/db_builder.rb', line 89

def default_replication_schema
  raise Webhookdb::InvalidPrecondition, "Org must have a key to calculate the replication schema" if @org.key.blank?
  return "public" unless self.class.isolate?(SCHEMA)
  return "whdb_#{@org.key}"
end

#generate_fdw_payload(remote_server_name:, fetch_size:, local_schema:, view_schema:) ⇒ Object

Raises:

  • (ArgumentError) 


357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
 
# File 'lib/webhookdb/organization/db_builder.rb', line 357

def generate_fdw_payload(
  remote_server_name:,
  fetch_size:,
  local_schema:,
  view_schema:
)
  raise ArgumentError, "no arg can be blank" if
    [remote_server_name, fetch_size, local_schema, view_schema].any?(&:blank?)
  conn = URI(@org.readonly_connection_url)
  fdw_sql = <<~FDW_SERVER
    CREATE EXTENSION IF NOT EXISTS postgres_fdw;
    DROP SERVER IF EXISTS #{remote_server_name} CASCADE;
    CREATE SERVER #{remote_server_name}
      FOREIGN DATA WRAPPER postgres_fdw
      OPTIONS (host '#{conn.host}', port '#{conn.port}', dbname '#{conn.path[1..]}', fetch_size '#{fetch_size}');

    CREATE USER MAPPING FOR CURRENT_USER
      SERVER #{remote_server_name}
      OPTIONS (user '#{conn.user}', password '#{conn.password}');

    CREATE SCHEMA IF NOT EXISTS #{local_schema};
    IMPORT FOREIGN SCHEMA #{self._org_schema}
      FROM SERVER #{remote_server_name}
      INTO #{local_schema};

    CREATE SCHEMA IF NOT EXISTS #{view_schema};
  FDW_SERVER

  views_for_integrations = @org.service_integrations.to_h do |sint|
    cmd = "CREATE MATERIALIZED VIEW IF NOT EXISTS #{view_schema}.#{sint.service_name} " \
          "AS SELECT * FROM #{local_schema}.#{sint.table_name};"
    [sint.opaque_id, cmd]
  end
  views_sql = views_for_integrations.values.sort.join("\n")

  result = {
    fdw_sql:,
    views_sql:,
    compound_sql: "#{fdw_sql}\n\n#{views_sql}",
    views: views_for_integrations,
  }
  return result
end

#migration_replication_schema_sql(old_schema, new_schema) ⇒ Object 



401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
 
# File 'lib/webhookdb/organization/db_builder.rb', line 401

def migration_replication_schema_sql(old_schema, new_schema)
  can_migrate_to_public = self.class.isolate?(DATABASE)
  if new_schema == "public" && !can_migrate_to_public
    raise IsolatedOperationError,
          "cannot migrate to public schema when using '#{self.class.isolation_mode}' isolation"
  end
  ad = Webhookdb::DBAdapter::PG.new
  qold_schema = ad.escape_identifier(old_schema)
  qnew_schema = ad.escape_identifier(new_schema)
  lines = []
  # lines << "ALTER SCHEMA #{qold_schema} RENAME TO #{qnew_schema};"
  # lines << "CREATE SCHEMA IF NOT EXISTS public;"
  lines << "CREATE SCHEMA IF NOT EXISTS #{qnew_schema};"
  @org.service_integrations.each do |sint|
    lines << ("ALTER TABLE IF EXISTS %s.%s SET SCHEMA %s;" %
      [qold_schema, ad.escape_identifier(sint.table_name), qnew_schema])
  end
  if self.class.isolate?(USER)
    ro_user = @org.readonly_user
    lines << "GRANT USAGE ON SCHEMA #{qnew_schema} TO #{ro_user};"
    lines << "GRANT SELECT ON ALL TABLES IN SCHEMA #{qnew_schema} TO #{ro_user};"
    lines << "REVOKE ALL ON SCHEMA #{qold_schema} FROM #{ro_user};" unless @org.single_db_user?
    lines << "REVOKE ALL ON ALL TABLES IN SCHEMA #{qold_schema} FROM #{ro_user};" unless @org.single_db_user?
    lines << "ALTER DEFAULT PRIVILEGES IN SCHEMA #{qnew_schema} GRANT SELECT ON TABLES TO #{ro_user};"
  end
  # lines << "DROP SCHEMA #{qold_schema} CASCADE;"
  return lines.join("\n")
end

#prepare_database_connections ⇒ Object 



95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
 
# File 'lib/webhookdb/organization/db_builder.rb', line 95

def prepare_database_connections
  # Grab a random server url. This will give us a 'superuser'-like url
  # that can create roles and whatever else.
  superuser_str = self._choose_superuser_url
  case self.class.isolation_mode
    when "database+user"
      self._prepare_database_connections_database_user(superuser_str)
    when "database+schema+user"
      self._prepare_database_connections_database_schema_user(superuser_str)
    when "schema"
      self._prepare_database_connections_schema(superuser_str)
    when "schema+user"
      self._prepare_database_connections_schema_user(superuser_str)
    when "none"
      self._prepare_database_connections_none(superuser_str)
    else
      raise "Did not expect mode #{self.class.isolation_mode}"
  end
  return self
end

#randident(prefix = "") ⇒ Object

prefix with <id>a to avoid ever conflicting database names



235
236
237
 
# File 'lib/webhookdb/organization/db_builder.rb', line 235

def randident(prefix="")
  return "a#{prefix}#{@org.id}a#{SecureRandom.hex(8)}"
end

#remove_related_database ⇒ Object

To remove related databases and users, we must 1) find the server hosting the database, which will itself contain the admin creds

suitable for having created it in the first place.

2) delete the database, using info extracted from the admin connection (run from the server conn) 3) delete each user (run from the server conn) We need these workarounds because we cannot drop the admin database while we’re connected to it (and we probably don’t want the admin role trying to delete itself).



283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
 
# File 'lib/webhookdb/organization/db_builder.rb', line 283

def remove_related_database
  return if @org.admin_connection_url_raw.blank?
  superuser_str = self._find_superuser_url_str
  # Cannot use conn cache since we may be removing ourselves
  borrow_conn(superuser_str) do |conn|
    case self.class.isolation_mode
      when "database+user", "database+schema+user"
        Webhookdb::ConnectionCache.disconnect(@org.admin_connection_url_raw)
        Webhookdb::ConnectionCache.disconnect(@org.readonly_connection_url_raw)
        conn << "DROP DATABASE #{@org.dbname};"
        conn << "DROP USER #{@org.readonly_user};" unless @org.single_db_user?
        conn << "DROP USER #{@org.admin_user};"
      when "schema+user"
        Webhookdb::ConnectionCache.disconnect(@org.readonly_connection_url_raw)
        conn << <<~SQL
          DROP SCHEMA IF EXISTS #{self._org_schema} CASCADE;
          DROP OWNED BY #{@org.readonly_user};
          DROP USER #{@org.readonly_user};
        SQL
      when "schema"
        conn << "DROP SCHEMA IF EXISTS #{self._org_schema} CASCADE"
      when "none"
        nil
      else
        raise "not supported yet"
    end
  end
end

#roll_connection_credentials ⇒ Object

Raises:



325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
 
# File 'lib/webhookdb/organization/db_builder.rb', line 325

def roll_connection_credentials
  raise IsolatedOperationError, "cannot roll credentials without a user isolation mode" unless
    self.class.isolate?(USER)
  superuser_uri = URI(self._find_superuser_url_str)
  orig_readonly_user = URI(@org.readonly_connection_url_raw).user
  ro_user = self.randident("ro")
  ro_pwd = self.randident
  @readonly_url = self._create_conn_url(ro_user, ro_pwd, superuser_uri, @org.dbname)
  lines = [
    "ALTER ROLE #{orig_readonly_user} RENAME TO #{ro_user};",
    "ALTER ROLE #{ro_user} WITH PASSWORD '#{ro_pwd}';",
  ]
  if self.class.isolate?(DATABASE)
    # Roll admin credentials for a separate database.
    # For schema isolation, we assume admin is the superuser so cannot roll creds.
    orig_admin_user = URI(@org.admin_connection_url_raw).user
    admin_user = self.randident("ad")
    admin_pwd = self.randident
    lines.push(
      "ALTER ROLE #{orig_admin_user} RENAME TO #{admin_user};",
      "ALTER ROLE #{admin_user} WITH PASSWORD '#{admin_pwd}';",
    )
    @admin_url = self._create_conn_url(admin_user, admin_pwd, superuser_uri, @org.dbname)
  else
    @admin_url = @org.admin_connection_url_raw
  end
  # New conn so we don't log it
  borrow_conn(superuser_uri.to_s, loggers: []) do |conn|
    conn << lines.join("\n")
  end
end