Re: PCI:SSF - Safe SQL Query & operators filter

On Mon, Nov 7, 2022 at 6:25 PM Jan Bilek <jan(dot)bilek(at)eftlab(dot)com(dot)au> wrote:

> The main problem comes from obvious - our application's PostgreSQL user
> needs to have an Superuser role as it manages most of its (dedicated)
> database (creates tables, drops those, manages views, triggers ... ).
>
No, the things you want to manage within the database do not require
Superuser. You only need superuser once to configure the system in such a
way, through role and grants and possibly default permissions, that from
then on most everything an application user would want to do can be done by
the role(s) you have created.

> So these - pg_read_server_files, pg_write_server_files and
> pg_execute_server_program roles are inherited from the Superuser
>
Those roles are entirely independent of Superuser; and in fact exists for
that very purpose, since Superuser can already do those things.

and are also super sensitive for us, but our application user apparently
> still needs to be superuser for multiple reasons.
>
You will need to be specific as to exactly what capability you need that
you cannot get a non-superuser role to accomplish.

1/ What we need is to create a postgresql user who would be
> pg_database_owner but not superuser
>
OWNER is an attribute of specific objects, including the database object.
You can already do this.

> ... but apparently we still need our user to be superuser to be able to
> carry on with some operations (picking one of more then few) ....
>
> CREATE OR REPLACE LANGUAGE plpython3u;
> HINT: Must be superuser to create this extension.
>

Yeah, this will be a bit tougher since you are in conflict regarding
whether you need O/S resources or not. It is hard to solve this problem
without the bigger picture.

> 2/ Second option is to introduce some sort of limitation of allowed SQL
> queries
>
A useful tool is "SECURITY DEFINER" tagged routines.

The lack of a separate database migration process seems to be your main
issue - something that can run audited code in an elevated context to put
the system into a state where non-elevated users can then do the things
they need to do. Even if you just have modes in your application that are
"admin mode" and "user mode" so admin mode can do the, hopefully limited,
subset of actions that need superuser while user mode operates under the
only semi-powerful database owner role you can probably bridge the gap (if
you indeed must have the database do things directly on the underlying
operating system).

David J.