Implementing Row-Level and Column-Level Security in PostgreSQL

The author, a PostgreSQL/Greenplum operations engineer at Qunar, describes a common business requirement: limiting users to view only rows or columns containing data they are authorized to see.

PostgreSQL offers comprehensive access‑control mechanisms, including row‑level security (RLS) introduced in version 9.6, which acts as a filter that applies a policy to each query.

Row‑Level Security Example

CREATE TABLE passwd (
  id bigserial primary key,
  user_name varchar(32) UNIQUE NOT NULL,
  pwhash varchar(32),
  real_name varchar(32) NOT NULL,
  home_phone varchar(12),
  home_dir text NOT NULL,
  shell text NOT NULL
);
-- Insert sample rows
INSERT INTO passwd (user_name,pwhash,real_name,home_phone,home_dir,shell) VALUES
  ('appuser','xxxx','appuser','111-222-3333','/root','/bin/dash'),
  ('appuser1','xxxx','appuser1','123-456-7890','/home/appuser1','/bin/zsh'),
  ('appuser2','xxxx','appuser2','098-765-4321','/home/appuser2','/bin/zsh');

After creating users, the author demonstrates how a regular user sees only their own row, while a superuser can view all rows. The ALTER TABLE passwd ENABLE ROW LEVEL SECURITY; command activates RLS, and a policy is created with CREATE POLICY passwd_rls_policy ON passwd FOR ALL TO PUBLIC USING (user_name = current_user); .

The BYPASSRLS attribute, granted only to superusers, allows a role to ignore RLS policies. The article shows how to grant it with ALTER USER appuser1 BYPASSRLS; and how disabling the policy or using ALTER TABLE passwd DISABLE ROW LEVEL SECURITY; restores full access.

Column‑Level Security

Column‑level security can be achieved by creating views that expose only selected columns or by revoking table privileges and granting column‑specific SELECT rights. Example:

REVOKE ALL ON passwd FROM public;
GRANT SELECT(user_name,home_dir) ON passwd TO appuser2;

When appuser2 attempts SELECT * FROM passwd; it receives a permission error, but SELECT user_name,home_dir FROM passwd; succeeds, demonstrating column‑level restriction.

Combining Row and Column Security

By setting a session variable ( SET rls.user_name = 'appuser1'; ) and using a policy that references this variable ( USING (user_name = current_setting('rls.user_name')) ), the author shows how to enforce both row and column restrictions simultaneously.

Conclusion

While RLS adds a WHERE clause to every query and can impact performance, it provides fine‑grained access control. The article advises evaluating the trade‑offs and applying row‑level security judiciously based on real‑world scenarios.