gem-hunter
Language: Python
Manage a Postgres cluster's roles, role memberships, schema ownership, and privileges
pgbedrock is an application for managing the roles, memberships, ownerships, and most importantly the permissions for tables, sequences, and schemas in a Postgres database.
Given the parameters to connect to a Postgres database (i.e. host, port, etc.) and a YAML file (a "spec") representing the desired database configuration, pgbedrock makes sure that the configuration of that database matches the spec. If there are differences, it will alter the database to make it match the spec.
It can be run as a docker container (via docker run quay.io/squarespace/pgbedrock) or
as a local command-line utility (via pip install pgbedrock).
Detailed information can be found in the documentation.
As an example, the definition for the jdoe role in the spec might look like this:
jdoe:
can_login: yes
is_superuser: no
attributes:
- PASSWORD "{{ env['JDOE_PASSWORD'] }}"
member_of:
- analyst
owns:
schemas:
- finance_reports
tables:
- finance_reports.Q2_revenue
- finance_reports.Q2_margin
privileges:
schemas:
read:
- finance
- marketing
write:
- reports
tables:
read:
- finance.*
- marketing.ad_spend
- marketing.impressions
write:
- reports.*
except:
- reports.Q2_fixed_assets
sequences:
write:
- reports.*When pgbedrock is run, it would make sure that:
- The role
jdoeexistsjdoecan log injdoeis not a superuserjdoe's password is the same as what is in the$JDOE_PASSWORDenvironment variable- All other role attributes for
jdoeare the Postgres defaults (as defined by pg_authid).jdoeis a member of theanalystrolejdoeis a member of no other rolesjdoeowns thefinance_reportsschemajdoeowns thefinance_reports.Q2_revenueandfinance_reports.Q2_margintablesjdoehas read-level schema access (in Postgres terms:USAGE) for thefinanceandmarketingschemasjdoehas write-level schema access (CREATE) for thereportsschemajdoehas read-level access (SELECT) to all tables in thefinanceschema and to themarketing.ad_spendandmarketing.impressionstablesjdoehas default privileges to read from all future tables created in thefinanceschemajdoehas write-level access (SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES, andTRIGGER) to all tables in thereportsschema except for theQ2_fixed_assetstablejdoehas default privileges to write to all future tables created in thereportsschemajdoehas write-level access (SELECT,USAGE,UPDATE) to all sequences in thereportsschemajdoehas default privileges to write to all future sequences created in thereportsschemajdoedoes not have any access other than that listed above (except whatever it inherits from theanalystrole thatjdoeis a member of)
Using pgbedrock requires three steps: generating a spec for a database, reviewing that spec, and configuring the database using that spec. Below we will do this using the pgbedrock docker image, but these steps can also be done with the pip-installed version of the tool.
Generate a spec for a database. Specify the connection parameters below (host, port,
database, username, and user password) as well as the place to output the tentative spec. Note
that the user passed with -U must be a superuser.
docker run -it \
quay.io/squarespace/pgbedrock generate \
-h myhost.mynetwork.net \
-p 5432 \
-d mydatabase \
-U mysuperuser \
-w supersecret > path/to/spec.ymlReview the spec. pgbedrock is not quite as flexible as Postgres's permissioning, and as a
result the generated spec may differ slightly from the current state of your database. For more
information on these potential simplifications, see the Notable Functionality And Caveats
section in the docs. As a result, it is recommended to run pgbedrock configure in check mode
the first time you use it to see what changes it would introduce to your current setup. This
looks similar to the command above, but requires us to also pass in the passwords for any roles
whose passwords are managed within Postgres itself. These can be identified in the spec file as
roles with a line that looks like PASSWORD "{{ env['MYROLE_PASSWORD'] }}" (if you forget to
pass in these passwords pgbedrock will just throw an error and refuse to run). Note that you must
run pgbedrock configure against the Postgres primary. To run pgbedrock in check mode we do
the following:
docker run -it \
-e "JDOE_PASSWORD=${JDOE_PASSWORD}" \
-e "JSMITH_PASSWORD=${JSMITH_PASSWORD}" \
-v /path/to/spec.yml:/opt/spec.yml \
quay.io/squarespace/pgbedrock configure spec.yml \
-h myhost.mynetwork.net \
-p 5432 \
-d mydatabase \
-U mysuperuser \
-w supersecret \
--checkNote that --check is actually the default behavior, so we could also omit that.
Configure the database using the spec. Once you feel comfortable with the changes pgbedrock
would introduce, run the above command again using --live instead of --check. Changes
will now be made real. To make future changes, modify the spec file and run the above command.
For further information, see the documentation.
Copyright 2018 Squarespace, Inc.
Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at:
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.