Compliant Kubernetes Service documentation has moved

Please note: You are not reading Kubernetes documentation. If you're looking for Compliant Kubernetes Service documentation, it has moved. Read more here.

Overview

Implementing the principle of least privilege (POLP) is an important step in securing your data. PostgreSQL allows for fine grained access controls through the use of roles and views. A common example is enabling a Data Analyst or a reporting application to have read access, but preventing any writes. This guide will show how to create and manage readonly users through the use of roles.

Quick Guide

This quick guide serves to provide a concise overview of the critical steps in creating and managing readonly users. 

catalyzeDB=# -- Initial Setup
catalyzeDB=# CREATE ROLE readonly;
catalyzeDB=# CREATE USER user1;
catalyzeDB=# GRANT readonly TO user1;
catalyzeDB=# ALTER DEFAULT PRIVILEGES FOR ROLE catalyze GRANT SELECT ON TABLES TO readonly;
catalyzeDB=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

catalyzeDB=# -- You must specifically grant usage to a schema to allow read access.
catalyzeDB=# CREATE SCHEMA schema1;
catalyzeDB=# GRANT USAGE ON SCHEMA schema1 TO readonly;

Detailed Guide

In this detailed guide we will not only walk through the steps above, but we'll also explore what each command does and why it's important for implementing readonly users.

First, let's create a table and display it's privileges.

catalyzeDB=# CREATE TABLE test1 (col text);
CREATE TABLE
catalyzeDB=# \dp test1
  Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-------+-------+-------------------+-------------------+----------
public | test1 | table | | |
(1 row)

You can see there are no access privileges assigned to the table we created. We may want to set it up so any NEW tables created have some default set of privileges. We can accomplish this with the ALTER DEFAULT PRIVILEGES command.

You can run the ALTER DEFAULT PRIVILEGES command on two types of objects:

ROLES: Any new objects created by the role will have the specified default privileges.

SCHEMAS: Any new objects created inside the schema will have the specified default privileges.

The alter default privileges command allows you to define the default privileges of the following object types: TABLES, SEQUENCES, FUNCTIONS, and TYPES. It's important to note, you cannot define default privileges for schemas. Any new schemas created must have privileges added after its creation. Let's look at altering the default privileges of the catalyze user so it grants read access to a readonly role by default.

It is generally a good idea to grant permissions to roles rather than individual user accounts. We'll create a readonly role to which access can be granted. Then we'll create a new user and grant the user access to the role.

catalyzeDB=# CREATE ROLE readonly;
CREATE ROLE
catalyzeDB=# CREATE USER user1;
CREATE ROLE
catalyzeDB=# GRANT readonly TO user1;
GRANT ROLE

Now let's set the default privileges for any new tables created by the catalyze user to allow read access to those tables.

catalyzeDB=# ALTER DEFAULT PRIVILEGES FOR ROLE catalyze GRANT SELECT ON TABLES TO readonly;
ALTER DEFAULT PRIVILEGES

Now create another table and look at the permissions.

catalyzeDB=# CREATE TABLE test2 (col text);
CREATE TABLE
catalyzeDB=# \dp test*
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-------+-------+---------------------------+-------------------+----------
public | test1 | table | | |
public | test2 | table | catalyze=arwdDxt/catalyze+| |
| | | readonly=r/catalyze | |
(2 rows)

We can see the readonly role has only the read privilege. Also notice table test1 does not allow read privileges to the readonly role. We can confirm this is the case by switching users to user1 and trying to read from both tables.

catalyzeDB=# SET ROLE user1;
SET
catalyzeDB=> SELECT * FROM test1;
ERROR: permission denied for relation test1
catalyzeDB=> SELECT * FROM test2;
col
-----
(0 rows)

We can allow read access to table1 with the GRANT command.

catalyzeDB=> SET ROLE catalyze;
SET
catalyzeDB=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT
catalyzeDB=# \dp test*
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-------+-------+---------------------------+-------------------+----------
public | test1 | table | catalyze=arwdDxt/catalyze+| |
| | | readonly=r/catalyze | |
public | test2 | table | catalyze=arwdDxt/catalyze+| |
| | | readonly=r/catalyze | |
(2 rows)

This is a common method of managing access to tables. First, set the access policy for future tables created by a user. Then, use GRANT adjust the access policy for existing tables.

Now let's take a look at how to create and manage new schemas. First, create the new schema and list the schemas.

catalyzeDB=# CREATE SCHEMA schema1;
CREATE SCHEMA
catalyzeDB=# \dn+
List of schemas
Name | Owner | Access privileges | Description
---------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
schema1 | catalyze | |
(2 rows)

Now let's create a new table inside the new schema and list the privileges.

catalyzeDB=# create table schema1.test1 (col text);
CREATE TABLE
catalyzeDB=# \dp *.test*
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
---------+-------+-------+---------------------------+-------------------+----------
public | test1 | table | catalyze=arwdDxt/catalyze+| |
| | | readonly=r/catalyze | |
public | test2 | table | catalyze=arwdDxt/catalyze+| |
| | | readonly=r/catalyze | |
schema1 | test1 | table | catalyze=arwdDxt/catalyze+| |
| | | readonly=r/catalyze | |
(3 rows)

Thanks to the default privileges, the new table has the correct privileges for the readonly role. Let's switch to user1 and see what happens when we try to read from it.

catalyzeDB=# SET ROLE user1;
SET
catalyzeDB=> SELECT * FROM schema1.test1;
ERROR: permission denied for schema schema1
LINE 1: select * from schema1.test1 ;

We can't read from the new table because we haven't been given access to the schema. Recall from above, this is because PostgreSQL doesn't allow default permissions to be granted on schemas. Let's grant the readonly role access to use the new schema.

catalyzeDB=# SET ROLE catalyze;
SET
catalyzeDB=# GRANT USAGE ON SCHEMA schema1 TO readonly;
GRANT
catalyzeDB=# \dn+
List of schemas
Name | Owner | Access privileges | Description
---------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres + |
| | readonly=U/postgres |
schema1 | catalyze | catalyze=UC/catalyze+|
| | readonly=U/catalyze |
(2 rows)

Switch roles back to user1 and you'll find you can read from the table under the new schema.

catalyzeDB=# SET ROLE user1;
SET
catalyzeDB=> SELECT * FROM schema1.test1;
col
-----
(0 rows)