Why Read-Only Roles Matter When Using Pgcli with AWS S3

A single mistyped query can wipe production data. That’s why read-only roles matter. When working with pgcli, AWS, and S3, the safest path is to grant only the permissions you need—never more.

Pgcli is a fast, feature-rich Postgres CLI tool with auto-completion, syntax highlighting, and smart connection handling. It’s ideal for quick database queries. But when integrating with AWS S3 for imports, exports, or backups, security concerns escalate fast. Direct access to S3 buckets without strict IAM policies opens the door to accidental deletes or overwrites.

The solution: combine Pgcli with AWS S3 read-only roles. This setup allows you to query and pull data from S3 without risking writes. Here’s how it works:

1. Create a Read-Only IAM Role
   • Go to the AWS IAM console.
   • Create a new role for S3 with a “read-only” policy.
   • Use AmazonS3ReadOnlyAccess as the managed policy.
   • Use a custom policy limiting access to only the buckets required.
   • Example:
2. Configure Pgcli to Use Role Credentials
   • If you’re running pgcli from an EC2 or container, attach the read-only role to the instance profile.
   • For local use, export AWS credentials linked to the read-only role via aws sts assume-role.
3. Query Without Risk
   • The role prevents any PUT, DELETE, or POST operations.
   • Pgcli commands that involve reading backups or CSVs from S3 run safely.

Restrict to Specific Buckets

{
 "Version": "2012-10-17",
 "Statement": [
 {
 "Effect": "Allow",
 "Action": [
 "s3:GetObject",
 "s3:ListBucket"
 ],
 "Resource": [
 "arn:aws:s3:::my-data-bucket",
 "arn:aws:s3:::my-data-bucket/*"
 ]
 }
 ]
 }

Why This Matters for Security
Read-only roles act as a final guardrail. Even if a script loops over file writes by mistake, the role blocks them. In multi-user environments, this protection is critical. It’s a simple move with disproportionate impact on operational safety.

Don’t wait until a database export goes wrong. Attach a read-only role, lock access down, and run Pgcli against S3 with confidence.

Want to see a secure, read-only flow in action? Try it on hoop.dev and get it live in minutes—no risk, all speed.