Unable to get pg_settings from non-postgres user

Question

I have the following query to get the settings for max_running_jobs.

select *    
from pg_settings 
where name = 'cron.max_running_jobs';

I'm able to get the records from postgres user but not with the non-postgres user.

Any grant/permission I got missed?

Whats is the Postgres version and distribution ? – Adelino Silva Jul 19 '22 at 14:04 — Adelino Silva, Jul 19 '22 at 14:04

score 0 · Answer 1 · answered Jul 19 '22 at 14:03

0

by this REF: https://pgpedia.info/p/pg_settings.html

the permission should be:

GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;

you can confirm it running:

SELECT grantee, string_agg(privilege_type, ', ') AS privileges
FROM information_schema.role_table_grants 
WHERE table_name='pg_settings'   
GROUP BY grantee;

answered Jul 19 '22 at 14:03

Adelino Silva

577
3
16

2

Better link to the official documentation – Frank Heikens Jul 19 '22 at 23:25
@AdelinoSilva, Still unable to access, after running `GRANT SELECT, UPDATE ON pg_settings TO myuser;` – MAK Jul 20 '22 at 11:35
@MAK check if this user can run: SELECT * FROM pg_show_all_settings() AS A; – Adelino Silva Jul 21 '22 at 15:36

Unable to get pg_settings from non-postgres user

1 Answers1

Linked