Security - Authorization in PostgreSQL

Introduction

PostgreSQL uses roles and privileges for managing database access and operations. This tutorial explains how to set up roles, grant permissions, and manage authorization effectively.

Roles and Privileges

PostgreSQL defines roles to control access permissions. Roles can be assigned specific privileges on database objects. There are two types of roles: login roles and group roles.

Creating Roles


-- Create a login role
CREATE ROLE username LOGIN PASSWORD 'password';

-- Create a group role
CREATE ROLE group_name;

Managing Permissions

Use GRANT and REVOKE commands to manage permissions effectively. Ensure roles are granted appropriate privileges based on security requirements.

Granting Privileges

Grant privileges to roles to control access to database objects.


-- Grant privileges on a table to a role
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE table_name TO role_name;

-- Grant all privileges on a schema to a role
GRANT ALL PRIVILEGES ON SCHEMA schema_name TO role_name;

-- Grant role membership
GRANT group_name TO username;

Revoking Privileges

Revoke previously granted privileges from roles.


-- Revoke privileges from a role
REVOKE SELECT ON TABLE table_name FROM role_name;

-- Revoke all privileges on a schema from a role
REVOKE ALL PRIVILEGES ON SCHEMA schema_name FROM role_name;