PostgreSQL Privileges and Security – Locking Down the Public Schema

Introduction

In a previous article we introduced the basics of understanding PostgreSQL schemas, the mechanics of creation and deletion, and reviewed several use cases. This article will extend upon those basics and explore managing privileges related to schemas.

More Terminology Overloading

But there is one preliminary matter requiring clarification. Recall that in the previous article, we dwelt on a possible point of confusion related to overloading of the term “schema”. The specialized meaning of that term in the context of PostgreSQL databases is distinct from how it is generally used in relational database management systems. We have another similar possible terminology kerfuffle for the present topic related to the word “public”.

Upon initial database creation, the newly created Postgresql database includes a pre-defined schema named “public”. It is a schema like any other, but the same word is also used as a keyword that denotes “all users” in contexts where otherwise an actual role name might be used, such as … wait for it … schema privilege management. The significance and two distinct uses will be clarified in examples below.

Querying Schema Privileges

Before making this concrete with example code to grant and revoke schema privileges, we need to review how to examine schema privileges. Using the psql command line interface, we list the schemas and associated privileges with the dn+ command. For a newly-created sampledb database we see this entry for the public schema:

sampledb=# dn+ 
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description      
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         |
(1 row)

The first two and the fourth columns are pretty straightforward: as mentioned previously showing the default-created schema named “public”, described as “standard public schema”, and owned by the role “postgres”. (The schema ownership, unless specified otherwise, is set to the role which creates the schema.) That third column listing the access privileges is of interest here. The format of the privilege information provides three items: the privilege grantee, the privileges, and privilege grantor in the format “grantee=privileges/grantor” that is, to the left of the equality sign is the role receiving the privilege(s), immediately to the right of the equality sign is a group of letters specifying the particular privilege(s), and lastly following the slash the role which granted to privilege(s). There may be multiple such privilege information specifications, listed separated by a plus sign since privileges are additive.

For schemas, there are two possible privileges which may be granted separately: U for “USAGE” and C for “CREATE”. The former is required for a role to have the ability to lookup database objects such as tables and views contained in the schema; the latter privilege allows for a role to create database objects in the schema. There are other letters for other privileges relating to different types of database objects, but for schemas, only U and C apply.

Thus to interpret the privilege listing above, the first specification tells us that the postgres user was granted the update and create privileges by itself on the public schema.

Notice that for the second specification above, an empty string appears to the left of the equal sign. This is how privileges granted to all users, by means of the PUBLIC key word mentioned earlier, is denoted.

This latter specification of granting usage and create privileges on the public schema to all users is viewed by some as possibly contrary to general security principles best practices, where one might prefer to start with access restricted by default, requiring the database administrator to explicitly grant appropriate and minimally necessary access privileges. These liberal privileges on the public schema are purposely configured in the system as a convenience and for legacy compatibility.

Note also that except for the permissive privilege settings, the only other thing special about the public schema is that it also listed in the search_path, as we discussed in the previous article. This is similarly for convenience: The search_path configuration and liberal privileges together result in a new database being usable as if there was no such concept as schemas.

Historical Background on the Public Schema

This compatibility concern originates from about fifteen years ago (prior to PostgreSQLversion 7.3, cf. version 7.3 release notes) when the schema feature was not part of PostgreSQL. Configuration of the public schema with liberal privileges and the search_path presence when schemas were introduced in version 7.3 allowed for compatibility of older applications, which are not schema-aware, to function unmodified with the upgraded database feature.

Otherwise there is nothing else particularly special about the public schema: some DBA’s delete it if their use case presents no requirement for it; others lock it down by revoking the default privileges.

Show Me the Code – Revoking Privileges

Let’s do some code to illustrate and expand on what we have discussed so far.

Schema privileges are managed with the GRANT and REVOKE commands to respectively add and withdraw privileges. We’ll try some specific examples for locking down the public schema, but the general syntax is:

REVOKE [ GRANT OPTION FOR ]
    { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

So, as an initial lock down example, let’s remove the create privilege from the public schema. Note that in these examples the lowercase word “public” refers to the schema and could be replaced by any other valid schema name that might exist in the database. The uppercase “PUBLIC” is the special keyword that implies “all users” and could instead be replaced with a specific role name or comma-separated list of role names for more fine-grained access control.

sampledb=# REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE
sampledb=# dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description       
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =U/postgres          | 
(1 row)

The only difference in this listing of schema privileges from the first is the absence of the “C” in the second privilege specification, verifying our command was effective: users other than the postgres user may no longer create tables, views, or other objects in the public schema.

Note that the above command revoking create privileges from the public schema is the recommended mitigation for a recently published vulnerability, CVE-2018-1058, which arises from the default privilege setting on the public schema.

A further level of lock down could entail denying lookup access to the schema entirely by removing the usage privilege:

sampledb=# REVOKE USAGE ON SCHEMA public FROM PUBLIC;
REVOKE
sampledb=# dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description       
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres | standard public schema
(1 row)

Since all available schema privileges for non-owner users have been revoked, the entire second privilege specification disappears in the listing above.

What we did with two separate commands could have been succinctly accomplished with a single command specifying all privileges as:

sampledb=# REVOKE ALL PRIVILEGES ON SCHEMA public FROM PUBLIC;
REVOKE

Additionally, it is also possible to revoke privileges from the schema owner:

sampledb=# REVOKE ALL PRIVILEGES ON SCHEMA public FROM postgres;
REVOKE
sampledb=# dn+
                        List of schemas
  Name  |  Owner   | Access privileges |      Description       
--------+----------+-------------------+------------------------
 public | postgres |                   | standard public schema
(1 row)

but that does not really accomplish anything practical, as the schema owner retains full privileges to owned schemas regardless of explicit assignment simply by virtue of ownership.

The liberal privilege assignment for the public schema is a special artifact associated with initial database creation. Subsequently-created schemas in an existing database do conform with the best practice of starting without assigned privileges. For example, examining schema privileges after creating a new schema named “private” shows the new schema has no privileges:

sampledb=# create schema private;
CREATE SCHEMA
sampledb=# dn+
                          List of schemas
  Name   |  Owner   |  Access privileges   |      Description       
---------+----------+----------------------+------------------------
 private | postgres |                      | 
 public  | postgres |                      | standard public schema
(2 rows)

Show Me the Code – Granting Privileges

The general form of the command to add privileges is:

GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
where role_specification can be:
  [ GROUP ] role_name
  | PUBLIC
  | CURRENT_USER
  | SESSION_USER

Using this command we can, for example, allow all roles to lookup database objects in the private schema by adding the usage privilege with

sampledb=# GRANT USAGE ON SCHEMA private TO PUBLIC;
GRANT
sampledb=# dn+
                          List of schemas
  Name   |  Owner   |  Access privileges   |      Description       
---------+----------+----------------------+------------------------
 private | postgres | postgres=UC/postgres+| 
         |          | =U/postgres          | 
 public  | postgres |                      | standard public schema
(2 rows)

Note how the UC privileges appear for the postgres owner as the first specification, now that we have assigned other-than-default privileges to the schema. The second specification, =U/postgres, corresponds to the GRANT command we just invoked as user postgres granting usage privilege to all users (where, recall, the empty string left of the equal sign implies “all users”).

A specific role, named “user1” for example, can be granted both create and usage privileges to the private schema with:

sampledb=# GRANT ALL PRIVILEGES ON SCHEMA private TO user1;
GRANT
sampledb=# dn+
                          List of schemas
  Name   |  Owner   |  Access privileges   |      Description       
---------+----------+----------------------+------------------------
 private | postgres | postgres=UC/postgres+| 
         |          | =U/postgres         +| 
         |          | user1=UC/postgres    | 
 public  | postgres |                      | standard public schema
(2 rows)

We have not yet mentioned the “WITH GRANT OPTION” clause of the general command form. Just as it sounds, this clause permits a granted role the power to itself grant the specified privilege to other users, and it is denoted in the privilege listing by asterisks appended to the specific privilege:

sampledb=# GRANT ALL PRIVILEGES ON SCHEMA private TO user1 WITH GRANT OPTION;
GRANT
sampledb=# dn+
                          List of schemas
  Name   |  Owner   |  Access privileges   |      Description       
---------+----------+----------------------+------------------------
 private | postgres | postgres=UC/postgres+| 
         |          | =U/postgres         +| 
         |          | user1=U*C*/postgres  | 
 public  | postgres |                      | standard public schema
(2 rows)

Conclusion

This wraps up the topic for today. As a final note, though, remember that we have discussed only schema access privileges. While the USAGE privilege allows lookup of database objects in a schema, to actually access the objects for specific operations, such as reading, writing, execution, and etc., the role must also have appropriate privileges for those operations on those specific database objects.