适用范围

磐维数据库3.0

问题概述

租户使用监控用户查询pg_settings视图的某个参数时发现没有数据。

原因分析

pg_settings视图的内容查询确实需要权限控制，普通用户查询GUC参数应该使用show语法或者current_setting函数：

=> select current_setting('log_directory');
ERROR:  must be superuser to examine "log_directory"
CONTEXT:  referenced column: current_setting

如果权限不足，会有提示信息显示。

普通用户不可查看的参数列表

创建普通用户admin，然后使用初始用户omm创建完整的参数名称表

create table admin.t_sname as select name from pg_settings ;
alter table admin.t_sname owner to admin;

接着使用普通用户admin执行下面的匿名块

DO LANGUAGE plpgsql $do$
declare
  v_rec record;
  v_sql varchar;
begin
    for v_rec in select name from admin.t_sname  loop
      begin
        v_sql = 'show '||v_rec.name||';';
        execute v_sql;
      exception 
      when SQLSTATE '42501' then 
        raise notice '%',v_rec.name;
      when others then
        NULL;
      end;  
    end loop;
end;
$do$;

输出信息如下：

NOTICE:  alarm_component
NOTICE:  asp_flush_mode
NOTICE:  asp_log_directory
NOTICE:  asp_log_filename
NOTICE:  asp_sample_num
NOTICE:  audit_backup_directory
NOTICE:  audit_data_format
NOTICE:  audit_directory
NOTICE:  audit_dump_directory
NOTICE:  available_zone
NOTICE:  comm_proxy_attr
NOTICE:  config_file
NOTICE:  data_directory
NOTICE:  dynamic_library_path
NOTICE:  external_pid_file
NOTICE:  failed_login_attempts
NOTICE:  hba_file
NOTICE:  ident_file
NOTICE:  krb_server_keyfile
NOTICE:  license_path
NOTICE:  log_directory
NOTICE:  log_filename
NOTICE:  no_audit_client
NOTICE:  num_internal_lock_partitions
NOTICE:  password_max_length
NOTICE:  password_min_digital
NOTICE:  password_min_length
NOTICE:  password_min_lowercase
NOTICE:  password_min_special
NOTICE:  password_min_uppercase
NOTICE:  password_reuse_max
NOTICE:  perf_directory
NOTICE:  pljava_vmoptions
NOTICE:  pw_wal_directory
NOTICE:  query_log_directory
NOTICE:  query_log_file
NOTICE:  redo_bind_cpu_attr
NOTICE:  repl_uuid
NOTICE:  secure_file_priv
NOTICE:  shared_preload_libraries
NOTICE:  ss_enable_aio
NOTICE:  ss_enable_catalog_centralized
NOTICE:  ss_enable_dms
NOTICE:  ss_enable_dorado
NOTICE:  ss_enable_dss
NOTICE:  ss_enable_ondemand_realtime_build
NOTICE:  ss_enable_ondemand_recovery
NOTICE:  ss_enable_scrlock
NOTICE:  ss_enable_scrlock_sleep_mode
NOTICE:  ss_enable_ssl
NOTICE:  ss_instance_id
NOTICE:  ss_interconnect_channel_count
NOTICE:  ss_interconnect_type
NOTICE:  ss_interconnect_url
NOTICE:  ss_log_backup_file_count
NOTICE:  ss_log_level
NOTICE:  ss_log_max_file_size
NOTICE:  ss_msg_wait_time
NOTICE:  ss_ock_log_path
NOTICE:  ss_ondemand_recovery_mem_size
NOTICE:  ss_parallel_thread_count
NOTICE:  ss_rdma_work_config
NOTICE:  ss_recv_msg_pool_size
NOTICE:  ss_scrlock_server_bind_core
NOTICE:  ss_scrlock_server_port
NOTICE:  ss_scrlock_worker_bind_core
NOTICE:  ss_scrlock_worker_count
NOTICE:  ss_txnstatus_cache_size
NOTICE:  ss_work_thread_count
NOTICE:  ssl_ciphers
NOTICE:  stats_temp_directory
NOTICE:  thread_pool_attr
NOTICE:  thread_pool_stream_attr
NOTICE:  track_stmt_retention_time
NOTICE:  track_stmt_standby_chain_size
NOTICE:  unix_socket_directory
NOTICE:  xlog_file_path
NOTICE:  xlog_file_size
NOTICE:  xlog_lock_file_path
ANONYMOUS BLOCK EXECUTE

如上79个的GUC参数，普通用户不能查看，必须有sysadmin权限才可以。