postgresql-admin

pluginagentmarketplace's avatarfrom pluginagentmarketplace

Administer PostgreSQL - security, roles, permissions, maintenance

⭐1stars🔀0forks📁View on GitHub🕐Updated Jan 5, 2026
[SQL and Databases]

When & Why to Use This Skill

The PostgreSQL Administration skill is a comprehensive toolkit designed for managing database security, user roles, and essential maintenance tasks. It provides production-ready SQL patterns for implementing Role-Based Access Control (RBAC), hardening database security through Row Level Security (RLS), and executing routine maintenance operations like VACUUM and REINDEX to ensure high performance and data integrity in production environments.

Use Cases

  • User & Role Management: Streamline the creation of application-specific roles with restricted connection limits and granular permissions (GRANT/REVOKE) across schemas.
  • Security Hardening: Secure sensitive data by implementing Row Level Security (RLS) policies for multi-tenant isolation and removing default public access to schemas.
  • Performance Optimization: Maintain database health by performing routine maintenance tasks such as ANALYZE for query planning and VACUUM to reclaim storage space.
  • Security Auditing: Identify potential vulnerabilities by auditing superuser privileges and inspecting public table permissions to ensure compliance with security standards.
  • Troubleshooting & Recovery: Resolve common database access issues, such as permission denied errors (42501) or authentication failures, using standardized troubleshooting procedures.
namepostgresql-admin
descriptionAdminister PostgreSQL - security, roles, permissions, maintenance
version"3.0.0"
sasmp_version"1.3.0"
bonded_agent04-postgresql-admin
bond_typePRIMARY_BOND
categorydatabase
difficultyadvanced
estimated_time3h

PostgreSQL Administration Skill

Atomic skill for database security and maintenance

Overview

Production-ready patterns for role management, security hardening, and routine maintenance.

Prerequisites

  • PostgreSQL 16+
  • Superuser or role management privileges

Parameters

parameters:
  operation:
    type: string
    required: true
    enum: [create_role, grant, revoke, audit, maintain]
  role_name:
    type: string
    pattern: "^[a-z][a-z0-9_]*$"

Quick Reference

Role Creation

CREATE ROLE app_user WITH LOGIN PASSWORD 'secure' CONNECTION LIMIT 100;
CREATE ROLE readonly_role;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO readonly_role;

Security Hardening

REVOKE ALL ON SCHEMA public FROM PUBLIC;
ALTER TABLE data ENABLE ROW LEVEL SECURITY;
CREATE POLICY isolation ON data USING (tenant_id = current_setting('app.tenant')::uuid);

Maintenance

ANALYZE VERBOSE table_name;
VACUUM (VERBOSE, ANALYZE) table_name;
REINDEX INDEX CONCURRENTLY idx_name;

Security Audit

SELECT rolname FROM pg_roles WHERE rolsuper;  -- Superusers
SELECT * FROM information_schema.table_privileges WHERE grantee = 'PUBLIC';

Troubleshooting

Error Cause Solution
42501 Permission denied Check GRANTs
28P01 Auth failed Reset password
55P03 Lock unavailable Kill blocker

Usage

Skill("postgresql-admin")