Column-level security

Learn how to use column-level security (CLS) in Firebolt to restrict which columns of a table a role can read using GRANT and REVOKE with column lists.

Column-level security (CLS) lets you restrict which columns of a table a given role can read. You grant or revoke SELECT on specific columns using PostgreSQL-style syntax:

GRANT SELECT (col1, col2) ON TABLE t TO analyst_role;

A user who has not been granted access to a column cannot read it in queries. SELECT * expands only to their permitted columns, explicit references to unpermitted columns are rejected, and catalog views like information_schema.columns hide those columns from view.

How column-level security works

CLS extends Firebolt’s existing Role-Based Access Control (RBAC) model. A table-level SELECT grant (no column list) continues to mean “access to all columns.” Column-level grants add a finer level of control within a table.

Key rules:

• Table-level SELECT grants all columns. If a role has GRANT SELECT ON TABLE t, it can read every column regardless of any column-scoped grants. To restrict the role to specific columns, you must first REVOKE SELECT ON TABLE and then grant column-level access.
• Column grants are additive. Granting (col_a) and then (col_b) is equivalent to granting (col_a, col_b) — the sets are merged.
• Union across roles. A user sees the union of columns granted across all their roles. If role_a grants SELECT (col1) and role_b grants SELECT (col2), a user with both roles can read both columns.
• Column identity is stable. Privileges are stored using internal column IDs, not column names. Renaming a column does not affect access. Dropping and re-adding a column with the same name creates a new ID, so old grants do not apply to the new column.
• Unauthorized columns are invisible. Referencing an unauthorized column produces the same error as referencing a column that does not exist.

Prerequisites

Before configuring column-level security, make sure:

• You have a user with GRANT privileges (typically an account_admin or a role with the relevant permissions).
• You understand RBAC basics and table permissions.
• The target database and schema exist, and you have USAGE on both.

Grant SELECT on specific columns

Use GRANT with a column list to restrict a role to specific columns:

GRANT SELECT (col1, col2) ON TABLE <table_name> TO <role_name>;

If the role already has a table-level SELECT grant (that is, GRANT SELECT ON TABLE without a column list), it can read all columns regardless of any column-scoped grants. You must first revoke the table-level grant before column-level restrictions take effect:

REVOKE SELECT ON TABLE <table_name> FROM <role_name>;
GRANT SELECT (col1, col2) ON TABLE <table_name> TO <role_name>;

Column-list syntax is only valid for SELECT on TABLE objects. You cannot use column lists with other privileges (such as INSERT or DELETE) or on views.

Example

Suppose you have an employees table with sensitive salary and SSN data:

CREATE TABLE employees (
  employee_id INT,
  name TEXT,
  department TEXT,
  salary NUMERIC(10,2),
  ssn TEXT
);

Grant an analyst_role access to only the non-sensitive columns:

-- Grant database and schema access
GRANT USAGE ON DATABASE my_db TO analyst_role;
GRANT USAGE ON SCHEMA public TO analyst_role;

-- Grant SELECT on specific columns only
GRANT SELECT (employee_id, name, department) ON TABLE employees TO analyst_role;

Verify access:

-- As a user with analyst_role:
SELECT * FROM employees;
-- Returns only employee_id, name, department

SELECT salary FROM employees;
-- Error: column "salary" not found

Revoke SELECT on specific columns

Use REVOKE with a column list to remove access to specific columns:

REVOKE SELECT (col1, col2) ON TABLE <table_name> FROM <role_name>;

Column-list REVOKE subtracts from the column-scoped grant only. It does not affect table-level SELECT grants.

Example

Remove access to the department column:

REVOKE SELECT (department) ON TABLE employees FROM analyst_role;

The role retains access to employee_id and name.

Interaction with table-level grants

Column-level and table-level SELECT grants follow an OR rule, consistent with PostgreSQL:

Role state	Effect
Table-level SELECT (no column list)	Role can read all columns.
Column-scoped SELECT for columns {A, B}	Role can read columns A and B only.
Both table-level and column-scoped grants	Same as table-level: role can read all columns.
No SELECT at all	Access denied at the table level.

Moving from full table access to column-scoped access

You cannot “punch holes” in table-level access with a column-list REVOKE. To restrict a role from full table access to a subset of columns:

1. Revoke the table-level SELECT:
   REVOKE SELECT ON TABLE employees FROM analyst_role;
   
2. Grant access to only the desired columns:
   GRANT SELECT (employee_id, name, department) ON TABLE employees TO analyst_role;
   

Table-level REVOKE removes column-scoped grants

REVOKE SELECT ON TABLE (without a column list) removes all SELECT on that table for the role, including any column-scoped grants. This matches PostgreSQL behavior.

Schema evolution

Column-level privileges use stable column IDs. When the table schema changes:

• ADD COLUMN: The new column is not included in any existing column-scoped grant. Roles with column-scoped SELECT cannot read the new column until an administrator explicitly grants it.
• DROP and re-add column: Dropping and re-adding a column with the same name creates a new internal column ID. Previous grants do not apply to the new column.
• RENAME COLUMN: Renaming a column does not affect access. The grant follows the column’s stable internal ID, not its name.

STRUCT columns

CLS operates at the top-level column granularity. A STRUCT column is granted or denied as a whole. Individual sub-fields cannot be independently granted or revoked.

-- Table: t(id INT, info STRUCT(name TEXT, score INT), secret INT)
-- Role granted SELECT (id, info):

SELECT * FROM t;          -- Returns id and info; secret is hidden
SELECT info.name FROM t;  -- Succeeds; sub-field access works
SELECT secret FROM t;     -- Error: column not found

Catalog visibility

View	Behavior under CLS
information_schema.columns	Shows only columns the current user is permitted to read.
information_schema.object_privileges	Shows the SELECT privilege for the table without exposing the column list. Both column-scoped and table-level grants appear as SELECT on the table.

Views and column-level security

When a query references a view, column-level access on the underlying table is evaluated using the view owner’s privileges, not the querying user’s. This is the same owner rights model that Firebolt applies to all views.

For example, if a view owner has SELECT (a, b) on a table and a view is defined as SELECT a, b FROM t, any user with SELECT on the view can see both columns through the view, even if their own direct table grants are narrower.

For scenarios that require data masking (exposing columns in a transformed or redacted form) or row-level security (filtering which rows a user can see), use secure views. Column-level security and secure views complement each other as part of Firebolt’s layered security model.

Example: multi-role column access

The following example sets up two roles with different column access on the same table, then shows the effective access for a user with both roles:

CREATE TABLE orders (
  order_id INT,
  customer_name TEXT,
  amount NUMERIC(10,2),
  internal_notes TEXT
);

-- Role for sales: can see order details but not internal notes
CREATE ROLE sales_role;
GRANT USAGE ON DATABASE my_db TO sales_role;
GRANT USAGE ON SCHEMA public TO sales_role;
GRANT SELECT (order_id, customer_name, amount) ON TABLE orders TO sales_role;

-- Role for support: can see order and notes but not amount
CREATE ROLE support_role;
GRANT USAGE ON DATABASE my_db TO support_role;
GRANT USAGE ON SCHEMA public TO support_role;
GRANT SELECT (order_id, customer_name, internal_notes) ON TABLE orders TO support_role;

-- User with both roles sees the union: all four columns
GRANT ROLE sales_role TO USER alice;
GRANT ROLE support_role TO USER alice;

-- As alice:
SELECT * FROM orders;
-- Returns order_id, customer_name, amount, internal_notes

Related topics

• GRANT — Syntax reference for granting privileges
• REVOKE — Syntax reference for revoking privileges
• Table permissions — Table-level privilege reference
• Using secure views — View-based security for data masking and row-level security
• Role-Based Access Control — Overview of Firebolt’s RBAC model
• information_schema.columns — Column metadata filtered by access