[vbezhenar]

How do you configure execute rights securely? I’ve skimmed over the docs. Execute right allows user to execute programs under postgres Unix account, so getting control of postgres database account should be possible. Basically execute right = superuser. Why even have a separate right for that? Just assign superuser role.

[anarazel]

Because enough users asked for them - there was a long debate about that. It's less to prevent those users from intentionally gaining superuser, and more to make it a bit harder to unintentionally have trusted users shoot themselves into the foot. The docs say at https://www.postgresql.org/docs/current/default-roles.html#D...

"The pg_read_server_files, pg_write_server_files and pg_execute_server_program roles are intended to allow administrators to have trusted, but non-superuser, roles which are able to access files and run programs on the database server as the user the database runs as. As these roles are able to access any file on the server file system, they bypass all database-level permission checks when accessing files directly and they could be used to gain superuser-level access, therefore care should be taken when granting these roles to users."

It's not uncommon to grant users the right to change their roles into specific ones with more privileges. For business critical installations the DBA's role will e.g. often not have superuser rights itself, but the permission to do 'SET ROLE some_superuser_role; some_dangerous_command; RESET ROLE;'. And then it can be useful to allow a user to go to different rules. E.g. one that look at files for low-level debugging, but not drop tables, to prevent accidents.

Edit: #1 fight formatting, #2 explain SET ROLE practice.

[vbezhenar]

Thanks, that makes sense. I still think that enough people don't read documentation carefully and might unintentionally introduce vulnerability into their database systems, but probably they would find enough ways to shoot themselves in the foot anyway.