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
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.