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 postgresIn 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 postgresSelect 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 postgresGRANT 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 postgresSelect 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 postgresCREATE 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 postgresGRANT ALL ON users_encryption TO user2;Reconnect as user2 and select data:
$ psql -h 10.92.6.36 -p 5432 -U user2 postgresSELECT * 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.