PostgreSQL Deep Dive: PostgreSQL Defaults and Impact on Security - Part 1

Recently I gave a "deep dive" talk on the topic of PostgreSQL security, and I wanted to capture one part of that content into a blog since this format is both better for making that content stand on its own and for expanding on it a bit.

Specifically, in this two-part blog, we will cover a PostgreSQL extension that I wrote called crunchy_check_access -- the motivation behind it and what it does -- and then use that extension to probe and understand the consequences of the default, out of the box, security of PostgreSQL. We will also examine the implications of choices we make when setting up "users" and "groups". To cap it off, we will explore the situation identified in CVE-2018-1058 and discuss how to protect yourself.

Warning: this two-part blog is a wall of text (and SQL). It would probably be more suited as a chapter in a long book. I encourage you to stick with it though, as I believe even those with a great deal of PostgreSQL experience will find the content very enlightening. At a minimum it will probably remind you of things you once learned but have since forgotten.

Attributes and Privileges and Settings, Oh My...

Before diving headlong into the deep end, we need to define some terms and level set on precisely how things work in PostgreSQL. Some of this will be covered again in later content, but this section will be a good central reference if you get lost in the depths.

In PostgreSQL, "users" and "groups" are really just two different forms of "roles". The only real difference is that a "user" has the ability to login, whereas a "group" does not. But as we will see, it is entirely possible for a "user" to have "members", just like a "group" would.

Roles are created at the "instance" level -- in other words they are not unique to the database you were logged into when you created them. They are common to all databases for a given PostgreSQL installation.

Roles have four types of relevant properties:

• Attributes - a capability of the role, for example LOGIN (the ability to log in) or SUPERUSER.
• Membership - as discussed above, one role may be a member of another, directly or indirectly.
• Privileges - a type of access permitted on a database object, such as SELECT on a table.
• Settings - a custom value for a PostgreSQL configuration variable bound to a particular role, for example a particular search_path setting.

The list of attributes available to a role is most easily seen as "options" in the PostgreSQL documentation for the CREATE ROLE command. These attributes are role specific, and controlled via the CREATE ROLE and ALTER ROLE commands.

There are several ways to make a particular role be a member of another "group" role. The preferred method is to use the role form of the GRANT command as discussed in the PostgreSQL reference documentation. It is possible to create a multi-level hierarchy of roles. Role "X" is said to be a MEMBER of role "Y" if there is a chain of grants between them. Additionally, role "X" has USAGE of "Y", if "X", and all intermediate roles (but not necessarily "Y" itself), are marked with the INHERIT attribute (which is the default). See pg_has_role for a simple method to determine if the current user has MEMBER and/or USAGE of another role.

Privileges are gained either through system defaults or explicit GRANT statements. However as we will discuss in much greater detail later, a role may gain privileges indirectly via group role membership when it has USAGE on another, directed granted, role. Also related to group roles is the concept of the PUBLIC group, in which every role is automatically a member. Certain privileges are given by default to PUBLIC, and therefore indirectly to every role created unless specific action (again, discussed later) is taken. We will also see later that PUBLIC does not act exactly like a group role in every respect.

Finally, specific values of PostgreSQL configuration settings may be bound to roles via the ALTER ROLE command with a SET clause.

Assuming a Role

It is important to understand that attributes of a given role may only be gained by logging in as that role directly, by using SET ROLE to switch that role, or by using SET SESSION AUTHORIZATION.

That last command is only available to Superusers and effectively allows a Superuser to imitate another user more completely. Essentially SET ROLE changes the CURRENT_USER, while SET SESSION AUTHORIZATION changes both CURRENT_USER and SESSION_USER. When executing subsequent SET ROLE commands, the roles permitted are determined based on the memberships of the SESSION_USER. The practical impact of this is that when a Superuser first executes SET SESSION AUTHORIZATION any subsequent SET ROLE commands are restricted, whereas if a Superuser first executes SET ROLE, they can subsequently SET ROLE to any other role.

Note that custom configuration settings bound to a role are only applied when that role logs in directly. Neither SET ROLE nor SET SESSION AUTHORIZATION affect those values.

Database Setup and crunchy_check_access Installation

The extension crunchy_check_access provides functions and views to facilitate PostgreSQL object access inspection. The origin of it really goes back 20 years or so, when I developed a similar facility to inspect object access permissions for an ERP system. ERP stands for Enterprise Resource Planning, and those types of systems had (still have, I suppose) fairly extensive and fine grained object access permissions. And in a lot of ways PostgreSQL is similarly capable. The problem with that, is it can become very difficult to quickly and reliably determine who has access to what.

To illustrate, let's create a brand new database on a newly installed instance of PostgreSQL. Then we will install crunchy_check_access, create some roles, create some objects, and finally grant access to those objects.

The example code will assume that you have set up your environment such that psql somedb will log you into somedb as the PostgreSQL superuser, and psql -U someuser somedb will log you into somedb as the someuser user. Similarly other PostgreSQL command line utilities will run as the superuser when invoked without the -U switch.

It also assumes you are running on some reasonably recent Linux distribution. For the record I am running Linux Mint 19.1 and I have built and installed PostgreSQL 11 from source.

First, create your new PostgreSQL instance. I am not going to try to cover those details here, but my colleague Yogesh Sharma wrote a nice blog covering that on Red Hat (and derivative) systems.

Now, create a database for our use and login as superuser:

createdb deepdive
psql -l
                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
  deepdive  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
  postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
  template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
            |          |          |             |             | postgres=CTc/postgres
  template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
            |          |          |             |             | postgres=CTc/postgres
(4 rows)

psql deepdive

Notice that we used createdb at the shell command line to directly create the new database. We could have also logged in to the default postgres database and issued the following SQL: CREATE DATABASE deepdive. Also notice that the -l switch to psql is a convenient way to list all of the databases in our PostgreSQL instance. deepdive is what we just created and the other three are part of the default initialization of the PostgreSQL instance.

We are now ready to create some roles.

CREATE GROUP endusers NOINHERIT;
CREATE USER dbadm SUPERUSER PASSWORD 'secret';
CREATE USER joe PASSWORD 'secret' IN ROLE endusers;
CREATE ROLE bob LOGIN PASSWORD 'secret' NOINHERIT;
CREATE ROLE alice LOGIN PASSWORD 'secret' NOINHERIT IN ROLE endusers;
CREATE USER mary PASSWORD 'secret' IN ROLE joe;

CREATE ROLE sue LOGIN PASSWORD 'secret';
CREATE ROLE appuser LOGIN PASSWORD 'secret';
CREATE ROLE dbadmins ROLE sue ADMIN bob;
CREATE GROUP apps ROLE appuser;
GRANT joe TO alice;
GRANT dbadm TO endusers;

Let's see what the result of these commands is:

\du
                                              List of roles
  Role name |                         Attributes                         |   Member of
-----------+------------------------------------------------------------+----------------
  alice     | No inheritance                                             | {endusers,joe}
  apps      | Cannot login                                               | {}
  appuser   |                                                            | {apps}
  bob       | No inheritance                                             | {dbadmins}
  dbadm     | Superuser                                                  | {}
  dbadmins  | Cannot login                                               | {}
  joe       |                                                            | {endusers,dbadm}
  endusers  | No inheritance, Cannot login                               | {dbadm}
  mary      |                                                            | {joe}
  postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
  sue       |                                                            | {dbadmins}

Seemingly this is not too complex. But is it? So alice belongs to two "groups", but one of them is joe, who is actually a "user" at least in as much as the role is allowed to login.

Additionally some of these roles were created with NOINHERIT attribute. As discussed earlier, ordinarily, when one role is a member of a second role, the former gets immediate access to all of the privileges granted to the latter. However when a role has the NOINHERIT attribute, the privileges are not automatically possessed, and the first role must explicitly escalate to the second using SET ROLE in order to access them.

Note too that there are three different ways shown which affect role membership. When we do CREATE USER ... IN ROLE ..., we are saying that the new role we create shall be a member of some other role. Conversely when we CREATE ROLE ... ROLE ... the new role is essentially a "group", initially made up of the members specified by the ROLE ... clause. Finally when we run GRANT role1 TO role2 we are explicitly adding role2 as a member of role1. Whew, hopefully that is clear!

Anyway, now let's create some objects to play with:

CREATE TABLE t1 (t1_id int PRIMARY KEY, widgetname text);
CREATE TABLE t2 (t2_id int PRIMARY KEY, t1_id int REFERENCES t1, qty int, location text);
CREATE VIEW widget_inv AS SELECT widgetname, location, qty FROM t2 JOIN t1 USING (t1_id);
CREATE FUNCTION get_inv(wdgt text, loc text) RETURNS int AS
$$
  SELECT qty FROM widget_inv WHERE widgetname = wdgt AND location = loc
$$ LANGUAGE sql;
GRANT SELECT ON widget_inv TO apps, endusers;

Recall that we were still logged in as postgres, so the objects are also owned by postgres currently. We have done one explicit GRANT to two of our "groups".

Before moving on, test yourself -- who currently has permission for what in our freshly created database? We created 10 roles, 2 tables, one view, and 1 function, so it shouldn't be that hard to figure out, right?

Now install crunchy_check_access and see what the answer really is. There are multiple ways you might do that, but here is one -- first at the OS command line:

git clone https://github.com/CrunchyData/crunchy_check_access.git
cd crunchy_check_access
USE_PGXS=1 make install

Then run psql deepdive and do:

CREATE EXTENSION check_access;

Note that the extension repository is crunchy_check_access on GitHub but it is check_access in PostgreSQL. Also note that you will need the PostgreSQL "development" package installed if you are using a package management system such as yum or apt.

Using check_access to Explore Resulting Privileges

To see who has permission to what (ignoring the postgres superuser and system catalog for the moment) we can run the following:

SELECT *
FROM all_access()
WHERE base_role != CURRENT_USER;

What you will see is something like 559 rows of output. Surprised by the volume?

The detail is useful for some purposes, but to make it a bit easier to determine what is going on, try this query:

\x
SELECT objtype, schemaname, objname, privname, array_agg(base_role)
FROM all_access()
WHERE base_role != CURRENT_USER
GROUP BY objtype, schemaname, objname, privname
ORDER BY 1, 2, 3, 4;

Database Objects

That produces 104 rows of unique object type, schema, object name, privilege name combinations with an array of roles having that particular form of access. Let's examine these, one object type at a time. The first object we will examine is the database object:

-[ RECORD 1 ]-
objtype    | database
schemaname |
objname    | deepdive
privname   | CONNECT
array_agg  | {alice,alice,alice,alice,apps,appuser,bob,bob,
              dbadm,dbadmins,joe,endusers,endusers,mary,
              pg_execute_server_program,pg_monitor,pg_read_all_settings,
              pg_read_all_stats,pg_read_server_files,pg_signal_backend,
              pg_stat_scan_tables,pg_write_server_files,sue}
-[ RECORD 2 ]-
objtype    | database
schemaname |
objname    | deepdive
privname   | CONNECT WITH GRANT OPTION
array_agg  | {alice,dbadm,endusers}
-[ RECORD 3 ]-
objtype    | database
schemaname |
objname    | deepdive
privname   | CREATE
array_agg  | {alice,dbadm,endusers}
-[ RECORD 4 ]-
objtype    | database
schemaname |
objname    | deepdive
privname   | CREATE WITH GRANT OPTION
array_agg  | {alice,dbadm,endusers}
-[ RECORD 5 ]-
objtype    | database
schemaname |
objname    | deepdive
privname   | TEMP
array_agg  | {alice,alice,alice,alice,apps,appuser,bob,bob,
              dbadm,dbadmins,joe,endusers,endusers,mary,
              pg_execute_server_program,pg_monitor,pg_read_all_settings,
              pg_read_all_stats,pg_read_server_files,pg_signal_backend,
              pg_stat_scan_tables,pg_write_server_files,sue}
-[ RECORD 6 ]-
objtype    | database
schemaname |
objname    | deepdive
privname   | TEMPORARY
array_agg  | {alice,alice,alice,alice,apps,appuser,bob,bob,
              dbadm,dbadmins,joe,endusers,endusers,mary,
              pg_execute_server_program,pg_monitor,pg_read_all_settings,
              pg_read_all_stats,pg_read_server_files,pg_signal_backend,
              pg_stat_scan_tables,pg_write_server_files,sue}
-[ RECORD 7 ]-
objtype    | database
schemaname |
objname    | deepdive
privname   | TEMPORARY WITH GRANT OPTION
array_agg  | {alice,dbadm,endusers}
-[ RECORD 8 ]-
objtype    | database
schemaname |
objname    | deepdive
privname   | TEMP WITH GRANT OPTION
array_agg  | {alice,dbadm,endusers}

Lot of information there!

Note the use of \x in psql produces the "expanded" output we see here with each column on a separate row for readability.

There are a couple general things right off to make note of:

• First, several of the privileges are repeated twice -- once alone, and once WITH GRANT OPTION. The grant option means that this role can grant this privilege to other roles. Any role with the superuser attribute will have this ability, but it can also be explicitly granted.
• Second, privilege TEMPORARY and TEMP are really just two spellings for the same privilege.

If we eliminate both of these forms of duplication, we are really seeing three distinct cases to consider:

• The first of those is CONNECT. Notice that, by default, all of our roles have this privilege, even the ones that cannot login (the "groups"). It turns out that this is actually so because by default CONNECT has be granted to PUBLIC, and thus every role gains that privilege. Also notice that, by default, recent versions of PostgreSQL come with preexisting "group" roles which are intended to allow easy assignment of certain tasks to non-superuser roles. These pg_* roles will be ignored for the rest of this blog (in fact, removed manually from the output), but they probably deserve a separate deep dive in order to understand all of the privileges those roles entail.
• The second case is CREATE database. We have intentionally screened out the postgres superuser, but there are three others in this list - alice, endusers, and dbadm. The reason for that is that dbadm was created as a superuser, endusers was made a member of dbadm, and alice was made a member of endusers. A subtle nuance to recognize though, is that alice and endusers were both created as NOINHERIT. This means that if you were to test your system by logging in as alice, you would find that alice cannot directly create a new database. And similarly neither can endusers. However alice can do SET ROLE dbadm and then do CREATE DATABASE alices_sandbox.
• The third case is TEMPORARY objects, which may be tables, views, and sequences. There is not much to be concerned about here except maybe the potential for unprivileged users to cause bloating of your system catalogs through temporary object creation.

Function Objects

The next object to examine is the function object. Going forward I am going to do the same consolidation that we did for the database object -- namely eliminate duplication. That will make this look like some records are missing because, well, they are. I am also going to remove the preexisting pg_* "group" roles to help us focus a bit:

-[ RECORD 9 ]-
objtype    | function
schemaname | public
objname    | all_access()
privname   | EXECUTE
array_agg  | {alice,dbadm,endusers}
-[ RECORD 11 ]-
objtype    | function
schemaname | public
objname    | all_access(16)
privname   | EXECUTE
array_agg  | {alice,dbadm,endusers}
-[ RECORD 13 ]-
objtype    | function
schemaname | public
objname    | check_access(25 16)
privname   | EXECUTE
array_agg  | {alice,dbadm,endusers}
-[ RECORD 15 ]-
objtype    | function
schemaname | public
objname    | check_access(25 16 25)
privname   | EXECUTE
array_agg  | {alice,dbadm,endusers}
-[ RECORD 17 ]-
objtype    | function
schemaname | public
objname    | get_inv(25 25)
privname   | EXECUTE
array_agg  | {alice,alice,alice,alice,apps,appuser,bob,bob,
              dbadm,dbadmins,joe,endusers,endusers,mary,sue}
-[ RECORD 19 ]-
objtype    | function
schemaname | public
objname    | my_privs()
privname   | EXECUTE
array_agg  | {alice,alice,alice,alice,apps,appuser,bob,bob,
              dbadm,dbadmins,joe,endusers,endusers,mary,sue}
-[ RECORD 21 ]-
objtype    | function
schemaname | public
objname    | my_privs_sys()
privname   | EXECUTE
array_agg  | {alice,alice,alice,alice,apps,appuser,bob,bob,
              dbadm,dbadmins,joe,endusers,endusers,mary,sue}

A small side bar note here -- the permissions for all_access() and check_access() are granted to each of three users twice. Let's see why that is:

SELECT pronamespace::regnamespace || '.' ||  proname || '(' || proargtypes::text || ')'
        AS objname FROM pg_proc WHERE proname LIKE '%_access';

            objname
-------------------------------
public.check_access(25 16 25)
public.check_access(25 16)
public.all_access(16)
public.all_access()

What you see here is that each of those function names exists twice in the same schema ("public"), with differing sets of argument types. In PostgreSQL, functions are disambiguated based on their "function signature", which is the fully qualified function name (i.e. schema.funcname) plus the set of input argument types. More on that later in this blog, but each of those unique function objects has its own set of privileges granted, and therefore the function name shows up as granted twice since both objects have been granted.

However we also see that get_inv(), my_privs, and my_privs_sys are granted to alice four times each and bob and endusers twice. What exactly is going on there? To investigate we can run the following (get_inv() only since all have identical privilege grants):

-- toggle expanded output off
\x
SELECT objtype, schemaname, objname, privname, base_role, as_role, role_path
FROM all_access()
WHERE base_role in ('alice','bob','endusers')
  AND objname in ('get_inv(25 25)')
    AND privname = 'EXECUTE' ORDER BY 3,5,7;

  objtype  | schemaname |    objname     | privname | base_role | as_role  |     role_path
----------+------------+----------------+----------+-----------+----------+--------------------
  function | public     | get_inv(25 25) | EXECUTE  | alice     | alice    | alice
  function | public     | get_inv(25 25) | EXECUTE  | alice     | joe      | alice.joe
  function | public     | get_inv(25 25) | EXECUTE  | alice     | endusers | alice.endusers
  function | public     | get_inv(25 25) | EXECUTE  | alice     | dbadm    | alice.endusers.dbadm
  function | public     | get_inv(25 25) | EXECUTE  | bob       | bob      | bob
  function | public     | get_inv(25 25) | EXECUTE  | bob       | dbadmins | bob.dbadmins
  function | public     | get_inv(25 25) | EXECUTE  | endusers  | endusers | endusers
  function | public     | get_inv(25 25) | EXECUTE  | endusers  | dbadm    | endusers.dbadm
(8 rows)

-- toggle expanded output on
\x

Here we see the following:

• alice has direct access to EXECUTE function get_inv(25 25). This is because, by default, when we created get_inv() the EXECUTE privilege was granted to PUBLIC. Hence bob and endusers also have direct access.
• Similarly, dbadmins has EXECUTE by virtue of the grant to PUBLIC, and bob can escalate (via SET ROLE) to the dbadmins role, thereby having a second path with which to run get_inv(25 25).
• endusers can escalate to dbadm, which in turn can execute get_inv(25 25) for two reasons. First of all the grant to PUBLIC, but also critically because dbadm has the Superuser attribute and can therefore do anything.
• Finally, alice can escalate to endusers or joe to execute the function, or escalate again from endusers to dbadm and become Superuser.

What are the key takeaways here?

Well, to me, there are several. The fact that PostgreSQL defaults to granting EXECUTE on function objects to PUBLIC may be surprising, and also means that any particular user may have several paths with which to gain the ability to run any particular function. Also, watch what happens if we REVOKE EXECUTE from joe:

-- become joe
SET SESSION AUTHORIZATION joe;
SELECT CURRENT_USER, get_inv('something','somewhere');

  current_user | get_inv
--------------+---------
  joe          |
(1 row)

-- reset to postgres
RESET SESSION AUTHORIZATION;

-- revoke privilege from joe
REVOKE ALL ON FUNCTION get_inv(text, text) FROM joe;

-- become joe again
SET SESSION AUTHORIZATION joe;
SELECT CURRENT_USER, get_inv('something','somewhere');

  current_user | get_inv
--------------+---------
  joe          |
(1 row)

-- What happened here?!?

-- reset to postgres
RESET SESSION AUTHORIZATION;

Why can joe still execute the function? The reason is that the ever-present PUBLIC group still has EXECUTE privilege for get_inv(), and all users including joe are counted as members of PUBLIC. Since we never actually granted the EXECUTE privilege for this function to joe, the REVOKE above did nothing for us. We will see later in this blog how to best "fix" this issue, but basically it involves revoking EXECUTE from PUBLIC on this function. Of course we then need to grant EXECUTE to the individual roles that need the access. And we might also want to change the PostgreSQL default behavior so that every new function does not have EXECUTE granted to PUBLIC. Again, more on that later.

Let's look at another issue:

-- allow alice to read the view that get_inv() uses
GRANT SELECT ON widget_inv TO alice;

-- become alice
SET SESSION AUTHORIZATION alice;

-- run the same query
SELECT CURRENT_USER, get_inv('something','somewhere');

  current_user | get_inv
--------------+---------
  alice        |
(1 row)

Notice that this runs fine. Perhaps that is not surprising, but it ought to be. Recall that alice was originally created with the attribute for "No inheritance". Normally this means that in order to use the privileges of a "group" in which you belong, you must first explicitly escalate via SET ROLE to that group. But in this case, alice did not need to run SET ROLE PUBLIC. So although PUBLIC is similar to a catch-all group role, it does not act exactly the same as other group roles in that it is not affected by NOINHERIT.

Now revoke EXECUTE from PUBLIC and see what happens:

-- reset to postgres
RESET SESSION AUTHORIZATION;
REVOKE ALL ON FUNCTION get_inv(text, text) FROM PUBLIC;

-- become alice
SET SESSION AUTHORIZATION alice;

SELECT CURRENT_USER, get_inv('something','somewhere');
ERROR:  permission denied for function get_inv

SET ROLE endusers;

SELECT CURRENT_USER, get_inv('something','somewhere');
ERROR:  permission denied for function get_inv

SET ROLE dbadm;
SELECT SESSION_USER, CURRENT_USER, get_inv('something','somewhere');

  session_user | current_user | get_inv
--------------+--------------+---------
  alice        | dbadm        |
(1 row)

-- reset to postgres
RESET SESSION AUTHORIZATION;

-- restore state
GRANT EXECUTE ON FUNCTION get_inv(text, text) TO PUBLIC;
REVOKE SELECT ON widget_inv FROM alice;

Note that even after taking away privileges from PUBLIC it is possible for alice to escalate to dbadm becoming a superuser, and execute the function. If we had simply tested to see if alice could execute the function directly we would have failed to notice this path to privilege. We saw this same situation earlier, for the same reason, with respect to alice and the CREATE database object privilege.

Language Objects

The next database object of interest is language.

-[ RECORD 23 ]-
objtype    | language
schemaname |
objname    | c
privname   | USAGE
array_agg  | {alice,dbadm,endusers}
-[ RECORD 25 ]-
objtype    | language
schemaname |
objname    | internal
privname   | USAGE
array_agg  | {alice,dbadm,endusers}
-[ RECORD 27 ]-
objtype    | language
schemaname |
objname    | plpgsql
privname   | USAGE
array_agg  | {alice,alice,alice,alice,apps,appuser,bob,bob,
              dbadm,dbadmins,joe,endusers,endusers,mary,sue}
-[ RECORD 29 ]-
objtype    | language
schemaname |
objname    | sql
privname   | USAGE
array_agg  | {alice,alice,alice,alice,apps,appuser,bob,bob,
              dbadm,dbadmins,joe,endusers,endusers,mary,sue}

The USAGE privilege on a language allows functions to be created in that language. Here we see that the c and internal languages can only be used by superusers. That means dbadm directly, and endusers and alice via explicit SET ROLE commands. On the other hand, creation of sql and plpgsql functions has been granted to PUBLIC, meaning all roles have access to create those functions by default. The reason for the difference is that c and internal languages are untrusted whereas sql and plpgsql are trusted. An untrusted language potentially allows access which bypasses normal PostgreSQL Discretionary Access Control (DAC), and therefore only superusers (who already bypass all DAC) should be allowed to use them to create functions. A trusted language on the other hand does not provide any way to bypass DAC, and therefore can be "safely" granted to all roles. However, we'll see later that even trusted languages can be used in an unsafe way.

Schema Objects

After language, we come to the schema object type.

-[ RECORD 31 ]-
objtype    | schema
schemaname | public
objname    | public
privname   | CREATE
array_agg  | {alice,alice,alice,alice,apps,appuser,bob,bob,
              dbadm,dbadmins,joe,endusers,endusers,mary,sue}
-[ RECORD 33 ]-
objtype    | schema
schemaname | public
objname    | public
privname   | USAGE
array_agg  | {alice,alice,alice,alice,apps,appuser,bob,bob,
              dbadm,dbadmins,joe,endusers,endusers,mary,sue}

Fairly simple, but as we will see later, very important implications to security. By default, the public schema has CREATE and USAGE privileges granted to PUBLIC, and therefore all of our roles currently have these privileges.

Table Objects

We now come to table objects:

-[ RECORD 35, 37, 39, 41, 43, 45, 47,
49, 51, 53, 55, 57, 59, 61 ]-
objtype    | table
schemaname | public
objname    | t1, t2
privname   | SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER, TRUNCATE
array_agg  | {alice,dbadm,endusers}

Once again I have done the same deduplication, but in addition, I consolidated otherwise identical records into one entry to aid in comprehension. In a nutshell, this consolidated record shows that alice, dbadm, and endusers have all privileges for both t1 and t2.

Recall what we saw earlier in order to understand why. The role dbadm has the SUPERUSER attribute which bypasses DAC, endusers can SET ROLE dbadm, and alice can SET ROLE endusers and therefore also dbadm. Pretty straight forward, but once again, if we simply checked to see if alice could do a SELECT * FROM t1 we would be deceiving ourselves into thinking that t1 was secure.

SET SESSION AUTHORIZATION alice;
SELECT * from t1;
ERROR:  permission denied for table t1

SET ROLE dbadm;
SELECT * from t1;
t1_id | widgetname
-------+------------
(0 rows)

RESET SESSION AUTHORIZATION;

Also notice that we can escalate directly from alice to dbadm in one shot. We do not need to switch to endusers first.

View Objects

Finally, the last object type we will discuss, views:

-[ RECORD 63, 65, 67, 71, 73, 75, 77, 79, 81,
85, 87, 89, 91, 93 ,95, 99, 101, 103 ]-
objtype    | view
schemaname | public
objname    | my_privs, my_privs_sys, widget_inv
privname   | INSERT, UPDATE, DELETE, REFERENCES, TRIGGER, TRUNCATE
array_agg  | {alice,dbadm,endusers}
-[ RECORD 69, 83 ]-
objtype    | view
schemaname | public
objname    | my_privs, my_privs_sys
privname   | SELECT
array_agg  | {alice,alice,alice,alice,apps,appuser,bob,bob,
              dbadm,dbadmins,joe,endusers,endusers,mary,sue}
-[ RECORD 97 ]-
objtype    | view
schemaname | public
objname    | widget_inv
privname   | SELECT
array_agg  | {alice,alice,alice,apps,appuser,dbadm,joe,endusers,endusers,mary}

Based on everything we has seen so far, this output should make sense:

• Non SELECT privilege is restricted to superusers or those roles that can SET ROLE to superuser status.
• The SELECT privilege on my_privs and my_privs_sys has been granted to PUBLIC, and therefore all roles can exercise it, and those roles which are NOINHERIT can exercise it via multiple paths.
• The SELECT privilege on widget_inv was granted way up at the beginning of this article to apps, endusers, which (if you are keeping up) you know matches the above list modulo NOINHERIT paths, etc. -- proof left to the reader as an exercise!

About Views and Functions

One aspect of views in PostgreSQL which is not often discussed is the fact that a view always accesses the underlying objects in the view SQL as the owner of the view, not the role invoking the outer query. This is different than the default behavior of PostgreSQL functions.

Functions might be SECURITY INVOKER (the default), or SECURITY DEFINER. The former means that the function executes with the privileges of the invoker (CURRENT_USER) whereas the latter means that the function executes with the privileges of the owner of the function -- which is the creator by default, but ownership might be changed by the superuser.

Thus you can think of views in PostgreSQL as being SECURITY DEFINER while functions are usually (unless specifically created otherwise) SECURITY INVOKER. Things can get interesting, or more likely confusing, when a view includes one or more function calls.

To illustrate, recall the objects already created and privileges granted for this article.

CREATE TABLE t1 (t1_id int PRIMARY KEY, widgetname text);
CREATE TABLE t2 (t2_id int PRIMARY KEY, t1_id int REFERENCES t1, qty int, location text);
CREATE VIEW widget_inv AS SELECT widgetname, location, qty FROM t2 JOIN t1 USING (t1_id);
CREATE FUNCTION get_inv(wdgt text, loc text) RETURNS int AS
$$
  SELECT qty FROM widget_inv WHERE widgetname = wdgt AND location = loc
$$ LANGUAGE sql;
GRANT SELECT ON widget_inv TO apps, endusers;

Notice that no grants were performed on tables t1 and t2. Yet as we saw above, the function get_inv() can be executed by appuser (in fact all roles as it was granted to PUBLIC by default), and the view widget_inv can also be selected by appuser.

SET SESSION AUTHORIZATION appuser;

SELECT CURRENT_USER, SESSION_USER, * FROM t1;
ERROR:  permission denied for table t1

SELECT CURRENT_USER, SESSION_USER, get_inv('anything','anywhere');

  current_user | session_user | get_inv
--------------+--------------+---------
  appuser      | appuser      |
(1 row)

RESET SESSION AUTHORIZATION;

And sure enough, appuser is successful in calling get_inv(). No data was returned because we have not put any into the tables.

So for the non-believers out there, try this:

INSERT INTO t1 VALUES (1, 'widget1');
INSERT INTO t2 VALUES (1, 1, 42, 'loc1'), (2, 1, 24, 'loc2');

SET SESSION AUTHORIZATION appuser;

SELECT CURRENT_USER, SESSION_USER, * FROM t1;
ERROR:  permission denied for table t1

SELECT CURRENT_USER, SESSION_USER, get_inv('widget1','loc1');

  current_user | session_user | get_inv
--------------+--------------+---------
  appuser      | appuser      |      42
(1 row)

RESET SESSION AUTHORIZATION;

Voilà, we have access to data.

Now let's see what happens if we mix functions and views the other way around:

CREATE OR REPLACE FUNCTION ft1() RETURNS SETOF t1 AS $$
  SELECT * FROM t1
$$ LANGUAGE sql;

CREATE VIEW vt2 AS
  SELECT * FROM t2;

CREATE VIEW widget_inv2 AS
  SELECT widgetname, location, qty
  FROM vt2 JOIN ft1() USING (t1_id);

GRANT SELECT ON vt2, widget_inv2 TO apps;

SET SESSION AUTHORIZATION appuser;

SELECT CURRENT_USER, SESSION_USER, * FROM ft1();
ERROR:  permission denied for table t1
CONTEXT:  SQL function "ft1" statement 1

SELECT CURRENT_USER, SESSION_USER, * FROM vt2;

  current_user | session_user | t2_id | t1_id | qty | location
--------------+--------------+-------+-------+-----+----------
  appuser      | appuser      |     1 |     1 |  42 | loc1
  appuser      | appuser      |     2 |     1 |  24 | loc2
(2 rows)

SELECT CURRENT_USER, SESSION_USER, * FROM widget_inv;

  current_user | session_user | widgetname | location | qty
--------------+--------------+------------+----------+-----
  appuser      | appuser      | widget1    | loc1     |  42
  appuser      | appuser      | widget1    | loc2     |  24
(2 rows)

SELECT CURRENT_USER, SESSION_USER, * FROM widget_inv2;
ERROR:  permission denied for table t1
CONTEXT:  SQL function "ft1" statement 1

RESET SESSION AUTHORIZATION;

DROP VIEW vt2, widget_inv2;
DROP FUNCTION ft1();

As expected, accessing the underlying tables directly via a VIEW works, but when a SECURITY INVOKER (i.e. default) function is called in a similar VIEW, access is denied. We can "fix" this by making the function SECURITY DEFINER:

CREATE OR REPLACE FUNCTION ft1() RETURNS SETOF t1 AS $$
  SELECT * FROM t1
$$ SECURITY DEFINER LANGUAGE sql;

CREATE VIEW widget_inv2 AS
  SELECT widgetname, location, qty
  FROM t2 JOIN ft1() USING (t1_id);

GRANT SELECT ON widget_inv2 TO apps;

SET SESSION AUTHORIZATION appuser;

SELECT CURRENT_USER, SESSION_USER, * FROM ft1();

  current_user | session_user | t1_id | widgetname
--------------+--------------+-------+------------
  appuser      | appuser      |     1 | widget1
(1 row)

SELECT CURRENT_USER, SESSION_USER, * FROM widget_inv2;

  current_user | session_user | widgetname | location | qty
--------------+--------------+------------+----------+-----
  appuser      | appuser      | widget1    | loc1     |  42
  appuser      | appuser      | widget1    | loc2     |  24
(2 rows)

RESET SESSION AUTHORIZATION;

DROP VIEW widget_inv2;
DROP FUNCTION ft1();

We will take a breather here for now. The second part of this two part blog will cover the interesting case of CVE-2018-1058 and its implications. I hope you have enjoyed the first part of the blog and will come back for part 2!