Column-level security

Column-level security allows you to create a security policy for table columns allowing or restricting the user to view a specific column or set of columns. This policy allows blocking access to the columns, including such operations as SELECT.

The article provides an example of creating a column-level security policy to delimit user access.

You can implement column-level security in three methods:

  • table view;

  • column-level permissions;

  • column-level encryption.

Preparing steps

  1. Get access to the ADPG server console by logging in with a trusted account.

  2. Switch to the user postgres.

    $ sudo su - postgres

  3. Go to the ADPG interactive terminal.

    $ psql

  4. Create the table demo2.

    CREATE TABLE demo2 ( numb int, role text, name text, param int );
    Result:
    postgres=# CREATE TABLE demo 2 ( numb int, role text, name text, perm int );
CREATE TABLE

  5. Add data to the first row.

    INSERT INTO demo2 VALUES (1, 'user', 'student01', 1 );
    Result:
    postgres=# INSERT INTO demo2 VALUES (1, 'user', 'student01', 1 );
INSERT 0 1

  6. Add data to the second row.

    INSERT INTO demo2 VALUES (2, 'non-user', 'student02', 0 );
    Result:
    postgres=# INSERT INTO demo 2 VALUES (2, 'non-user', 'student02', 0 );
INSERT 0 1

  7. Output data from the table.

    SELECT * FROM demo2;
    Result:
    postgres=# SELECT * FROM demo2;
 number |   role   |   name    | perm
------+----------+-----------+------
    1 | user     | student01 |    1
    2 | non-user | student02 |    0
(2 rows)

  8. Create the teacher user.

    CREATE ROLE teacher;
    Result:
    postgres=# CREATE ROLE teacher;
CREATE ROLE

After trying to establish a session by a user, the following error might appear in the console:

connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: role "teacher" isn't permitted to log in

Change the attributes of this role and grant the necessary privilege:

ALTER ROLE teacher WITH LOGIN;
Result
postgres=# ALTER ROLE teacher WITH LOGIN;
ALTER ROLE

To make queries to the demo2 table, for example, SELECT, run the query:

GRANT SELECT ON demo2 TO teacher;
Result:
postgres=# GRANT SELECT ON demo2 TO teacher;
GRANT

Table view method

The table view method allows you to display only the columns available to the user and provide the view name to the user instead of the table name.

The teacher user was created in the Preparing steps section. In this example, the user shouldn’t see the perm column in the table. To do this, revoke the teacher user’s access to the table and create a view with available columns.

  1. Revoke access to the table.

    REVOKE SELECT ON demo2 FROM teacher;
    Result:
    postgres=# REVOKE SELECT ON demo2 FROM teacher;
REVOKE

  2. Create the table view demo2 with available columns.

    CREATE VIEW demo2_info AS SELECT numb, role, name FROM demo2;
    Result:
    postgres=# CREATE VIEW demo2_info AS SELECT numb, role, name FROM demo2;
CREATE VIEW

  3. Grant accesses to the table view.

    GRANT SELECT ON demo2_info TO teacher;
    Result:
    postgres=# GRANT SELECT ON demo2_info TO teacher;
GRANT

  4. Connect to the database as the teacher user.

    \c postgres teacher
    Result:
    postgres=# \c postgres teacher
You are now connected to database "postgres" as user "teacher".

  5. Output data from the table.

    SELECT * FROM demo2;
    Result:
    postgres=> SELECT * FROM demo2;
ERROR:  permission denied for table demo2

  6. Output data from the table view.

    SELECT * FROM demo2_info;
    Result:
    postgres=> SELECT * FROM demo2_info;
numb |   role   |   name
------+----------+-----------
1 | user     | student01
2 | non-user | student02
(2 rows)

As you can see, the demo2 table isn’t available to the teacher user. When executing the SELECT query to the created demo2_info view, the perm column is unavailable to the teacher user.

Column-level permissions method

This method is based on granting access only to certain columns of the table for dedicated users.

In the Table view method section the teacher user was denied access to the demo2 table. Only the specified columns will be available, but when using the method column-level permissions, the teacher user is granted the right to access the table.

  1. Grant the teacher user the right to access the numb, role, and name columns of the demo2 table.

    GRANT SELECT (numb, role, name) ON demo2 TO teacher;
    Result:
    postgres=# GRANT SELECT (numb, role, name) ON demo2 TO teacher;
GRANT

  2. Connect to the database as the teacher user.

    \c postgres teacher
    Result:
    postgres=# \c postgres teacher
You are now connected to database "postgres" as user "teacher".

  3. Request to view all the columns of the table.

    SELECT numb, role, name, perm FROM demo2;
    Result:
    postgres=> SELECT numb, role, name, perm FROM demo2;
ERROR:  permission denied for table demo2

    When executing the request, the teacher user was denied access because there is no permission to access all columns of the table.

  4. Request to view available columns of the table.

    SELECT numb, role, name FROM demo2;
    Result:
    postgres=> SELECT numb, role, name FROM demo2;
 numb |   role   |   name
------+----------+-----------
    1 | user     | student01
    2 | non-user | student02
(2 rows)

    The teacher user has access to the columns of the demo2 table, except the perm column.

The user shouldn’t have GRANT access to the table if you need to implement the column-level permissions method. If such access was already granted, it must be revoked via the REVOKE operator. For example, use the REVOKE SELECT ON demo2 FROM teacher query command.

Column-level encryption method

The use of the column-level encryption method is based on column data encryption, with the user getting access to all table columns yet not being able to see the encrypted data. The pgcrypto extension is used to implement this method.

To use this method, you need to install the extension for the ADPG service contrib.

  1. Grant the teacher user access to the demo2 table.

    GRANT SELECT ON demo2_info TO teacher;
    Result:
    postgres=# GRANT SELECT ON demo2 TO teacher;
GRANT

  2. Connect to the database as the teacher user.

    \c postgres teacher
    Result:
    postgres=# \c postgres teacher
You are now connected to database "postgres" as user "teacher".

  3. Check access to the information in the table.

    SELECT * FROM demo2;
    Result:
    postgres=> SELECT * FROM demo2;
 numb |   role   |   name    | perm
------+----------+-----------+------
    1 | user     | student01 |    1
    2 | non-user | student02 |    0
(2 rows)

  4. Connect to the database as the postgres user.

    \c postgres postgres
    Result:
    postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".

  5. Create a pgcrypto extension.

    CREATE EXTENSION pgcrypto;
    Result:
    postgres=# CREATE EXTENSION pgcrypto;
CREATE EXTENSION

  6. Create the methodist user.

    CREATE USER methodist;
    Result:
    postgres=# CREATE USER methodist;
CREATE ROLE

  7. Grant the methodist user permission to access the demo2 table.

    GRANT SELECT ON demo2 TO methodist;
    Result:
    postgres=# GRANT SELECT ON demo TO student01;
GRANT

  8. Encrypt the data of the name column using the encryption key.

    UPDATE demo2 SET name = pgp_sym_encrypt('student01','demo_sec_key') WHERE name = 'student01';
UPDATE demo2 SET name = pgp_sym_encrypt('student02','demo_sec_key') WHERE name = 'student02';
    Result:
    UPDATE 1
    CAUTION
    In this example, demo_sec_key is the encryption key. Don’t transfer the encryption key to the third parties.

  9. Connect to the database as the methodist user.

    \c postgres methodist
    Result:
    postgres=> \c postgres methodist
You are now connected to database "postgres" as user "methodist".

  10. Check access to the information in the table.

    SELECT * FROM demo2;
    Result:
    postgres=> SELECT * FROM demo2;
numb |   role   |                                                                           name                                                                           | perm
------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------+------
1 | user     | \xc30d04070302040d5a2793e68b4a79d23a01742b4db830ffffb32e0d11edb78b720a91e4f9668fba7bad83000de2bd97502b678be31ac2227a3dfa72b94a374b720b06491573fd25eb08da |    1
2 | non-user | \xc30d040703021cc0ce1790a51a2978d23a01932226cb250e6c3707bb33176da2cf3d8aeaedd4916d77a3dc6e2cac21bc8411a1ba79d372394826a5b242a5e41d373a8de60c21f36af6315d |    0
(2 rows)

    You can see from the example that the methodist user doesn’t see encrypted information from the name column. The column name is also unavailable.

  11. To view the encrypted information in the name column, run a query that includes the encrypted key:

    SELECT numb, pgp_sym_decrypt(name::bytea,'demo_sec_key') FROM demo2;
    Result:
    postgres=> SELECT numb, pgp_sym_decrypt(name::bytea,'demo_sec_key') FROM demo2;
 numb | pgp_sym_decrypt
------+-----------------
    1 | student01
    2 | student02
(2 rows)

    After inserting the encrypted key into the request command, the encrypted information in the name column has become available to the methodist user.

    NOTE
    In this example, some columns were hidden.

If an incorrect encrypted key is used when accessing the table, the access request will be rejected.

SELECT numb, pgp_sym_decrypt(name::bytea,'not-a-demo_sec_key') FROM demo2;
Result:
postgres=> SELECT numb, pgp_sym_decrypt(name::bytea,'not-a-demo_sec_key') FROM demo2;
ERROR: Wrong key or corrupt data
Found a mistake? Seleсt text and press Ctrl+Enter to report it