Access Control in Pgcli: How to Manage Database Permissions Effectively

Pgcli is widely recognized as a powerful command-line interface for interacting with PostgreSQL databases. With its autocompletion and syntax highlighting, it streamlines queries and database management tasks. However, when working with databases, particularly in collaborative environments, effective access control becomes essential. This guide explores how Pgcli can make managing access control in PostgreSQL databases more efficient.

Why Access Control Matters in PostgreSQL

Databases often contain sensitive or critical data that should only be accessible to authorized users. Access control ensures that individuals have the appropriate permissions to interact with the database—minimizing risks like data breaches and unauthorized changes. PostgreSQL has a robust set of tools for user management and permissions, and combining these features with Pgcli can significantly enhance usability during day-to-day workflows.

Setting Up Access Control in Pgcli

Access control in PostgreSQL revolves around three main entities: roles, privileges, and objects:

• Roles: Represent users or groups in the database.
• Privileges: Define what actions a role can take on a specific object (e.g., tables, schemas).
• Objects: The database entities, such as tables or functions, that need protection.

To begin with access control using Pgcli, make sure you’ve connected to your database using a user account with administrative privileges.

pgcli -U postgres -h localhost -d my_database

Here, you’re connecting as the postgres user, which is the default superuser in PostgreSQL.

Create a New Role

Creating a new database role for a user is straightforward. Suppose you want to add a developer to your database:

CREATE ROLE developer WITH LOGIN PASSWORD 'securepassword';

This command creates a role named developer with login privileges and a password. Replace 'securepassword' with a stronger password.

Grant Privileges

Once a role exists, you’ll need to assign specific privileges. For example, if the developer role should only read from a table (products), grant the SELECT privilege:

GRANT SELECT ON TABLE products TO developer;

Now, the developer can query the table but cannot make changes.

Revoke Privileges

If access needs to be restricted, use the REVOKE command. For instance, to remove write permissions from a role:

REVOKE INSERT, UPDATE, DELETE ON TABLE products FROM developer;

This ensures the developer role cannot alter the table’s data.

Manage Roles and Role Inheritance

Roles in PostgreSQL can inherit permissions from other roles. This is useful for creating role hierarchies. For example:

CREATE ROLE read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
GRANT read_only TO developer;

Here, the read_only role has SELECT permissions for all tables in the public schema. By assigning read_only to developer, everything granted to read_only is now available to the developer role.

Using Pgcli for Day-to-Day Management

Pgcli’s natural autocompletion will assist you in typing commands efficiently. For example, typing partial SQL statements like GRANT SEL will autopopulate suggestions for SELECT. This is particularly handy when modifying access privileges across multiple schemas and tables.

Additionally, Pgcli supports \du commands to list roles and their permissions. Use:

\du

This gives a quick overview of all users and their access levels.

Streamline Your Database Access Control with Hoop.dev

Managing access control is critical for security and collaboration. While Pgcli simplifies much of this process, implementing and testing changes can still involve trial and error—especially as team needs evolve.

Hoop.dev enhances this workflow by offering clear visibility into access policies, permissions, and real-time usage patterns in your database. With just a few clicks, you can reduce friction in role management and validate that permissions reflect organizational requirements. Get started and see it live in minutes!