Column-level security

PostgreSQL allows you to restrict access to a particular column or set of columns for a user. This way, the user cannot view or modify column data.

You can use the following options to implement column-level security:

Connect to the database as the postgres user and create a table for examples. You can use psql for this:

$ psql -h 10.92.6.36 -p 5432 -U postgres postgres

In the code above, 10.92.6.36 is the server address, 5432 is the port for connections, -U postgres is a user name, and postgres is the database name.

CREATE TABLE users (
user_id serial PRIMARY KEY,
user_name VARCHAR NOT NULL,
real_name VARCHAR NOT NULL,
address VARCHAR,
salary INT
);

Insert data to the table:

INSERT INTO users (user_name, real_name, address, salary)
    VALUES
   ('james', 'James Brown', '123 2nd Street', 5000),
   ('mary', 'Mary Smith', '456 13th Street', 6000),
   ('alice', 'Alice Gray', '789 19th Street', 5700);

Create roles:

CREATE ROLE user1 WITH LOGIN PASSWORD 'password1';
CREATE ROLE user2 WITH LOGIN PASSWORD 'password2';

Create a table view

You can create a view that includes only the allowed columns and grant the user access to this view, and not to the underlying table. For example, you can hide the salary column from user1. Create a view with the user_name, real_name, and address columns and grant user1 privileges on this view:

CREATE VIEW users_info AS SELECT user_name, real_name, address FROM users;
GRANT ALL ON users_info TO user1;

Reconnect to the database as user1:

$ psql -h 10.92.6.36 -p 5432 -U user1 postgres

Select data from the users_info view:

SELECT * FROM users_info;

Since user1 has permissions to this view, the result is the following:

 user_name |  real_name  |     address
-----------+-------------+-----------------
 james     | James Brown | 123 2nd Street
 mary      | Mary Smith  | 456 13th Street
 alice     | Alice Gray  | 789 19th Street

If you try to select all data from the users table on behalf of user1, an error occurs, because user1 does not have access rights to the entire table.

SELECT * FROM users;

The result:

ERROR: permission denied for table users

Grant access to specific columns

You can grant access to a user only to certain columns of a table. For example, you can hide the salary column from user2. Reconnect to the database as the postgres user and add permissions to user2 for all columns of the users table except salary:

$ psql -h 10.92.6.36 -p 5432 -U postgres postgres
GRANT SELECT (user_name, real_name, address) ON users TO user2;

Reconnect to the database as user2:

$ psql -h 10.92.6.36 -p 5432 -U user2 postgres

Select data from allowed columns of the users table:

SELECT user_name, real_name, address FROM users;

The result:

 user_name |  real_name  |     address
-----------+-------------+-----------------
 james     | James Brown | 123 2nd Street
 mary      | Mary Smith  | 456 13th Street
 alice     | Alice Gray  | 789 19th Street

Note, if user2 tries to select all columns from the users table, an error occurs:

SELECT * FROM users;

The result:

ERROR: permission denied for table users

Use column encryption

It is also possible to encrypt specific database columns to protect data. You can use an additional pgcrypto module for this. For information on how to install and use this module, see Encryption.

The pgp_sym_encrypt and pgp_sym_decrypt function from the pgcrypto module are used in the example below.

Reconnect to the database as user1 and create the users_encryption table:

$ psql -h 10.92.6.36 -p 5432 -U user1 postgres
CREATE TABLE users_encryption (
user_id serial PRIMARY KEY,
user_name VARCHAR NOT NULL,
real_name VARCHAR NOT NULL,
address VARCHAR,
salary TEXT
);

Add data and encrypt the salary column using the pgp_sym_encrypt function:

INSERT INTO users_encryption (user_name, real_name, address, salary)
    VALUES
   ('james', 'James Brown', '123 2nd Street', pgp_sym_encrypt('5000','secret_encryption_key')),
   ('mary', 'Mary Smith', '456 13th Street', pgp_sym_encrypt('6000','secret_encryption_key')),
   ('alice', 'Alice Gray', '789 19th Street', pgp_sym_encrypt('5700','secret_encryption_key'));

Select data to check if the salary column is encrypted:

SELECT * FROM users_encryption;

The result:

user_id | user_name |  real_name  |     address     |            salary
--------+-----------+-------------+-----------------+-----------------------------------------------
      1 | james     | James Brown | 123 2nd Street  | \xc30d04070302ec4106d9f16a58767ed23501d9679...
      2 | mary      | Mary Smith  | 456 13th Street | \xc30d04070302a972f63c44aa0b8270d23501ce960...
      3 | alice     | Alice Gray  | 789 19th Street | \xc30d04070302edf9f8629c0fb48669d23501560fe...

Reconnect to the database as the postgres user and add permissions to user2 for the users_encryption table:

$ psql -h 10.92.6.36 -p 5432 -U postgres postgres
GRANT ALL ON users_encryption TO user2;

Reconnect as user2 and select data:

$ psql -h 10.92.6.36 -p 5432 -U user2 postgres
SELECT * FROM users_encryption;

The result is the same:

user_id | user_name |  real_name  |     address     |            salary
--------+-----------+-------------+-----------------+-----------------------------------------------
      1 | james     | James Brown | 123 2nd Street  | \xc30d04070302ec4106d9f16a58767ed23501d9679...
      2 | mary      | Mary Smith  | 456 13th Street | \xc30d04070302a972f63c44aa0b8270d23501ce960...
      3 | alice     | Alice Gray  | 789 19th Street | \xc30d04070302edf9f8629c0fb48669d23501560fe...

Execute the SELECT statement with the pgp_sym_decrypt function to view decrypted data from the salary column:

SELECT user_id, user_name, real_name, address, pgp_sym_decrypt(salary::bytea, 'secret_encryption_key') AS salary FROM users_encryption;

The result:

user_id | user_name |  real_name  |     address     | salary
---------+-----------+-------------+-----------------+--------
       1 | james     | James Brown | 123 2nd Street  | 5000
       2 | mary      | Mary Smith  | 456 13th Street | 6000
       3 | alice     | Alice Gray  | 789 19th Street | 5700

Note, any user that has the encryption key can decrypt the column data. In the example above, user1 encrypts data and user2 decrypts data using the encryption key.

Found a mistake? Seleсt text and press Ctrl+Enter to report it