Must be superuser after GRANT SELECT ON pg_settings(PostgreSQL)

Question

PostgreSQL - 9.6

I connect as postgres to userdb, add grant select pg_settings for user.

➤ psql://[email protected]:5432/postgres 

# \c userdb
You are now connected to database "userdb" as user "postgres".

➤ psql://[email protected]:5432/userdb 

# GRANT SELECT ON pg_settings TO user;
GRANT
Time: 7.728 ms

➤ psql://[email protected]:5432/userdb 

# \q

After i connect as user to userdb and try get hba_file

psql -h postgresql-96.local -p 5432 -U user -d userdb
Password for user user: 
psql (9.6.6)
Type "help" for help.

userdb=> SHOW hba_file;
ERROR:  must be superuser to examine "hba_file"
userdb=> 

userdb=> select setting from pg_settings where name='hba_file';
 setting 
---------
(0 rows)

But i cannot show, get, view hba_file. How get hba_file as user? Thank you

Answer 1

I am not entirely sure, but I think you can't get around the "superuser" check for those settings.

With Postgres 10 you could use the new default role pg_read_all_settings but if upgrading is not an option now, the easiest way would be to create a function that runs with superuser privileges.

Create the following function as the superuser:

create function public.list_settings() 
  returns setof pg_catalog.pg_settings
as
$$ 
  select *
  from pg_catalog.pg_settings;
$$
language sql
security definer;

The security definer means that the function always runs with the privileges of the user who created the function (the superuser).

Then grant execute on that function to your user:

grant execute on function public.list_settings() to the_user;

The the regular user can do:

select * 
from public.list_settings()
where name = 'hba_file';

Of course you could create the function in a different schema, e.g. admin