PostgreSQL - 9.6

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

➤ psql://postgres@postgresql-96.local:5432/postgres 

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

➤ psql://postgres@postgresql-96.local:5432/userdb 

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

➤ psql://postgres@postgresql-96.local: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=> select setting from pg_settings where name='hba_file';
(0 rows)

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


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
  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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.