Python utility function to ensure that a PostgreSQL role has certain permissions or role memberships
Project description
pg-sync-roles
Python utility function to ensure that a PostgreSQL role has certain permissions or role memberships
[!WARNING]
Work in progress. This README serves as a rough design spec.
Features
- Transparently handles high numbers of permissions - avoiding "row is too big" errors.
- Locks where necessary - working around "tuple concurrently updated" or "tuple concurrently deleted" errors" that can happen when permission changes are performed concurrently.
- Optionally removes permissions from roles
- Handles database connect, schema usage, table select permissions, and role memberships - typically useful when using PostgreSQL as a data warehouse with a high number of users that need granular permissions.
Installation
pg-sync-roles can be installed from PyPI using pip. psycopg2 or psycopg (Psycopg 3) must also be explicitly installed.
pip install pg-sync-roles psycopg
Usage
To give a user CONNECT privileges on a database, as well as membership of role:
from pg_sync_roles import DatabaseConnect, RoleMembership, pg_sync_roles
# For example purposes, PostgreSQL can be run locally using this...
# docker run --rm -it -e POSTGRES_HOST_AUTH_METHOD=trust -p 5432:5432 postgres
# ... which should work with this engine
engine = sa.create_engine('postgresql+psycopg://postgres@127.0.0.1:5432/')
with engine.begin() as conn:
pg_sync_roles(
conn,
'my_user_name',
grants=(
DatabaseConnect('my_database_name'),
RoleMembership('my_role_name'),
),
)
A more complex example:
from pg_sync_roles import (
RoleMembership,
SchemaUsage,
SchemaOwnership,
TableSelect,
pg_sync_roles,
)
engine = sa.create_engine('postgresql+psycopg://postgres@127.0.0.1:5432/')
with engine.begin() as conn:
pg_sync_roles(
conn,
'my_role_name',
grants=(
TableSelect('my_schema', 'my_table'),
SchemaUsage('my_schema'),
RoleMembership('my_other_role'),
SchemaOwnership('my_other_schema', create_if_not_exists=True),
),
revoke_other_role_memberships=True,
revoke_other_table_selects=True,
revoke_other_schema_usage=True,
)
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
pg_sync_roles-0.0.3.tar.gz
(3.7 kB
view hashes)
Built Distribution
Close
Hashes for pg_sync_roles-0.0.3-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 5e646c47579bb45f82413c37fd5176fccb0dc0499911d6c877f6367d99c29ec1 |
|
MD5 | 78944321e9deb8db679cf6aef8b2af8a |
|
BLAKE2b-256 | a1ec536db4762c90b751b9ca5e61565045b6e07810529f2d9406d3467715a7fa |