Revoke User Access to all System Information Functions on Postgresql

Question

Is there a way to remove access to all system information functions for a given user (which is already limited to read only access) in Postgres?

For example, a user could normally run pg_conf_load_time() or SELECT current_setting('datestyle');.

Normally you could do do

REVOKE ALL ON FUNCTION current_setting(text) FROM my_user;

but I get no privileges could be revoked for "current_setting".

Is there a way to disallow access to all functions on these pages?

• https://www.postgresql.org/docs/current/functions-admin.html
• https://www.postgresql.org/docs/current/functions-info.html

Answer 1

You would have to

REVOKE EXECUTE ON FUNCTION ... FROM PUBLIC;

because that's the default privilege for functions.

Revoking a privilege that was not granted is a no-op in SQL.

After revoking the privilege from PUBLIC, you'd have to explicitly grant it to all users that should have it.

But I think you shouldn't do that.

Why not?

1. These modifications will not survive a major upgrade.

2. PostgreSQL does not consider this information security critical, and I think that is correct.

   Everybody should be able to see the settings relevant for the session.

   Note that certain parameters that reveal details about the operating system where PostgreSQL is running are only visible to superusers or members of the pg_read_all_settings role anyway.

3. You may break applications that rely on these functions.