Managing PostgreSQL Roles with pgcli

You know the feeling. You’re in the middle of a session, running a quick query through pgcli, moving fast. Then you remember — roles. Permissions. Grants. Revokes. That quiet backbone of every PostgreSQL database that either lets your commands fly or shuts them down mid-thought.

pgcli isn’t just a pretty shell for PostgreSQL. It can be the fastest way to understand, debug, and manage database roles without leaving your terminal. With its autocomplete, syntax highlighting, and output formatting, you can see the full picture of your role structure in seconds.

Listing Roles in `pgcli`

To see all roles, connect to your database with pgcli and run:

\du

The output gives you each role name, their attributes (superuser, login, replication, etc.), and member roles. This is the command to start with when you need a broad view of access patterns.

Creating Roles

Roles are users or groups in PostgreSQL. You can create one in pgcli like this:

CREATE ROLE analyst LOGIN PASSWORD 'password';

Add attributes to grant access or set restrictions:

CREATE ROLE data_engineer LOGIN CREATEDB;

Run \du again to confirm the new role exists.

Continue reading? Get the full guide.

PostgreSQL Access Control + Lambda Execution Roles: Architecture Patterns & Best Practices

Free. No spam. Unsubscribe anytime.

Granting and Revoking Permissions

Granting a role privileges to a database:

GRANT CONNECT ON DATABASE app_db TO analyst;

Granting schema-level privileges:

GRANT USAGE ON SCHEMA public TO analyst;

Revoking is just as direct:

REVOKE CONNECT ON DATABASE app_db FROM analyst;

Role Memberships

PostgreSQL supports role inheritance. You can make one role a member of another to pass down permissions:

GRANT data_engineer TO analyst;

Membership simplifies permission management in large systems, cutting down on duplicate grants.

Best Practices for Role Management

Keep superuser privileges minimal.
Use role groups to manage access for teams.
Remove unused roles immediately.
Audit \du output regularly for unexpected changes.

Managing database roles through pgcli keeps your work inside one powerful interface, reduces context switching, and shortens the feedback loop on permission changes. It’s the fastest way to script, test, and roll out role adjustments without friction.

If you want to go from zero to a live, queryable PostgreSQL environment with role management in minutes, check out hoop.dev. You’ll see it running for real before you’ve even finished your coffee.

Would you like me to also provide you with an SEO keyword cluster map for the phrase “pgcli database roles” so you can rank even higher?