class Potluck::Postgres

A Ruby interface for controlling and connecting to Postgres. Uses [Sequel](github.com/jeremyevans/sequel) to connect and perform automatic role and database creation, as well as for utility methods such as database schema migration.

Constants

CONNECTION_REFUSED_STRING
CONNECTION_REFUSED_TIMEOUT
DATABASE_NOT_FOUND_REGEX
ROLE_NOT_FOUND_REGEX
STARTING_UP_STRING
STARTING_UP_TIMEOUT

Attributes

database[R]

Public Class Methods

new(config, **args) click to toggle source

Creates a new instance.

  • config - Configuration hash to pass to Sequel.connect.

  • args - Arguments to pass to Potluck::Service.new (optional).

Calls superclass method
# File lib/potluck/postgres.rb, line 49
def initialize(config, **args)
  super(**args)

  @config = config
end
plist() click to toggle source

Content of the launchctl plist file.

Calls superclass method
# File lib/potluck/postgres.rb, line 160
    def self.plist
      super(
        <<~EOS
          <key>ProgramArguments</key>
          <array>
            <string>/usr/local/opt/postgresql/bin/postgres</string>
            <string>-D</string>
            <string>/usr/local/var/postgres</string>
          </array>
          <key>WorkingDirectory</key>
          <string>/usr/local</string>
          <key>StandardOutPath</key>
          <string>/usr/local/var/log/postgres.log</string>
          <key>StandardErrorPath</key>
          <string>/usr/local/var/log/postgres.log</string>
        EOS
      )
    end

Public Instance Methods

connect() click to toggle source

Connects to the configured Postgres database.

# File lib/potluck/postgres.rb, line 66
def connect
  role_created = false
  database_created = false

  begin
    (tries ||= 0) && (tries += 1)
    @database = Sequel.connect(@config, logger: @logger)
  rescue Sequel::DatabaseConnectionError => e
    if (dud = Sequel::DATABASES.last)
      dud.disconnect
      Sequel.synchronize { Sequel::DATABASES.delete(dud) }
    end

    message = e.message.downcase

    if message =~ ROLE_NOT_FOUND_REGEX && !role_created && manage?
      role_created = true
      create_role
      retry
    elsif message =~ DATABASE_NOT_FOUND_REGEX && !database_created && manage?
      database_created = true
      create_database
      retry
    elsif message.include?(STARTING_UP_STRING) && tries < STARTING_UP_TIMEOUT
      sleep(1)
      retry
    elsif message.include?(CONNECTION_REFUSED_STRING) && tries < CONNECTION_REFUSED_TIMEOUT
      sleep(1)
      retry
    elsif message.include?(CONNECTION_REFUSED_STRING)
      raise(PostgresError.new(e.message.strip, e))
    else
      raise
    end
  end

  # Only grant permissions if the database already existed but the role did not. Automatic database
  # creation (via #create_database) is performed as the configured role, which means explicit permission
  # granting is not necessary.
  grant_permissions if role_created && !database_created
end
disconnect() click to toggle source

Disconnects from the database if a connection was made.

# File lib/potluck/postgres.rb, line 111
def disconnect
  @database&.disconnect
end
migrate(dir, steps = nil) click to toggle source

Runs database migrations by way of Sequel’s migration extension. Migration files must use the timestamp naming strategy as opposed to integers.

  • dir - Directory where migration files are located.

  • steps - Number of steps forward or backward to migrate from the current migration, otherwise will migrate to latest (optional).

# File lib/potluck/postgres.rb, line 123
def migrate(dir, steps = nil)
  return unless File.directory?(dir)

  Sequel.extension(:migration)

  # Suppress Sequel schema migration table queries.
  original_level = @logger.level
  @logger.level = Logger::WARN if @logger.level == Logger::INFO

  args = [@database, dir, {allow_missing_migration_files: true}]
  migrator = Sequel::TimestampMigrator.new(*args)

  return if migrator.files.empty?

  if steps
    all = migrator.files.map { |f| File.basename(f) }
    applied = migrator.applied_migrations
    current = applied.last

    return if applied.empty? && steps <= 0

    index = [[0, (all.index(current) || -1) + steps].max, all.size].min
    file = all[index]

    args.last[:target] = migrator.send(:migration_version_from_file, file)
  end

  migrator = Sequel::TimestampMigrator.new(*args)
  @logger.level = original_level
  migrator.run
ensure
  @logger.level = original_level if original_level
end
stop() click to toggle source

Disconnects and stops the Postgres process.

Calls superclass method
# File lib/potluck/postgres.rb, line 58
def stop
  disconnect
  super
end

Private Instance Methods

admin_database_config() click to toggle source

Returns a configuration hash for connecting to Postgres to perform administrative tasks (i.e. role and database creation). Uses the system user as the username and no password.

# File lib/potluck/postgres.rb, line 244
def admin_database_config
  config = @config.dup
  config[:username] = ENV['USER']
  config[:password] = nil

  config
end
create_database() click to toggle source

Attempts to connect to the ‘postgres’ database with the configured user and password and create the configured database. Useful in development.

# File lib/potluck/postgres.rb, line 205
def create_database
  tmp_config = @config.dup
  tmp_config[:database] = 'postgres'

  begin
    Sequel.connect(tmp_config, logger: @logger) do |database|
      database.execute("CREATE DATABASE \"#{@config[:database]}\"")
    end
  rescue => e
    raise(PostgresError.new("Failed to create database #{@config[:database].inspect} by connecting to "\
      "database #{tmp_config[:database].inspect} as role #{tmp_config[:username].inspect}. "\
      'Please create the database manually.', e))
  end
end
create_role() click to toggle source

Attempts to connect to the ‘postgres’ database as the system user with no password and create the configured role. Useful in development.

# File lib/potluck/postgres.rb, line 185
def create_role
  tmp_config = admin_database_config
  tmp_config[:database] = 'postgres'

  begin
    Sequel.connect(tmp_config, logger: @logger) do |database|
      database.execute("CREATE ROLE \"#{@config[:username]}\" WITH LOGIN CREATEDB REPLICATION"\
        "#{" PASSWORD '#{@config[:password]}'" if @config[:password]}")
    end
  rescue => e
    raise(PostgresError.new("Failed to create database role #{@config[:username].inspect} by "\
      "connecting to database #{tmp_config[:database].inspect} as role "\
      "#{tmp_config[:username].inspect}. Please create the role manually.", e))
  end
end
grant_permissions() click to toggle source

Grants appropriate permissions for the configured database role.

# File lib/potluck/postgres.rb, line 223
def grant_permissions
  tmp_config = admin_database_config

  begin
    Sequel.connect(tmp_config, logger: @logger) do |db|
      db.execute("GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO \"#{@config[:username]}\"")
      db.execute("GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO \"#{@config[:username]}\"")
      db.execute("ALTER DEFAULT PRIVILEGES FOR ROLE \"#{@config[:username]}\" IN SCHEMA public GRANT "\
        "ALL PRIVILEGES ON TABLES TO \"#{@config[:username]}\"")
    end
  rescue => e
    raise(PostgresError.new("Failed to grant database permissions for role "\
      "#{@config[:username].inspect} by connecting as role #{tmp_config[:username].inspect}. Please "\
      'grant appropriate permissions manually.', e))
  end
end