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;
GRANT
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=>
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
Best Answer
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:
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:
The the regular user can do:
Of course you could create the function in a different schema, e.g.
admin