PG Phriday: Tackling Intractable ACLs

Friday February 05, 2016

Say that three times fast! Joking aside, managing database object access is a headache for users and DBAs alike. Postgres isn’t really an exception in that regard, but it does provide a few tools to greatly reduce the pain involved. The crazy thing is that few people even know this feature exists. I’ve known about it for a while myself, but it always slips my mind because it feels so wrong not to explicitly grant permissions.

What does that mean? Well, consider we have a schema with some existing tables, and a role we want to have read permission on tables in that schema. Here’s one as an example:

CREATE SCHEMA acl_app;

CREATE TABLE acl_app.t1 (foo INT);
CREATE TABLE acl_app.t2 (bar INT);

CREATE ROLE acl_read;
GRANT USAGE ON SCHEMA acl_app TO acl_read;

If this were a preexisting schema, normally we would grant read permission to tables like this:

GRANT SELECT ON acl_app.t1 TO acl_read;
GRANT SELECT ON acl_app.t2 TO acl_read;

And that’s also the usual suggestion for grants after tables are created. Create the table, then grant the permissions. It’s fairly straight-forward, and an expected part of database administration. But what about when we have an existing table with dozens or hundreds of tables? Doing the grants manually as shown above would be monumentally irritating! In fact, for many database systems, the only way forward is to use system catalogs to generate a script, and then execute the output in the database. Postgres lets you do that:

COPY (
  SELECT 'GRANT SELECT ON acl_app.' || tablename ||
         ' TO acl_read;'
    FROM pg_tables
   WHERE schemaname = 'acl_app'
) TO '/tmp/grant_perms.sql';

\i /tmp/grant_perms.sql

But the kind Postgres devs have also provided us with some extremely useful shorthand, because while usable, the script approach is something of an ugly hack. Here’s how that looks:

GRANT SELECT
   ON ALL TABLES IN SCHEMA acl_app
   TO acl_read;

\z acl_app.*

 Schema  | Name | Type  |     Access privileges     
---------+------+-------+---------------------------
 acl_app | t1   | table | postgres=arwdDxt/postgres
         |      |       | acl_read=r/postgres       
 acl_app | t2   | table | postgres=arwdDxt/postgres
         |      |       | acl_read=r/postgres

As we can see here, the permissions show that the acl_read role can read the tables in this schema. Unfortunately this is a one-time operation. All subsequent tables created in the future will not be readable by the acl_read role. Here’s what happens if we create a new table now:

CREATE TABLE acl_app.t3 (baz INT);

\z acl_app.*

 Schema  | Name | Type  |     Access privileges     
---------+------+-------+---------------------------
 acl_app | t1   | table | postgres=arwdDxt/postgres
         |      |       | acl_read=r/postgres       
 acl_app | t2   | table | postgres=arwdDxt/postgres
         |      |       | acl_read=r/postgres       
 acl_app | t3   | table |

Now we have a new table that’s only accessible by the postgres user, since that’s who created it. In most database software, that’s where the story ends. We can either re-run the above command after adding new tables, or ask developers to add an explicit GRANT statement following every new table, for every user or role that needs access.

That’s not so hard when using a template. But templates aren’t always maintained, new hires might not know where they are or use them properly, and sometimes people just plain forget. If testing or staging environments don’t exactly match production, or developers use these environments with elevated privileges, missing grants might not show up until the new tables and the associated code hit production. What then?

Well, Postgres provides some non-standard syntax that solves this conundrum. We can actually modify the default privileges for several database objects, so they’re explicitly set upon creation. Let’s try this out with another new table:

ALTER DEFAULT PRIVILEGES
   IN SCHEMA acl_app
GRANT SELECT ON TABLES
   TO acl_read;

CREATE TABLE acl_app.t4 (buz SERIAL);

\z acl_app.*

 Schema  |    Name    |   Type   |     Access privileges     
---------+------------+----------+---------------------------
 acl_app | t1         | table    | postgres=arwdDxt/postgres
         |            |          | acl_read=r/postgres       
 acl_app | t2         | table    | postgres=arwdDxt/postgres
         |            |          | acl_read=r/postgres       
 acl_app | t3         | table    |
 acl_app | t4         | table    | postgres=arwdDxt/postgres
         |            |          | acl_read=r/postgres       
 acl_app | t4_buz_seq | sequence |

As we can see, the new t4 table has the expected grant. But this is actually a trick example. When tables are declared in Postgres with a SERIAL or BIG SERIAL column type, a new sequence is created, and grants to the parent table do not cascade to this new sequence. Thus, if a role needed to insert into this table and we granted INSERT privileges, it would not have that capability so long as it used the sequence.

Of course, the way to fix that comes through the same mechanism. For roles that require insert capabilities, two default modifications cover that scenario. Here’s another role meant for inserting into our tables, and how those permissions show up:

CREATE ROLE acl_writer;
GRANT USAGE ON SCHEMA acl_app TO acl_writer;

ALTER DEFAULT PRIVILEGES
   IN SCHEMA acl_app
GRANT SELECT, INSERT, UPDATE ON TABLES
   TO acl_writer;

ALTER DEFAULT PRIVILEGES
   IN SCHEMA acl_app
GRANT USAGE ON SEQUENCES
   TO acl_writer;

CREATE TABLE acl_app.t5 (bla SERIAL);

\z acl_app.t5*

 Schema  |    Name    |   Type   |     Access privileges
---------+------------+----------+----------------------------
 acl_app | t5         | table    | postgres=arwdDxt/postgres
         |            |          | acl_read=r/postgres
         |            |          | acl_writer=arw/postgres
 acl_app | t5_buz_seq | sequence | postgres=rwU/postgres
         |            |          | acl_writer=U/postgres

Now, when new tables are created that automatically generate an associated sequence, the acl_writer role will be able to utilize both. We can also see that the acl_read role also worked as expected. The best part is that we don’t need to remember anything when creating new objects, so long as we set everything up beforehand. And if we forget and don’t add this stuff until later? Well, we can always use the ALL TABLES IN grant syntax to fix any existing objects.

There is one caveat we must point out, however. New functions in Postgres are always granted usage to PUBLIC following creation. This is mostly because Postgres doesn’t differentiate between functions and procedures, so there is no assumption that a function will act on database objects. Normally this isn’t a problem, but if a function is created with SECURITY DEFINER, it executes as the user who created it. If the function does modify table data, that means any user with access to the database can invoke it, and that’s a massive security hole.

We recommend fixing this in all production databases with these commands:

ALTER DEFAULT PRIVILEGES
REVOKE EXECUTE ON FUNCTIONS
  FROM PUBLIC;

REVOKE EXECUTE
    ON ALL FUNCTIONS IN SCHEMA PUBLIC
  FROM PUBLIC;

The last command should be repeated for any other schema in the database. From that point on, all function grants must either be explicit, or use the ALTER DEFAULT... technique we’ve covered in this article. It probably seems draconian, and in most cases that might be the case. However, in critical or sensitive database systems, sometimes being a little zealous can be beneficial. Preventing accidental privilege escalations also stops malicious ones.

And data can rarely be too safe.

See Also