Re: Proposal: SET ROLE hook

Joe Conway <mail(at)joeconway(dot)com> writes:

> In many environments there is a policy requiring users to login using
> unprivileged accounts, and then escalate their privileges if and when
> they require it. In PostgreSQL this could be done by granting the
> superuser role to an unprivileged user with noinherit, and then making
> the superuser role nologin. Something like:
>
> 8<-------------
> psql -U postgres
> create user joe noinherit;
> grant postgres to joe;
> alter user postgres nologin;

A bit off-topic, but I've often had to create an intermediate role with
no-inherit between the gifted role to permit accessing it without the
grantee losing ability to inherit some other(s).

To wit;

We have an owner role for a given DB who is supposed to own most/all
objects. We might want 1 or more other roles/users to be able to run as
owner guy to create objects... We want to enforce that objects so
created are automatically owned by the correct owner role.

create role owner;
create role owner_role no inherit in role owner;
create role read_write;
create role jerry in role read_write, owner_role;

I can by default run as the read_write guy and mangle data to my little
heart's content (the super-dev that I am) but if a new table is needs
creation I must do set role owner. This insures that the table is owned
by owner and not jerry afterward.

Often wondered why not;

grant owner to jerry no inherit;

FWIW

> psql -U joe
> -- do stuff *not requiring* escalated privs
> set role postgres;
> -- do stuff *requiring* escalated privs
> reset role;
> 8<-------------
>
> One of the problems with this is we would ideally like to know whenever
> joe escalates himself to postgres. Right now that is not really possible
> without doing untenable things such as logging every statement.
>
> In order to address this issue, I am proposing a new SET ROLE hook. The
> attached patch (role-esc-hook.diff) is I believe all we need. Then
> extension authors could implement logging of privilege escalation.
>
> A proof of concept extension patch is also attached. That one is not
> meant to be applied, just illustrates one potential use of the hook. I
> just smashed it on top of passwordcheck for the sake of convenience.
>
> With both patches applied, the following scenario:
> 8<------------------------
> psql -U joe postgres
> psql (9.6devel)
> Type "help" for help.
>
> postgres=> set role postgres;
> SET
> postgres=# select rolname, rolpassword from pg_authid;
> rolname | rolpassword
> ----------+-------------
> joe |
> postgres |
> (2 rows)
>
> postgres=# set log_statement = none;
> SET
> postgres=# reset role;
> RESET
> 8<------------------------
>
> Generates the following in the log:
>
> 8<------------------------
> LOG: Role joe transitioning to Superuser Role postgres
> STATEMENT: set role postgres;
> LOG: statement: select rolname, rolpassword from pg_authid;
> LOG: statement: set log_statement = none;
> LOG: Superuser Role postgres transitioning to Role joe
> STATEMENT: reset role;
> 8<------------------------
>
> Note that we cannot prevent joe from executing
> set log_statement = none;
> but we at least get the evidence in the log and can ask joe why he felt
> the need to do that. We could also set up alerts based on the logged
> events, etc.
>
> This particular hook will not capture role changes due to SECURITY
> DEFINER functions, but I think that is not only ok but preferred.
> Escalation during a SECURITY DEFINER function is a preplanned sanctioned
> event, unlike an ad hoc unconstrained role change to superuser. And
> given the demo patch, we could see any SECURITY DEFINER function created
> by the superuser when it gets created in the log (which again is subject
> to auditing, alerts, etc.)
>
> Ultimately I would also like to see a general hook available which would
> fire for all changes to GUC settings, but I think this one is still very
> useful as it is highly targeted.
>
> Comments?
>
> Thanks,
>
> Joe

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net
p: 312.241.7800