pg-sync-roles

pg-sync-roles is a powerful Python utility designed to simplify the management of PostgreSQL role permissions and memberships. This tool enables you to ensure that a PostgreSQL role possesses specific permissions while automatically revoking any that are not needed, thereby streamlining your permission management process.

Key Features

• Efficient Permission Management: Transparently handles numerous permissions while preventing "row is too big" errors that can occur with high volumes of permissions.
• Advisory Locking: Utilizes locking mechanisms to mitigate "tuple concurrently updated" or "tuple concurrently deleted" errors during concurrent permission changes.
• No SUPERUSER Requirement: Can be operated without SUPERUSER privileges, making it ideal for managed PostgreSQL environments such as Amazon RDS.
• Flexible Permission Granting: Seamlessly grants:
  • Login access with a configurable password and expiry.
  • Role memberships to facilitate collaboration.
  • Database connection permissions.
  • Schema usage, creation, and ownership privileges.
  • SELECT permissions on tables and similar objects, while automatically revoking non-SELECT permissions.
• Selective Schema Management: Offers the ability to ignore specific schemas during permission management for better operational efficiency.

Important Note: pg-sync-roles does not revoke any permissions granted to the PUBLIC pseudo-role.

Why Use pg-sync-roles?

This tool is particularly beneficial in scenarios where PostgreSQL functions as a data warehouse with a multitude of users requiring precise permissions. The lack of a SUPERUSER requirement allows for a more compliant and safer operation within managed PostgreSQL clusters. Future versions also plan to support additional privilege types and object management capabilities.

Usage Examples

To give a role the ability to log in, connect to a database, and gain membership in another role:

import string
import secrets
from datetime import datetime, timedelta, timezone
from pg_sync_roles import Login, DatabaseConnect, RoleMembership, sync_roles

password_alphabet = string.ascii_letters + string.digits
password = ''.join(secrets.choice(password_alphabet) for i in range(64))
valid_until = datetime.now(timezone.utc) + timedelta(days=28)

with engine.connect() as conn:
    sync_roles(
        conn,
        'my_user_name',
        grants=(
            Login(password=password, valid_until=valid_until),
            DatabaseConnect('my_database_name'),
            RoleMembership('my_role_name'),
        ),
    )

For granting SELECT permissions on a table and managing schema privileges:

from pg_sync_roles import (
    RoleMembership,
    SchemaUsage,
    SchemaCreate,
    TableSelect,
    sync_roles,
)

with engine.connect() as conn:
    sync_roles(
        conn,
        'my_role_name',
        grants=(
            SchemaUsage('my_schema'),
            TableSelect('my_schema', 'my_table'),
            SchemaOwnership('my_other_schema'),
            SchemaUsage('my_other_schema'),
            SchemaCreate('my_other_schema'),
            RoleMembership('my_other_role'),
        ),
    )

Get Started

Explore the full documentation in the README for more advanced usage, API details, and design considerations. pg-sync-roles is the ideal solution for managing PostgreSQL role permissions efficiently and securely.