Column-level permissions in PostgreSQL

Introduction

Column-level permissions let you control who can see or change specific columns in a table. This keeps sensitive data safe.

You want to hide salary information from most employees but allow HR to see it.

You want to let users update their contact info but not their user ID.

You want to show product names to customers but hide supplier details.

You want to restrict access to credit card numbers in a customer table.

You want to allow read-only access to some columns but full access to others.

Syntax

PostgreSQL

GRANT SELECT (column_name) ON table_name TO role_name;
REVOKE SELECT (column_name) ON table_name FROM role_name;

You specify the columns inside parentheses after the privilege type.

You can grant or revoke permissions on specific columns separately from the whole table.

Examples

This lets the sales team see only the name and email columns in the customers table.

PostgreSQL

GRANT SELECT (name, email) ON customers TO sales_team;

This removes permission for everyone to see the salary column in the employees table.

PostgreSQL

REVOKE SELECT (salary) ON employees FROM public;

This allows the support team to update only the phone column in the customers table.

PostgreSQL

GRANT UPDATE (phone) ON customers TO support_team;

Sample Program

This example creates an employees table and a role hr_team. It grants hr_team permission to see only the name and email columns, not salary. Then it switches to hr_team role and tries to select all columns.

PostgreSQL

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT,
  email TEXT,
  salary NUMERIC
);

INSERT INTO employees (name, email, salary) VALUES
('Alice', '[e-mail]', 70000),
('Bob', '[e-mail]', 60000);

CREATE ROLE hr_team;

GRANT SELECT (name, email) ON employees TO hr_team;
REVOKE SELECT (salary) ON employees FROM hr_team;

SET ROLE hr_team;

SELECT * FROM employees;

Important Notes

If a role does not have permission on a column, that column will show as NULL when selected.

Column-level permissions work together with table-level permissions. You need SELECT on the table or on the columns.

Summary

Column-level permissions control access to specific columns in a table.

Use GRANT and REVOKE with column names in parentheses.

Restricted columns show as NULL if the user lacks permission.