Query on pg_settings view
Hi,
I have a weird case when running a query on the pg_settings view.
I have two users, first one is the default user - postgres which is a
superuser and another one is a role which i had created, i called it
test_role and it's not a superuser.
When I run a select on pg_settings with these two users I recieve different
results.
Running the query 'select * from pg_settings' with 'postgres' user returns
269 rows when running the same query with 'test_role' returns 254 rows.
For example, the following query 'select lower(setting) from pg_settings
where lower(name) = 'config_file'' returns null when I connect with
'test_role' but with 'postgres' user I recieve one row
(/var/lib/pgsql/9.6/data/postgresql.conf).
I have granted all privileges on pg_settings to 'test_role' but it didn't
changed the results.
I don't have any idea what it's wrong here.
The PostgreSQL version is PostgreSQL 9.6.1 on x86_64-pc-linux-gnu
Can you please help me with this issue?
Thanks in advance,
Ronen
Ronen Nofar wrote:
I have a weird case when running a query on the pg_settings view.
I have two users, first one is the default user - postgres which is a superuser
and another one is a role which i had created, i called it test_role and it's not a superuser.
When I run a select on pg_settings with these two users I recieve different results.
That is intentional, because some settings should only be
visible for superusers, for example everything that has to
do with the operating system (location of configuration file
or socket directories).
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Greetings,
* Laurenz Albe (laurenz.albe@cybertec.at) wrote:
Ronen Nofar wrote:
I have a weird case when running a query on the pg_settings view.
I have two users, first one is the default user - postgres which is a superuser
and another one is a role which i had created, i called it test_role and it's not a superuser.
When I run a select on pg_settings with these two users I recieve different results.That is intentional, because some settings should only be
visible for superusers, for example everything that has to
do with the operating system (location of configuration file
or socket directories).
This isn't quite correct any longer- with PG10, we have a default role
called 'pg_read_all_settings' which can be GRANT'd to other roles to
allow viewing of all settings, even those previously restricted to
superuser-only.
Thanks!
Stephen