skills.rest
rbudiharso
rbudiharsoCOMMUNITY
Explore all skills by this author →
Website𝕏@rbudiharsoGitHub

rds-postgres-permissions

Audit and grant PostgreSQL user permissions and diagnose vacuum bloat on AWS RDS.

2|Updated Oct 20, 2017
Search Tags:#devops#postgresql#permissions#vault#vacuum#grants#aws rds
One-click install
npx skills add https://github.com/rbudiharso/dotfiles --skill rds-postgres-permissions-rbudiharso
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: rds-postgres-permissions
Source: https://github.com/rbudiharso/dotfiles/tree/main/hermes/.hermes/skills/devops/rds-postgres-permissions
Command: npx skills add https://github.com/rbudiharso/dotfiles --skill rds-postgres-permissions-rbudiharso

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes scripts (resource) and references (resource) components.

What problem does it solve? Managing PostgreSQL user permissions on AWS RDS is error-prone: inherited role grants are hard to audit, GRANT statements miss sequences or future tables, and bloated tables silently waste disk space when autovacuum falls behind. This Skill provides tested SQL queries, scripts, and workflows to audit permissions, grant DML/DDL access correctly, and diagnose vacuum health across all databases on an instance. ## Core Features & Use Cases - Permission Auditing: Inspect role properties, role memberships, database-level access, and table-level privileges using reliable has_table_privilege() queries instead of the unreliable information_schema.role_table_grants view. - DML/DDL Granting: Use the scripts/grant_dml.sql template to grant SELECT/INSERT/UPDATE/DELETE on all existing tables and sequences across non-system schemas, plus default privileges for future tables. - Vacuum & Bloat Analysis: Scan every database with scripts/vacuum-check.sh for dead tuples, stale statistics, and oversized tables, then decide between VACUUM ANALYZE and VACUUM FULL. - Vault Credential Workflow: Source RDS credentials from HashiCorp Vault at connection time so passwords never appear in chat transcripts. - Use Case: A developer reports "permission denied" on a staging table. Audit their role memberships and table privileges, run the grant script via psql -f, and verify access with has_table_privilege() — all without exposing the database password. ## Quick Start Ask the agent to audit a PostgreSQL user's permissions on your RDS instance using credentials stored in Vault, then grant DML access and verify the result.

Frequently Asked Questions about rds-postgres-permissions

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I grant SELECT, INSERT, UPDATE, DELETE on all tables in PostgreSQL?▼

Use a DO block executed via psql -f that loops over non-system schemas, granting USAGE on each schema, DML on all tables, USAGE and SELECT on sequences, and ALTER DEFAULT PRIVILEGES for future tables. Dollar-quoting breaks with psql -c, so always use a script file.

How do I check a PostgreSQL user's table permissions on RDS?▼

Query has_table_privilege() against pg_class joined with pg_namespace to test SELECT, INSERT, UPDATE, and DELETE per table. Avoid information_schema.role_table_grants, which can return zero rows even when privileges exist through inherited role grants.

Why does my DO block fail when run with psql -c?▼

Dollar-quoting ($$) in DO blocks breaks when passed through psql -c because of shell and argument parsing. Write the block to a .sql file and execute it with psql -f instead.

When should I use VACUUM FULL instead of VACUUM ANALYZE on PostgreSQL?▼

Use VACUUM ANALYZE to clear dead tuples and refresh planner statistics without locking. Use VACUUM FULL only when a table has severe bloat, such as gigabytes of size with few live rows, since it rewrites the table and takes an exclusive lock.

Why does INSERT fail with permission denied on a serial column?▼

Tables with SERIAL or identity columns require USAGE and SELECT privileges on the underlying sequence, not just table grants. Grant USAGE, SELECT ON ALL SEQUENCES IN SCHEMA to the user to fix it.

How do I avoid exposing RDS database passwords in chat?▼

Store credentials in HashiCorp Vault KV and source them at connection time with vault kv get, exporting PGPASSWORD as an environment variable. Re-authenticate with vault login when the OIDC token expires after one hour.

Related Skills

Explore More Software Engineering Skills→
tjl8787tjl8787

rds

Manage AWS RDS instances for provisioning, backup, restore, and performance optimization.

Community
Advanced
ajayatwal1105-emersonajayatwal1105-emerson

competitor-monitor

Configure recurring competitor scans across website, pricing, ads, social, SEO, and SERP signals.

Community
Advanced
selrai-companyselrai-company

postgresql-optimization

Analyze EXPLAIN ANALYZE and pg_stat_statements to recommend PostgreSQL indexes and configuration.

Official
Advanced
googlegoogle
17.1k

cloud-sql-basics

Manage Cloud SQL instances and databases using gcloud CLI and Terraform.

Official
Advanced