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
Public Class Methods
Creates a new instance.
-
config
- Configuration hash to pass toSequel.connect
. -
args
- Arguments to pass to Potluck::Service.new (optional).
# File lib/potluck/postgres.rb, line 49 def initialize(config, **args) super(**args) @config = config end
Content of the launchctl plist file.
# 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
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
Disconnects from the database if a connection was made.
# File lib/potluck/postgres.rb, line 111 def disconnect @database&.disconnect end
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
Disconnects and stops the Postgres
process.
# File lib/potluck/postgres.rb, line 58 def stop disconnect super end
Private Instance Methods
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
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
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
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