pgbedrock

https://pgbedrock.readthedocs.io/en/latest/
Admin
pgbedrock is an application for managing roles, memberships, ownerships, and permissions for tables,...
Tokens:6,837
Snippets:50
Trust Score:9.6
Update:1 month ago
Show doc for...
Tokens:
Raw
### Set up local development environment

Source: https://pgbedrock.readthedocs.io/en/latest/development.html

Commands to create a virtual environment and install the necessary dependencies for development.

```bash
mkvirtualenv pgbedrock3 --python python3
pip3 install -e . -r requirements-dev.txt -r requirements-publish.txt
```

--------------------------------

### Install psycopg2 dependencies on macOS

Source: https://pgbedrock.readthedocs.io/en/latest/development.html

Commands to resolve potential installation failures for psycopg2 by installing system-level dependencies.

```bash
brew install postgresql openssl
xcode-select --install
```

```bash
LDFLAGS="-L$(brew --prefix openssl)/lib" pip3 install psycopg2
```

--------------------------------

### Example role definition in spec.yml

Source: https://pgbedrock.readthedocs.io/en/latest/spec_overview.html

Shows a complete role definition with all optional fields: can_login, is_superuser, attributes, member_of, owns, and privileges. All items except the role name are optional.

```yaml
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.*
        sequences:
            write:
                - reports.*

```

--------------------------------

### Owns keyword example

Source: https://pgbedrock.readthedocs.io/en/latest/spec_overview.html

Shows the owns section for a role, listing schemas, sequences, and tables owned by the role. The example uses the 'analyst' role.

```yaml
analyst:
    owns:
        schemas:
            - finance
        sequences:
            - finance.*
        tables:
            - finance.*
            - marketing.ad_spend

```

--------------------------------

### Example Role Definition in YAML Spec

Source: https://pgbedrock.readthedocs.io/en/latest/index.html

A sample YAML configuration for a role named 'jdoe', defining login capabilities, attributes, group memberships, ownerships, and specific schema, table, and sequence privileges.

```yaml
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.*
        sequences:
            write:
                - reports.*
```

--------------------------------

### Privileges keyword example

Source: https://pgbedrock.readthedocs.io/en/latest/spec_overview.html

Shows the privileges section for a group role 'analyst' with read and write access on schemas, tables, and sequences. Note that 'foo.*' is a shorthand for all objects in a schema, not a regex.

```yaml
analyst:
    can_login: no
    privileges:
        schemas:
            read:
                - finance
                - marketing
            write:
                - reports
        tables:
            read:
                - finance.*
                - marketing.*
            write:
                - reports.*

```

--------------------------------

### Run pgbedrock configure via Docker with check mode and selected submodules

Source: https://pgbedrock.readthedocs.io/en/latest/making_configuration_changes.html

Runs pgbedrock configure in a Docker container, using environment variables for templated passwords, mounting a spec file, and enabling check mode with --check. The --prompt flag triggers an interactive password prompt, and --attributes and --memberships limit the submodules run. The role provided with -U must be a superuser. Check mode is the default, but --live commits changes.

```bash
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 \
    --prompt \
    --check \
    --attributes \
    --memberships \
    --no-ownerships \
    --no-privileges
```

--------------------------------

### Minimal role definition with defaults

Source: https://pgbedrock.readthedocs.io/en/latest/spec_overview.html

Creates a role named 'foo' with all default settings. Only the role name is required; all other fields are optional.

```yaml
foo:

```

--------------------------------

### Generate a spec with Docker

Source: https://pgbedrock.readthedocs.io/en/latest/generating_a_spec.html

Runs pgbedrock generate in a Docker container, connecting to a database and writing the spec to path/to/spec.yml. The command prints results to STDOUT, so the output is redirected to a file. Requires Docker and the quay.io/squarespace/pgbedrock image.

```bash
docker run -it \
    quay.io/squarespace/pgbedrock generate \
    -h myhost.mynetwork.net \
    -p 5432 \
    -d mydatabase \
    -U mysuperuser \
    -w supersecret > path/to/spec.yml
```

--------------------------------

### Run test suite commands

Source: https://pgbedrock.readthedocs.io/en/latest/development.html

Common make and pytest commands used to verify functionality and check coverage.

```bash
make test
pytest
make coverage
```

--------------------------------

### Push a new git tag for release

Source: https://pgbedrock.readthedocs.io/en/latest/development.html

Commands to create a tagged release using a multi-line message from a file.

```bash
git tag x.x.x -F tag_message
git push --tags origin master
```

--------------------------------

### Release to PyPI and Quay

Source: https://pgbedrock.readthedocs.io/en/latest/development.html

Commands to trigger the release process for PyPI and Quay container registry.

```bash
make release-pypi
make release-quay
```

--------------------------------

### Review the spec in check mode

Source: https://pgbedrock.readthedocs.io/en/latest/index.html

Runs pgbedrock in check mode to preview changes without modifying the database. Passwords for roles managed within Postgres must be provided via environment variables.

```bash
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 \
    --check
```

--------------------------------

### Define role with password from environment variable

Source: https://pgbedrock.readthedocs.io/en/latest/spec_overview.html

Use this YAML snippet in a pgbedrock spec template to set a role's password from an environment variable. The variable name is arbitrary, but it must exist in the environment or pgbedrock will refuse to run and report the missing variable name. When running via Docker, pass the variable using the -e flag.

```yaml
myrole:
    attributes:
        - PASSWORD "{{ env['MYROLE_PASSWORD'] }}"

```
Tokens:
Raw