Squarespace / pgbedrock

Manage a Postgres cluster's roles, role memberships, schema ownership, and privileges
https://pgbedrock.readthedocs.io/en/latest/
Other
316 stars 35 forks source link

Add functions to ownership list #30

Open clrcrl opened 6 years ago

clrcrl commented 6 years ago

Functions, like tables and schemas, have an owner, which should also be managed by pgbedrock.

For example

jdoe:
    ...
    owns:
        schemas:
            - finance_reports
        tables:
            - finance_reports.Q2_revenue
            - finance_reports.Q2_margin
        functions:
            - f_cast_string_as_number
zcmarine commented 6 years ago

Definitely! I think this should be quite doable, though if we handle ownership for functions we should also handle permissions for them as well, though that is probably two separate PRs as those ideas don't have to be linked (although the privilege map mentioned below in Ownership point 1 is used on both parts and might force them to be more coupled).

Below is a brief overview on what would likely need to be done. If you have any interest in taking a crack at it let me know! Otherwise this will be on the roadmap but may be prioritized below a few other issues like #3 (Provide multi-database support).

Adding function ownership: There are three places that we would need to modify:

  1. Context.py would need two changes: The query pulls in ownerships (here) would need to take functions in as well and the privilege map (here) would need to have functions in it.
  2. We'd want to assert via tests that the spec_inspector.py is properly checking a real function. The relevant checks in that module that should catch if someone is off in the spec are here.
  3. We'd want to assert via tests that ownership.py properly works with a real function. This should probably be done for analyze_ownerships() (and could just be an in-place modification of an existing test) and possibly a test for NonschemaAnalyzer specifically.

Adding function privileges:

  1. Modify the context.py queries that get default and non-default privileges.
  2. Add tests verifying that privileges.py operates correctly over default and non-default privileges for a real function. Again, these could be in-place modifications of tests or duplicates of tests.

It may sound like a lot, but other than getting the queries right and getting comfortable with how pgbedrock works, it shouldn't be too difficult as I think pgbedrock's machinery is generalized enough that once the queries are correct the rest of the code should just work (though emphasis on "should", of course).