Posgra is a tool to manage PostgreSQL roles/permissions.
It defines the state of PostgreSQL roles/permissions using Ruby DSL, and updates roles/permissions according to DSL.
Add this line to your application's Gemfile:
gem 'posgra'And then execute:
$ bundle
Or install it yourself as:
$ gem install posgra
$ posgra help
Commands:
posgra database SUBCOMMAND # Manage database grants
posgra grant SUBCOMMAND # Manage grants
posgra help [COMMAND] # Describe available commands or one specific command
posgra role SUBCOMMAND # Manage roles
Options:
-h, [--host=HOST]
# Default: localhost
-p, [--port=N]
# Default: 5432
-d, [--dbname=DBNAME]
# Default: postgres
-U, [--user=USER]
-P, [--password=PASSWORD]
[--account-output=ACCOUNT-OUTPUT]
# Default: account.csv
[--color], [--no-color]
# Default: true
[--debug], [--no-debug]A default connection to a database can be established by setting the following environment variables:
POSGRA_DB_HOST: database hostPOSGRA_DB_PORT: database portPOSGRA_DB_DATABASE: database database namePOSGRA_DB_USER: database userPOSGRA_DB_PASSWORD: database password
posgra role export pg_roles.rb
vi pg_roles.rb
posgra role apply --dry-run pg_roles.rb
posgra role apply pg_roles.rbposgra grant export pg_grants.rb
vi pg_grants.rb
posgra grant apply --dry-run pg_grants.rb
posgra grant apply pg_grants.rbposgra database export pg_dbgrants.rb
vi pg_dbgrants.rb
posgra database apply --dry-run pg_dbgrants.rb
posgra database apply pg_dbgrants.rbexport POSGRA_DEFAULT_ACL_PRIVS=arwdRxt
posgra grant export pg_grants.rbuser "alice"
group "staff" do
user "bob"
endrole "bob" do
schema "main" do
on "microposts" do
grant "DELETE", grantable: true
grant "INSERT"
grant "REFERENCES"
grant "SELECT"
grant "TRIGGER"
grant "TRUNCATE"
grant "UPDATE"
end
on "microposts_id_seq", expired: '2014/10/07' do
grant "SELECT"
grant "UPDATE"
end
on /^user/ do
grant "SELECT"
end
end
endrole "alice" do
database "my_database" do
grant "CONNECT", :grantable => true
grant "CREATE"
grant "TEMPORARY"
end
end
role "bob" do
database "my_database" do
grant "CONNECT"
grant "CREATE"
grant "TEMPORARY"
end
endtemplate "all grants" do
on context.object do
grant "DELETE", grantable: true
grant "INSERT"
grant "REFERENCES"
grant "SELECT"
grant "TRIGGER"
grant "TRUNCATE"
grant "UPDATE"
end
end
template "grant select" do
grant "SELECT"
end
role "bob" do
schema "main" do
include_template "all grants", object: "microposts"
on "microposts_id_seq", expired: '2014/10/07' do
grant "SELECT"
grant "UPDATE"
end
on /^user/ do
include_template "grant select"
end
end
enddocker-compose up -d
bundle install
bundle exec rakePort forwarding is required.
VBoxManage controlvm default natpf1 "psql,tcp,127.0.0.1,5432,,5432"