zbxdb + postgres 9 without superuser access for sql user

[alexandr554]

on this thread https://github.com/ikzelf/zbxdb/issues/7 was discussed superuser privilege problem in the postgres database

based on this https://dba.stackexchange.com/questions/192917/must-be-superuser-after-grant-select-on-pg-settingspostgresql

maybe, we can create fuction in database which runs with the privileges of the superuser and which list all needed stuff. and this will be more secure, than give superuser access to sql user.

sorry, i misqualified and can't write a code and make patch.

[ikzelf]

What queries do you want to run? My problem with this is that I can not create objects into a database that I do not administer.

With postgres (in the older versions more than in the newer versions) superuser is still needed for some queries. Even with newer versions superuser is still needed when trying to read the other databases using the dblink extension. To make things a bit more secure, during the connection the session is altered to a read only session.

This is not ideal but for most it seems to be workable.

[alexandr554]

with no superuser access i have this problem:

$ zbxdb.py -c ./etc/zbxdb.myserver.cfg
2021-04-22 22:07:15,917___main__ log level 30
2021-04-22 22:07:15,917___main__ start python-3.6.5 zbxdb-2.09 pid=3747293 Connecting ...

2021-04-22 22:07:15,918___main__ Fatal messages
2021-04-22 22:07:15,918___main__ Critical messages
2021-04-22 22:07:15,918___main__ Error messages
2021-04-22 22:07:15,918___main__ Warning message
2021-04-22 22:07:15,918___main__ zbxdb found db_type=postgres, driver psycopg2; checking for driver

2021-04-22 22:07:15,948___main__ problem
Traceback (most recent call last):
  File "/home/zbxdb/zbxdb/bin/zbxdb.py", line 813, in main
    connect_info = db_connections.connection_info(_conn)
  File "/home/zbxdb/zbxdb/bin/dbconnections/postgres.py", line 36, in connection_info
    conn_info['iname'] = _data[0]
TypeError: 'NoneType' object is not subscriptable

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/zbxdb/zbxdb/bin/zbxdb.py", line 919, in <module>
    main()
  File "/home/zbxdb/zbxdb/bin/zbxdb.py", line 877, in main
    err_code, err_msg = driver_errors.db_errorcode(db_driver, dberr)
  File "/home/zbxdb/zbxdb/bin/drivererrors/psycopg2.py", line 9, in db_errorcode
    LOGGER.debug("db_errorcode pgcode %s\n", excep.pgcode)
AttributeError: 'TypeError' object has no attribute 'pgcode'

because sql select in file "/home/zbxdb/zbxdb/bin/dbconnections/postgres.py" returns some wrong answer

    _c.execute("""select inet_server_addr()||':'||p.setting||':'|| d.setting
          from  pg_settings p, pg_settings d
          where p.name = 'port'
          and   d.name = 'data_directory'""")
    _data = _c.fetchone()
    conn_info['iname'] = _data[0]

but, if i create function in database and replace "from pg_settings p, pg_settings d" with "from public.list_settings() p, public.list_settings() d" it starts to work.

i don't understand, is other acces limitations or not

[ikzelf]

Can you share that list_settings code?

[alexandr554]

ofcource!

create function public.list_settings() 
  returns setof pg_catalog.pg_settings
as
$$ 
  select *
  from pg_catalog.pg_settings;
$$
language sql
security definer;

As described on link above

The security definer means that the function always runs with the privileges of the user who created the function (the superuser).

After that we must give permissions for regular user: grant execute on function public.list_settings() to the_user;

[ikzelf]

Hi, I just verified. You should issue a 'grant select on pg_catalog.pg_settings to your_sql_user;' This will allow your user to query the table. And yes, you need an administrator account to grant that but it's a better solution than installing other things in your database.

[alexandr554]

Oh thanks! It turned out to be easier than i thought!