Column-Level Security

Column-level access control enables fine-grained management of database user permissions at the level of individual columns within tables. Users can be granted access to specific columns while restricting access to others. Below is a detailed guide on how to use Tacnode’s column-level access control.

Supported operation types for column-level permissions:

Column-level permissions apply to TABLE, VIEW, and MATERIALIZED VIEW objects.

Overview

In Tacnode, the GRANT and REVOKE commands can be used to manage column-level permissions, allowing or restricting a user’s ability to query (SELECT) or modify (UPDATE) specific columns.

Grant column-level permissions

GRANT {SELECT | UPDATE} (<column>[, ...])
ON <table>
TO <user>;

Revoke column-level permissions

REVOKE {SELECT | UPDATE} (<column>[, ...])
ON <table>
FROM <user>;

Usage Example

Scenario: Create an employees table and set up column-level permissions

1. Create table

   CREATE TABLE employees (
       id SERIAL PRIMARY KEY,
       name VARCHAR(50),
       salary NUMERIC,
       department VARCHAR(20)
   );

2. Insert data

   INSERT INTO employees (name, salary, department)
   VALUES
   ('Alice', 50000, 'HR'),
   ('Bob', 70000, 'IT');

3. Create regular user

   CREATE USER normal_user WITH PASSWORD 'password';

4. Grant SELECT privilege on name and department only

   GRANT SELECT (name, department)
   ON employees
   TO normal_user;

5. Testing user permissions Log in as the normal user and test queries:

   SET ROLE normal_user;
    
   SELECT * FROM employees;     -- Fails with `permission denied for table employees` because of lack of access to salary column
   SELECT name, department FROM employees;   -- Succeeds

6. Revoke specific access

   REVOKE SELECT (department)
   ON employees
   FROM normal_user;

Notes

1. Column-level permissions have the following restrictions:

   • INSERT and DELETE are always controlled by table-level privileges.
   • Partitioned child tables do not inherit column-level privileges automatically; permissions must be set explicitly on each partition.
   • Foreign key constraints do not inherit column-level privileges and might expose certain column data. Additional REFERENCE privilege is required.

2. Privilege precedence:

   • If permission rules conflict, "deny" permissions override "grant" permissions.

3. Granting permission on the full table, for example:

   GRANT SELECT ON employees TO normal_user;

   – allows the user to access all columns and overrides any column-level restrictions.

For more information, see GRANT and REVOKE.