PG Phriday: Tackling Intractable ACLs

February 5th, 2016 | Published in Database, Tech Talk | 2 Comments


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.


Tags: , ,

2 Comments

Feed
  1. Keith says:

    February 5th, 2016 at 11:37 am [#]


    There’s another caveat to the ALTER DEFAULT PRIVILEGES system. You said

    “We can actually modify the default privileges for several database objects, so they’re explicitly set upon creation”

    Which is not quite true and seems to be the assumption of how this system works and really confuses people. In your commands above, you never set which user you were altering the default privileges of. So from then on it’s only objects YOU create that have those default privileges set. If someone else goes and creates something in your acl_app schema, it will not have those default privileges. Unless you go and change the default privileges of that person’s role as well.

    We run into many clients being very confused by this and wondering why everything they create doesn’t get the defaults they’ve set in multi user environments. Our proposed solution is to typically designate a specific role that is in charge of object creation and is typically also the owner of all those objects as well. Users and application roles are then granted what they need and not allowed to edit production schema themselves (they can still make stuff in their own schemas for themselves).


    1. Shaun says:

      February 5th, 2016 at 11:41 am [#]


      Very good point!

      Thanks for bringing that up. It would be really handy if there was a way to apply the commands to all users, but them’s the breaks.


Sorry, this post is closed to comments.