Обсуждение: [GENERAL] 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