DevOps Knowledge Hub
DevOps Knowledge Hub

Essential Commands for Managing Users, Roles, and Permissions in PostgreSQL

Master the essential SQL commands for robust PostgreSQL security and user management. This guide provides practical steps for creating, modifying, and deleting roles, setting complex attributes (like LOGIN and CREATEDB), and managing group membership. Learn how to precisely control access using the powerful `GRANT` and `REVOKE` commands, defining object-level permissions on tables, schemas, and functions. Implement the principle of least privilege with detailed examples and discover key psql commands for auditing current security settings.

368 views

Essential Commands for Managing Users, Roles, and Permissions in PostgreSQL

Database security hinges on robust access control. In PostgreSQL, this control is managed primarily through Roles and associated Permissions (or privileges). Understanding how to create, modify, and manage these security entities is fundamental for any database administrator or developer.

This comprehensive guide explores the essential SQL commands necessary for securely managing users, defining access policies, and implementing the principle of least privilege within your PostgreSQL environment. We will cover role creation, attribute setting, group membership, and detailed object permission management using GRANT and REVOKE.

Understanding PostgreSQL Roles

Unlike some other database systems that differentiate strictly between users and groups, PostgreSQL uses a unified concept: the Role. A role can represent:

  1. A database user (an entity that can log in, typically having the LOGIN attribute).
  2. A group of users (an entity used solely for grouping privileges, typically lacking the LOGIN attribute).

Effective security starts with defining roles that accurately reflect the responsibilities of the individuals or applications accessing the database.

1. Role Management: Creation, Modification, and Deletion

The fundamental commands for managing database principals revolve around CREATE ROLE, ALTER ROLE, and DROP ROLE.

Creating New Roles

When creating a role, you must specify its attributes, particularly whether it can log in (LOGIN) and its authentication credentials (PASSWORD).

Creating a Basic Login User

To create a standard user role that requires a password for connection:

CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password_here';

Creating a Role with Specific Attributes

Roles can have various attributes that define their capabilities and system permissions:

Attribute Description
LOGIN Allows the role to connect to the database.
SUPERUSER Grants all database privileges (use sparingly).
CREATEROLE Allows the role to create, alter, and drop other roles.
CREATEDB Allows the role to create new databases.
REPLICATION Allows the role to initiate streaming replication.
INHERIT / NOINHERIT Controls whether the role automatically inherits privileges from roles it is a member of (default is INHERIT).

Example: Creating an Administrator Role (Non-Superuser)

CREATE ROLE db_admin WITH
    LOGIN
    PASSWORD 'admin_secret'
    CREATEROLE
    CREATEDB
    VALID UNTIL '2025-01-01'; -- Optional expiration date

Modifying Existing Roles

Use ALTER ROLE to change attributes or update the password for an existing role.

-- Change the password for an existing user
ALTER ROLE app_user WITH PASSWORD 'new_strong_password';

-- Revoke login capability (turning a user into a group)
ALTER ROLE db_admin NOLOGIN;

-- Set a user to be locked out
ALTER ROLE old_employee NOLOGIN;

Dropping Roles

Before dropping a role, ensure that the role does not own any database objects (tables, schemas, etc.). If it does, you must first transfer ownership using REASSIGN OWNED.

-- Drop all objects owned by the role and reassign them to 'postgres'
REASSIGN OWNED BY old_employee TO postgres;

-- Then, drop the role
DROP ROLE old_employee;

Warning: Dropping a role is irreversible. Use caution, especially with roles that own many objects.

2. Managing Group Membership

Roles often function as groups to simplify permission management. Instead of granting permissions to 50 individual users, you grant them to one group role, and then make the users members of that group.

Creating a Group Role

Groups are typically created without the LOGIN attribute.

CREATE ROLE data_analysts NOLOGIN;

Granting and Revoking Group Membership

Use the GRANT command to add members to a group role, and REVOKE to remove them.

-- Add user 'alice' and user 'bob' to the 'data_analysts' group
GRANT data_analysts TO alice, bob;

-- Remove 'bob' from the 'data_analysts' group
REVOKE data_analysts FROM bob;

The WITH ADMIN OPTION

If you include WITH ADMIN OPTION when granting membership, the recipient role can then grant membership in that group to others, and can also DROP the group role.

GRANT data_analysts TO supervisor WITH ADMIN OPTION;

3. Managing Object Permissions (Privileges)

Permissions define what actions a role can perform on which database objects (tables, views, functions, schemas, etc.). This is the core of database security.

The GRANT Command Syntax

GRANT privilege_list ON object_type object_name TO role_name [WITH GRANT OPTION];

Common Privileges and Examples

Table Privileges

Privilege Action Allowed
SELECT Read data from the table.
INSERT Add new rows.
UPDATE Modify existing rows.
DELETE Remove existing rows.
TRUNCATE Empty the table completely.
REFERENCES Create foreign key constraints.

Example: Granting Read-Only access to a specific table.

GRANT SELECT ON TABLE production.orders TO data_analysts;

-- Grant all DML operations on a table
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE staging.temp_data TO app_user;

Database and Schema Privileges

Database and schema privileges are crucial for controlling the environment structure.

Object Key Privilege Purpose
DATABASE CONNECT Allows connection to the database.
DATABASE CREATE Allows creation of new schemas, tablespaces, etc.
SCHEMA USAGE Allows access to objects within the schema.
SCHEMA CREATE Allows creation of new objects within the schema.

Example: Granting Schema Access

If a user needs to access tables within the app_schema, they must have USAGE on that schema.

GRANT USAGE ON SCHEMA app_schema TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA app_schema TO app_user;

Sequence and Function Privileges

Sequence usage (for auto-incrementing IDs) and function execution require specific privileges.

-- Allow the user to advance the sequence (necessary for INSERTs)
GRANT USAGE, SELECT ON SEQUENCE app_schema.user_id_seq TO app_user;

-- Allow execution of a specific stored procedure or function
GRANT EXECUTE ON FUNCTION audit_log_insert(text) TO app_user;

Revoking Permissions

Use the REVOKE command to remove specific permissions previously granted. The syntax mirrors GRANT.

-- Revoke the ability to insert new records into the orders table
REVOKE INSERT ON TABLE production.orders FROM app_user;

-- Revoke all permissions previously granted (note: does not revoke ownership)
REVOKE ALL PRIVILEGES ON TABLE production.orders FROM data_analysts;

Note on Revocation: If a privilege was granted to a role, and that role is a member of another group, revoking the privilege only affects the directly granted permission. If the role still inherits the privilege via group membership, they retain access.

Applying Permissions to Future Objects

Managing permissions for objects that don't yet exist requires using ALTER DEFAULT PRIVILEGES. This is essential for schemas where applications frequently create new tables.

Example: Ensuring a user can SELECT from all future tables created by the app_owner role within the app_schema:

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app_schema
    GRANT SELECT ON TABLES TO app_user;

4. Viewing Current Permissions

To audit security settings, PostgreSQL provides several tools and catalog views.

Command/View Description
\du (in psql) Lists all roles and their attributes.
\du+ role_name (in psql) Shows detailed role attributes and membership.
\dp table_name (in psql) Lists permissions (privileges) granted on a table.
pg_roles System catalog view containing role definitions.
information_schema.table_privileges View showing granted privileges on tables.

Example: Checking privileges on a table via psql

=> \dp production.orders

Security Best Practices for User Management

  1. Principle of Least Privilege (PoLP): Only grant the minimal set of permissions required for a role to perform its function. Avoid granting ALL PRIVILEGES or SUPERUSER unless absolutely necessary.
  2. Separate Application Roles: Use dedicated roles for applications (e.g., api_service_role) separate from human DBA roles. Applications should typically only have DML permissions (SELECT, INSERT, UPDATE, DELETE) and not DDL permissions (CREATE, DROP).
  3. Use Groups Extensively: Create non-login roles (groups) to manage sets of permissions (e.g., read_only_group, etl_writer_group). Assign individual users to these groups rather than granting permissions individually.
  4. Avoid Using Default Roles: Never use the postgres superuser role for general application or development tasks.
  5. Secure Authentication: Always use strong passwords, and whenever possible, leverage client certificate authentication or centralized identity management solutions instead of password-based authentication.

Conclusion

Effective management of roles and permissions is the bedrock of PostgreSQL security. By mastering CREATE ROLE, ALTER ROLE, GRANT, and REVOKE, database administrators can implement granular control, ensuring that every user or application accessing the database has precisely the access required and nothing more. Consistently applying the principle of least privilege and utilizing group roles simplifies long-term maintenance and strengthens your database posture against unauthorized access.