Skip to content

Instantly share code, notes, and snippets.

@rietta rietta/plpgsql.rake
Last active Jun 26, 2019

Embed
What would you like to do?
Are you using PostgreSQL and don't want to make your app run as PostgreSQL super user, then add this custom rake task to your `lib/tasks` folder and be happy.
#
# PostgreSQL writes two optional commands to the database schema
# file, called db/structure.sql, that can only be run as a root
# database user. These are not needed actually, so comment them
# out automatically
#
# CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
# COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
#
namespace :db do
namespace :structure do
desc 'Comment out the plpgsql lines from structure.sql so that a non-root user can create the test database'
task :fix_plpgsql do
lines_to_strike = [
'CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;',
"COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';",
]
schema_file = File.join(File.dirname(__FILE__), '..', '..', 'db', 'structure.sql')
if File.exist?(schema_file) and (input = File.open(schema_file))
# Create a temp file, read through the original, commenting out the target lines.
lines = Array.new
line_count = 0
input.each_line do |line|
if line
line_count += 1
if lines_to_strike.include?(line.strip)
lines << "-- The following was commented out by rake db:structure:fix_plpgsql\n"
lines << '-- ' + line
else
lines << line
end
end
end # each
input.close
if lines.count > line_count
# Lines were commented out, so write the new content to the file
File.write(schema_file, lines.join)
else
# No lines were commented out, so there is no need to rewrite the file
STDERR.puts "No changes are needed to #{schema_file}, it's left unchanged."
end
end
end # task
end # namespace
end # namespace
# Inform Rake that this should be run every time rake db:structure:dump is run
Rake::Task['db:structure:dump'].enhance do
Rake::Task['db:structure:fix_plpgsql'].invoke
end
@rietta

This comment has been minimized.

Copy link
Owner Author

rietta commented Dec 10, 2013

I was tired of the error:
db/structure.sql:23: ERROR: must be owner of extension plpgsql

The standard suggestion is to make the PostgreSQL user that the application is using to have superuser role, with
ALTER ROLE app_user_name with SUPERUSER;

I think that is a terrible idea as it violates the principle of least privilege!

@ivar

This comment has been minimized.

Copy link

ivar commented Jul 10, 2014

Thanks - this is great

@jatinganhotra

This comment has been minimized.

Copy link

jatinganhotra commented Jan 14, 2015

This is great. Thanks.

@pboling

This comment has been minimized.

Copy link

pboling commented Oct 12, 2015

Excellent 👍

@ritec

This comment has been minimized.

Copy link

ritec commented Jun 26, 2019

Hi there @rietta! Under Postgres 11.2 if you do a rake db:structure:dump these lines seem to be removed automatically. I recently upgraded from 9.6 to 11.2 and upon generating the new structure files with the new version of the DB the lines are removed. Very interesting, wondering if you have any input about that. I do have the plpgsql extension installed.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.