Granting user access to specific columns

A user can be given access to only some table columns.

Getting ready

We will continue the example from the previous recipe, so we assume that there is already a schema called someschema and a role called somerole, with USAGE privileges on it. We create a new table on which we will grant column-level privileges:

CREATE TABLE someschema.sometable2(col1 int, col2 text);

How to do it…

We want to grant somerole the ability to view existing data and insert new data; we also want to provide the ability to amend existing data, limited to the col2 column only. We use the following self-evident statements:

GRANT SELECT, INSERT ON someschema.sometable2
TO somerole;
GRANT UPDATE (col2) ON someschema.sometable2
TO somerole;

We can then test whether this has worked successfully as follows:

1. Let’s assume the identity of the somerole role and test these privileges with the following commands:

   SET...