10:00

Checking Your Privileges

25 March 2024

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

The second thing is that if there is no role specified that, that means the special role PUBLIC. PUBLIC means “all roles.” So, anyone can execute f()! This is the default privilege for new functions.

Combined, this means that when the function was created, EXECUTE was granted to PUBLIC. The REVOKE was a no-op, because there was no explicit grant of privileges to lowpriv.

How do we fix it? First, we can revoke that undesirable first grant to PUBLIC:

test=> REVOKE EXECUTE ON FUNCTION f() FROM PUBLIC;
REVOKE

hipriv can still run the function, because we gave it an explicit grant:

test=> SELECT current_user;
 current_user 
--------------
 hipriv
(1 row)

test=> SELECT f();
 f 
---
 1
(1 row)

But lowpriv can’t skate in under the grant to PUBLIC, so it can’t run the function anymore:

test=> SELECT current_user;
 current_user 
--------------
 lowpriv
(1 row)

test=> SELECT f();
ERROR:  permission denied for function f

The next thing we should do is alter the default privileges for new functions so that PUBLIC does not have EXECUTE privilege on them. You can do this with:

test=> ALTER DEFAULT PRIVILEGES FOR USER owner REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
ALTER DEFAULT PRIVILEGES

This means any new functions created by the role owner will not have EXECUTE granted to PUBLIC. It’s important to remember that this does not change the privileges of any existing functions, and it only changes it for functions created by owner, not any other user or role.

So, if you are counting on the PostgreSQL privilege system to prevent roles from running functions (and accessing other objects), be sure you know what the default permissions are, and adjust them accordingly.

There are no comments yet.