Skip to content

Commit

Permalink
Connect to PostgreSQL with psql, allowing peer authentication
Browse files Browse the repository at this point in the history 
Shelling out also negates the need to install the pg gem and, by
extension, the build-essential packages.
  • Loading branch information
@chewi
chewi committed Jun 18, 2014
1 parent 2b6473d commit 9a2da4d
Show file tree
Hide file tree
Showing 4 changed files with 69 additions and 124 deletions.
101 changes: 44 additions & 57 deletions libraries/provider_database_postgresql.rb
View file
Expand Up @@ -26,73 +26,55 @@ class Postgresql < Chef::Provider
include Chef::Mixin::ShellOut

def load_current_resource
Gem.clear_paths
require 'pg'
@current_resource = Chef::Resource::Database.new(@new_resource.name)
@current_resource.database_name(@new_resource.database_name)
@current_resource
end

def action_create
unless exists?
begin
encoding = @new_resource.encoding
if encoding != 'DEFAULT'
encoding = "'#{@new_resource.encoding}'"
end
Chef::Log.debug("#{@new_resource}: Creating database #{new_resource.database_name}")
create_sql = "CREATE DATABASE \"#{new_resource.database_name}\""
create_sql += " TEMPLATE = #{new_resource.template}" if new_resource.template
create_sql += " ENCODING = #{encoding}" if new_resource.encoding
create_sql += " TABLESPACE = #{new_resource.tablespace}" if new_resource.tablespace
create_sql += " LC_CTYPE = '#{new_resource.collation}' LC_COLLATE = '#{new_resource.collation}'" if new_resource.collation
create_sql += " CONNECTION LIMIT = #{new_resource.connection_limit}" if new_resource.connection_limit
create_sql += " OWNER = \"#{new_resource.owner}\"" if new_resource.owner
Chef::Log.debug("#{@new_resource}: Performing query [#{create_sql}]")
db('template1').query(create_sql)
@new_resource.updated_by_last_action(true)
ensure
close
encoding = @new_resource.encoding
if encoding != 'DEFAULT'
encoding = "'#{@new_resource.encoding}'"
end
Chef::Log.debug("#{@new_resource}: Creating database #{new_resource.database_name}")
create_sql = "CREATE DATABASE \"#{new_resource.database_name}\""
create_sql += " TEMPLATE = #{new_resource.template}" if new_resource.template
create_sql += " ENCODING = #{encoding}" if new_resource.encoding
create_sql += " TABLESPACE = #{new_resource.tablespace}" if new_resource.tablespace
create_sql += " LC_CTYPE = '#{new_resource.collation}' LC_COLLATE = '#{new_resource.collation}'" if new_resource.collation
create_sql += " CONNECTION LIMIT = #{new_resource.connection_limit}" if new_resource.connection_limit
create_sql += " OWNER = \"#{new_resource.owner}\"" if new_resource.owner
Chef::Log.debug("#{@new_resource}: Performing query [#{create_sql}]")
db(create_sql, 'template1')
@new_resource.updated_by_last_action(true)
end
end

def action_drop
if exists?
begin
Chef::Log.debug("#{@new_resource}: Dropping database #{new_resource.database_name}")
db('template1').query("DROP DATABASE \"#{new_resource.database_name}\"")
@new_resource.updated_by_last_action(true)
ensure
close
end
Chef::Log.debug("#{@new_resource}: Dropping database #{new_resource.database_name}")
db("DROP DATABASE \"#{new_resource.database_name}\"", 'template1')
@new_resource.updated_by_last_action(true)
end
end

def action_query
if exists?
begin
Chef::Log.debug("#{@new_resource}: Performing query [#{new_resource.sql_query}]")
db(@new_resource.database_name).query(@new_resource.sql_query)
Chef::Log.debug("#{@new_resource}: query [#{new_resource.sql_query}] succeeded")
@new_resource.updated_by_last_action(true)
ensure
close
end
Chef::Log.debug("#{@new_resource}: Performing query [#{new_resource.sql_query}]")
db(@new_resource.sql_query, @new_resource.database_name)
Chef::Log.debug("#{@new_resource}: query [#{new_resource.sql_query}] succeeded")
@new_resource.updated_by_last_action(true)
end
end

private

def exists?
begin
Chef::Log.debug("#{@new_resource}: checking if database #{@new_resource.database_name} exists")
ret = db('template1').query("SELECT * FROM pg_database where datname = '#{@new_resource.database_name}'").num_tuples != 0
ret ? Chef::Log.debug("#{@new_resource}: database #{@new_resource.database_name} exists") :
Chef::Log.debug("#{@new_resource}: database #{@new_resource.database_name} does not exist")
ensure
close
end
Chef::Log.debug("#{@new_resource}: checking if database #{@new_resource.database_name} exists")
ret = db("SELECT * FROM pg_database where datname = '#{@new_resource.database_name}'", 'template1').size != 0
ret ? Chef::Log.debug("#{@new_resource}: database #{@new_resource.database_name} exists") :
Chef::Log.debug("#{@new_resource}: database #{@new_resource.database_name} does not exist")
ret
end

Expand All @@ -103,26 +85,31 @@ def exists?
# - action_query will use the resource database_name.
# - specifying a database in the connection will override this behavior
#
def db(dbname = nil)
close if @db
def db(query, dbname = @new_resource.database_name)
dbname = @new_resource.connection[:database] if @new_resource.connection[:database]
host = @new_resource.connection[:host]
port = @new_resource.connection[:port] || 5432
user = @new_resource.connection[:username] || 'postgres'
Chef::Log.debug("#{@new_resource}: connecting to database #{dbname} on #{host}:#{port} as #{user}")
password = @new_resource.connection[:password] || node[:postgresql][:password][:postgres]
@db = ::PGconn.new(
:host => host,
:port => port,
:dbname => dbname,
:user => user,
:password => password
)
end

def close
@db.close rescue nil
@db = nil
password = @new_resource.connection.fetch(:password) do
node['postgresql']['password']['postgres'] if user == 'postgres'
end

args = ['psql', '-w', '-t', '-A', '-R', "\x1e", '-F', "\x1f", '-c', query, '-p', port.to_s, '-U', user]
args.concat ['-h', host] unless host.nil? or host.empty?
args.push dbname unless dbname.nil? or dbname.empty?

# Try to use peer authentication if possible.
if (host.nil? or host[0] == '/') and password.nil? and node['platform'] != 'windows'
options = { :user => user }
else
# TODO: Use a .pgpass file.
options = { :environment => { "PGPASSWORD" => password } }
end

so = shell_out! *args, options
so.stdout.chomp.split("\x1e").map { |r| r.split("\x1f") }
end
end
end
Expand Down
33 changes: 8 additions & 25 deletions libraries/provider_database_postgresql_schema.rb
View file
Expand Up @@ -22,50 +22,33 @@ class Chef
class Provider
class Database
class PostgresqlSchema < Chef::Provider::Database::Postgresql
include Chef::Mixin::ShellOut

def load_current_resource
Gem.clear_paths
require 'pg'
@current_resource = Chef::Resource::PostgresqlDatabaseSchema.new(@new_resource.name)
@current_resource.schema_name(@new_resource.schema_name)
@current_resource
end

def action_create
unless exists?
begin
if new_resource.owner
db(@new_resource.database_name).query("CREATE SCHEMA \"#{@new_resource.schema_name}\" AUTHORIZATION \"#{@new_resource.owner}\"")
else
db(@new_resource.database_name).query("CREATE SCHEMA \"#{@new_resource.schema_name}\"")
end
@new_resource.updated_by_last_action(true)
ensure
close
if new_resource.owner
db("CREATE SCHEMA \"#{@new_resource.schema_name}\" AUTHORIZATION \"#{@new_resource.owner}\"")
else
db("CREATE SCHEMA \"#{@new_resource.schema_name}\"")
end
@new_resource.updated_by_last_action(true)
end
end

def action_drop
if exists?
begin
db(@new_resource.database_name).query("DROP SCHEMA \"#{@new_resource.schema_name}\"")
@new_resource.updated_by_last_action(true)
ensure
close
end
db("DROP SCHEMA \"#{@new_resource.schema_name}\"")
@new_resource.updated_by_last_action(true)
end
end

private
def exists?
begin
exists = db(@new_resource.database_name).query("SELECT schema_name FROM information_schema.schemata WHERE schema_name='#{@new_resource.schema_name}'").num_tuples != 0
ensure
close
end
exists
db("SELECT schema_name FROM information_schema.schemata WHERE schema_name='#{@new_resource.schema_name}'").size != 0
end
end
end
Expand Down
57 changes: 16 additions & 41 deletions libraries/provider_database_postgresql_user.rb
View file
Expand Up @@ -24,71 +24,46 @@ class Chef
class Provider
class Database
class PostgresqlUser < Chef::Provider::Database::Postgresql
include Chef::Mixin::ShellOut

def load_current_resource
Gem.clear_paths
require 'pg'
@current_resource = Chef::Resource::DatabaseUser.new(@new_resource.name)
@current_resource.username(@new_resource.name)
@current_resource
end

def action_create
unless exists?
begin
statement = "CREATE USER \"#{@new_resource.username}\""
statement += " WITH PASSWORD '#{@new_resource.password}'" if @new_resource.password
db('template1').query(statement)
@new_resource.updated_by_last_action(true)
ensure
close
end
statement = "CREATE USER \"#{@new_resource.username}\""
statement += " WITH PASSWORD '#{@new_resource.password}'" if @new_resource.password
db(statement, 'template1')
@new_resource.updated_by_last_action(true)
end
end

def action_drop
if exists?
begin
db('template1').query("DROP USER \"#{@new_resource.username}\"")
@new_resource.updated_by_last_action(true)
ensure
close
end
db("DROP USER \"#{@new_resource.username}\"", 'template1')
@new_resource.updated_by_last_action(true)
end
end

def action_grant
begin
# FIXME: grants on individual tables
grant_statement = "GRANT #{@new_resource.privileges.join(', ')} ON DATABASE \"#{@new_resource.database_name}\" TO \"#{@new_resource.username}\""
Chef::Log.info("#{@new_resource}: granting access with statement [#{grant_statement}]")
db(@new_resource.database_name).query(grant_statement)
@new_resource.updated_by_last_action(true)
ensure
close
end
# FIXME: grants on individual tables
grant_statement = "GRANT #{@new_resource.privileges.join(', ')} ON DATABASE \"#{@new_resource.database_name}\" TO \"#{@new_resource.username}\""
Chef::Log.info("#{@new_resource}: granting access with statement [#{grant_statement}]")
db(grant_statement)
@new_resource.updated_by_last_action(true)
end

def action_grant_schema
begin
grant_statement = "GRANT #{@new_resource.privileges.join(', ')} ON SCHEMA \"#{@new_resource.schema_name}\" TO \"#{@new_resource.username}\""
Chef::Log.info("#{@new_resource}: granting access with statement [#{grant_statement}]")
db(@new_resource.database_name).query(grant_statement)
@new_resource.updated_by_last_action(true)
ensure
close
end
grant_statement = "GRANT #{@new_resource.privileges.join(', ')} ON SCHEMA \"#{@new_resource.schema_name}\" TO \"#{@new_resource.username}\""
Chef::Log.info("#{@new_resource}: granting access with statement [#{grant_statement}]")
db(grant_statement)
@new_resource.updated_by_last_action(true)
end

private
def exists?
begin
exists = db('template1').query("SELECT * FROM pg_user WHERE usename='#{@new_resource.username}'").num_tuples != 0
ensure
close
end
exists
db("SELECT * FROM pg_user WHERE usename='#{@new_resource.username}'", 'template1').size != 0
end
end
end
Expand Down
2 changes: 1 addition & 1 deletion recipes/postgresql.rb
View file
Expand Up @@ -17,4 +17,4 @@
# limitations under the License.
#

include_recipe 'postgresql::ruby'
include_recipe 'postgresql::client'

0 comments on commit 9a2da4d

Please sign in to comment.