Christophe Pettus: Checking Your Privileges

The PostgreSQL roles and privileges system can be full of surprises. Let’s say we have a database test, owned by user owner. In it, we create a very secret function f that we do not want just anyone to be able to execute: test=> select current_user; current_user -------------- owner (1 row) test=> CREATE FUNCTION f() RETURNS int as $$ SELECT 1; $$ LANGUAGE sql; CREATE FUNCTION test=> select f(); f --- 1 (1 row) There are two other users: hipriv and lowpriv. We want hipriv to be able to run the function, but not lowpriv. So, we grant EXECUTE to hipriv, but revoke it from lowpriv: test=> GRANT EXECUTE ON FUNCTION f() TO hipriv; GRANT test=> REVOKE EXECUTE ON FUNCTION f() FROM lowpriv; REVOKE Let’s test it! We log in as hipriv and run the function: test=> SELECT current_user; current_user -------------- hipriv (1 row) test=> SELECT f(); f --- 1 (1 row) Works great. Now, let’s try it as lowpriv: test=> SELECT current_user; current_user -------------- lowpriv (1 row) test=> SELECT f(); f --- 1 (1 row) Wait, what? Why did it let lowpriv run f()? We explicitly revoked that permission! Is the PostgreSQL privileges system totally broken? Well, no. But there are some surprises. Let’s look at the privileges on f(): test=> SELECT proacl FROM pg_proc where proname = 'f'; proacl ----------------------------------------- {=X/owner,owner=X/owner,hipriv=X/owner} (1 row) The interpretation of each of the entries is “=/“. We see that owner has X (that is, EXECUTE) on f() granted by itself, and hipriv has EXECUTE granted by owner. But what’s with that first one that doesn’t have a role at the start? And where is our REVOKE on lowpriv? The first thing that may be surprising is that there is no such thing as a REVOKE entry in the privileges. REVOKE removes a privilege that already exists; it doesn’t create a new entry that says “don’t allow this.” This means that unless there is already an entry that matches the REVOKE, REVOKE is a no-[...]