On Tue, Mar 3, 2015 at 6:05 PM, Jim Nasby <jim.na...@bluetreble.com> wrote:
> What about a separate column that's just the text from pg_hba? Or is that > what you're opposed to? I'm not sure what you mean by that. There's a rawline field we could put somewhere but it contains the entire line. > FWIW, I'd say that having the individual array elements be correct is more > important than what the result of array_out is. That way you could always do > array_to_string(..., ', ') and get valid pg_hba output. Well I don't think you can get that without making the view less useful for every other purpose. Like, I would want to be able to do WHERE "user" @> array[?] or WHERE database = array[?] or to join against a list of users or databases somewhere else. To do what you suggest would mean the tokens will need to be quoted based on pg_hba.conf syntax requirements. That would mean I would need to check each variable or join value against pg_hba.conf's quoting requirements to compare with it. It seems more practical to have that knowledge if you're actually going to generate a pg_hba.conf than to pass around these quoted strings all the time. On further review I've made a few more changes attached. I think we should change the column names to "users" and "databases" to be clear they're lists and also to avoid the "user" SQL reserved word. I removed the dependency on strlist_to_array which is in objectaddress.c which isn't a very sensible dependency -- it does seem like it would be handy to have a list-based version of construct_array moved to arrayfuncs.c but for now it's not much more work to handle these ourselves. I changed the options to accumulate one big array instead of an array of bunches of options. Previously you could only end up with a singleton array with a comma-delimited string or a two element array with one of those and map=. I think the error if pg_hba fails to reload needs to be LOG. It would be too unexpected to the user who isn't necessarily the one who issued the SIGHUP to spontaneously get a warning. I also removed the "mask" from local entries and made some of the NULLS that shouldn't be possible to happen (unknown auth method or connection method) actually throw errors. -- greg
*** a/doc/src/sgml/catalogs.sgml --- b/doc/src/sgml/catalogs.sgml *************** *** 7393,7398 **** --- 7393,7403 ---- <entry>views</entry> </row> + <row> + <entry><link linkend="view-pg-hba-settings"><structname>pg_hba_settings</structname></link></entry> + <entry>client authentication settings</entry> + </row> + </tbody> </tgroup> </table> *************** *** 9696,9699 **** SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx --- 9701,9786 ---- </sect1> + <sect1 id="view-pg-hba-settings"> + <title><structname>pg_hba_settings</structname></title> + + <indexterm zone="view-pg-hba-settings"> + <primary>pg_hba_settings</primary> + </indexterm> + + <para> + The read-only <structname>pg_hba_settings</structname> view provides + access to the client authentication configuration from pg_hba.conf. + Access to this view is limited to superusers. + </para> + + <table> + <title><structname>pg_hba_settings</> Columns</title> + + <tgroup cols="3"> + <thead> + <row> + <entry>Name</entry> + <entry>Type</entry> + <entry>Description</entry> + </row> + </thead> + <tbody> + <row> + <entry><structfield>type</structfield></entry> + <entry><type>text</type></entry> + <entry>Type of connection</entry> + </row> + <row> + <entry><structfield>databases</structfield></entry> + <entry><type>text[]</type></entry> + <entry>List of database names</entry> + </row> + <row> + <entry><structfield>users</structfield></entry> + <entry><type>text[]</type></entry> + <entry>List of user names</entry> + </row> + <row> + <entry><structfield>address</structfield></entry> + <entry><type>inet</type></entry> + <entry>Client machine address</entry> + </row> + <row> + <entry><structfield>mask</structfield></entry> + <entry><type>inet</type></entry> + <entry>IP Mask</entry> + </row> + <row> + <entry><structfield>compare_method</structfield></entry> + <entry><type>text</type></entry> + <entry>IP address comparison method</entry> + </row> + <row> + <entry><structfield>hostname</structfield></entry> + <entry><type>text</type></entry> + <entry>Client host name</entry> + </row> + <row> + <entry><structfield>method</structfield></entry> + <entry><type>text</type></entry> + <entry>Authentication method</entry> + </row> + <row> + <entry><structfield>options</structfield></entry> + <entry><type>text[]</type></entry> + <entry>Configuration options set for authentication method</entry> + </row> + <row> + <entry><structfield>line_number</structfield></entry> + <entry><type>integer</type></entry> + <entry> + Line number within client authentication configuration file + the current value was set at + </entry> + </row> + </tbody> + </tgroup> + </table> + </sect1> </chapter> *** a/doc/src/sgml/client-auth.sgml --- b/doc/src/sgml/client-auth.sgml *************** *** 680,685 **** local all @admins,+support md5 --- 680,690 ---- local db1,db2,@demodbs all md5 </programlisting> </example> + + <para> + The contents of this file are reflected in the pg_hba_settings view. + See <xref linkend="view-pg-hba-settings"> for details. + </para> </sect1> <sect1 id="auth-username-maps"> *** a/src/backend/catalog/system_views.sql --- b/src/backend/catalog/system_views.sql *************** *** 414,419 **** CREATE RULE pg_settings_n AS --- 414,424 ---- GRANT SELECT, UPDATE ON pg_settings TO PUBLIC; + CREATE VIEW pg_hba_settings AS + SELECT * FROM pg_hba_settings() AS A; + + REVOKE ALL on pg_hba_settings FROM public; + CREATE VIEW pg_timezone_abbrevs AS SELECT * FROM pg_timezone_abbrevs(); *** a/src/backend/libpq/hba.c --- b/src/backend/libpq/hba.c *************** *** 25,38 **** --- 25,43 ---- #include <arpa/inet.h> #include <unistd.h> + #include "access/htup_details.h" #include "catalog/pg_collation.h" + #include "catalog/pg_type.h" + #include "funcapi.h" #include "libpq/ip.h" #include "libpq/libpq.h" + #include "miscadmin.h" #include "postmaster/postmaster.h" #include "regex/regex.h" #include "replication/walsender.h" #include "storage/fd.h" #include "utils/acl.h" + #include "utils/builtins.h" #include "utils/guc.h" #include "utils/lsyscache.h" #include "utils/memutils.h" *************** *** 2220,2222 **** hba_getauthmethod(hbaPort *port) --- 2225,2654 ---- { check_hba(port); } + + + /* LDAP supports 10 currently, keep this well above the most any method needs */ + #define MAX_OPTIONS 16 + + /* + * Fill in suitable values to build a tuple representing the + * HbaLine provided + */ + static void + hba_getvalues_for_line(HbaLine *hba, Datum *values, bool *nulls) + { + ListCell *dbcell; + char buffer[NI_MAXHOST]; + StringInfoData str; + int index = 0; + int noptions; + Datum options[MAX_OPTIONS]; + + /* connection type */ + switch (hba->conntype) + { + case ctLocal: + values[index] = CStringGetTextDatum("local"); + break; + case ctHost: + values[index] = CStringGetTextDatum("host"); + break; + case ctHostSSL: + values[index] = CStringGetTextDatum("hostssl"); + break; + case ctHostNoSSL: + values[index] = CStringGetTextDatum("hostnossl"); + break; + default: + elog(ERROR, "Unexpected Connection Type in parsed HBA entry"); + break; + } + + /* databases */ + index++; + if (list_length(hba->databases) != 0) + { + int j = 0; + HbaToken *tok; + Datum *names = palloc(sizeof(Datum) * list_length(hba->databases)); + + foreach(dbcell, hba->databases) + { + tok = lfirst(dbcell); + names[j++] = CStringGetTextDatum(tok->string); + } + + values[index] = PointerGetDatum(construct_array(names, list_length(hba->databases), + TEXTOID, -1, false, 'i')); + } + else + nulls[index] = true; + + /* users */ + index++; + if (list_length(hba->roles) != 0) + { + int j = 0; + HbaToken *tok; + Datum *roles = palloc(sizeof(Datum) * list_length(hba->roles)); + + foreach(dbcell, hba->roles) + { + tok = lfirst(dbcell); + roles[j++] = CStringGetTextDatum(tok->string); + } + + values[index] = PointerGetDatum(construct_array(roles, list_length(hba->roles), + TEXTOID, -1, false, 'i')); + } + else + nulls[index] = true; + + /* address */ + index++; + if (pg_getnameinfo_all(&hba->addr, sizeof(struct sockaddr_storage), + buffer, sizeof(buffer), + NULL, 0, + NI_NUMERICHOST) == 0) + { + clean_ipv6_addr(hba->addr.ss_family, buffer); + values[index] = DirectFunctionCall1(inet_in, CStringGetDatum(buffer)); + } + else + nulls[index] = true; + + /* mask */ + index++; + if (pg_getnameinfo_all(&hba->mask, sizeof(struct sockaddr_storage), + buffer, sizeof(buffer), + NULL, 0, + NI_NUMERICHOST) == 0) + { + clean_ipv6_addr(hba->addr.ss_family, buffer); + values[index] = DirectFunctionCall1(inet_in, CStringGetDatum(buffer)); + } + else + nulls[index] = true; + + /* compare method */ + index++; + if (hba->conntype == ctLocal) + nulls[index] = true; + else if (hba->ip_cmp_method == ipCmpMask) + values[index] = CStringGetTextDatum("mask"); + else if (hba->ip_cmp_method == ipCmpSameHost) + values[index] = CStringGetTextDatum("samehost"); + else if (hba->ip_cmp_method == ipCmpSameNet) + values[index] = CStringGetTextDatum("samenet"); + else if (hba->ip_cmp_method == ipCmpAll) + values[index] = CStringGetTextDatum("all"); + else + elog(ERROR, "Unexpected Compare Method in parsed HBA entry"); + + /* hostname */ + index++; + if (hba->hostname) + values[index] = CStringGetTextDatum(hba->hostname); + else + nulls[index] = true; + + /* method */ + index++; + switch (hba->auth_method) + { + case uaReject: + values[index] = CStringGetTextDatum("reject"); + break; + case uaImplicitReject: + values[index] = CStringGetTextDatum("implicitreject"); + break; + case uaTrust: + values[index] = CStringGetTextDatum("trust"); + break; + case uaIdent: + values[index] = CStringGetTextDatum("ident"); + break; + case uaPassword: + values[index] = CStringGetTextDatum("password"); + break; + case uaMD5: + values[index] = CStringGetTextDatum("md5"); + break; + case uaGSS: + values[index] = CStringGetTextDatum("gss"); + break; + case uaSSPI: + values[index] = CStringGetTextDatum("sspi"); + break; + case uaPAM: + values[index] = CStringGetTextDatum("pam"); + break; + case uaLDAP: + values[index] = CStringGetTextDatum("ldap"); + break; + case uaCert: + values[index] = CStringGetTextDatum("cert"); + break; + case uaRADIUS: + values[index] = CStringGetTextDatum("radius"); + break; + case uaPeer: + values[index] = CStringGetTextDatum("peer"); + break; + default: + elog(ERROR, "Unexpected Auth Method in parsed HBA entry"); + break; + } + + /* options */ + index++; + noptions = 0; + + if (hba->auth_method == uaGSS || hba->auth_method == uaSSPI) + { + if (hba->include_realm) + options[noptions++] = CStringGetTextDatum("include_realm=true"); + + if (hba->krb_realm) + { + initStringInfo(&str); + appendStringInfoString(&str, "krb_realm="); + appendStringInfoString(&str, hba->krb_realm); + options[noptions++] = CStringGetTextDatum(str.data); + } + } + + if (hba->usermap) + { + initStringInfo(&str); + appendStringInfoString(&str, "map="); + appendStringInfoString(&str, hba->usermap); + options[noptions++] = CStringGetTextDatum(str.data); + } + + if (hba->auth_method == uaLDAP) + { + if (hba->ldapserver) + { + initStringInfo(&str); + appendStringInfoString(&str, "ldapserver="); + appendStringInfoString(&str, hba->ldapserver); + options[noptions++] = CStringGetTextDatum(str.data); + } + + if (hba->ldapport) + { + initStringInfo(&str); + snprintf(buffer, sizeof(buffer), "%d", hba->ldapport); + appendStringInfoString(&str, "ldapport="); + appendStringInfoString(&str, buffer); + options[noptions++] = CStringGetTextDatum(str.data); + } + + if (hba->ldaptls) + options[noptions++] = CStringGetTextDatum("ldaptls=true"); + + if (hba->ldapprefix) + { + initStringInfo(&str); + appendStringInfoString(&str, "ldapprefix="); + appendStringInfoString(&str, hba->ldapprefix); + options[noptions++] = CStringGetTextDatum(str.data); + } + + if (hba->ldapsuffix) + { + initStringInfo(&str); + appendStringInfoString(&str, "ldapsuffix="); + appendStringInfoString(&str, hba->ldapsuffix); + options[noptions++] = CStringGetTextDatum(str.data); + } + + if (hba->ldapbasedn) + { + initStringInfo(&str); + appendStringInfoString(&str, "ldapbasedn="); + appendStringInfoString(&str, hba->ldapbasedn); + options[noptions++] = CStringGetTextDatum(str.data); + } + + if (hba->ldapbinddn) + { + initStringInfo(&str); + appendStringInfoString(&str, "ldapbinddn="); + appendStringInfoString(&str, hba->ldapbinddn); + options[noptions++] = CStringGetTextDatum(str.data); + } + + if (hba->ldapbindpasswd) + { + initStringInfo(&str); + appendStringInfoString(&str, "ldapbindpasswd="); + appendStringInfoString(&str, hba->ldapbindpasswd); + options[noptions++] = CStringGetTextDatum(str.data); + } + + if (hba->ldapsearchattribute) + { + initStringInfo(&str); + appendStringInfoString(&str, "ldapsearchattribute="); + appendStringInfoString(&str, hba->ldapsearchattribute); + options[noptions++] = CStringGetTextDatum(str.data); + } + + if (hba->ldapscope) + { + initStringInfo(&str); + snprintf(buffer, sizeof(buffer), "%d", hba->ldapscope); + appendStringInfoString(&str, "ldapscope="); + appendStringInfoString(&str, buffer); + options[noptions++] = CStringGetTextDatum(str.data); + } + } + + if (hba->auth_method == uaRADIUS) + { + if (hba->radiusserver) + { + initStringInfo(&str); + appendStringInfoString(&str, "radiusserver="); + appendStringInfoString(&str, hba->radiusserver); + options[noptions++] = CStringGetTextDatum(str.data); + } + + if (hba->radiussecret) + { + initStringInfo(&str); + appendStringInfoString(&str, "radiussecret="); + appendStringInfoString(&str, hba->radiussecret); + options[noptions++] = CStringGetTextDatum(str.data); + } + + if (hba->radiusidentifier) + { + initStringInfo(&str); + appendStringInfoString(&str, "radiusidentifier="); + appendStringInfoString(&str, hba->radiusidentifier); + options[noptions++] = CStringGetTextDatum(str.data); + } + + if (hba->radiusport) + { + initStringInfo(&str); + snprintf(buffer, sizeof(buffer), "%d", hba->radiusport); + appendStringInfoString(&str, "radiusport="); + appendStringInfoString(&str, buffer); + options[noptions++] = CStringGetTextDatum(str.data); + } + } + + Assert(noptions <= MAX_OPTIONS); + if (noptions) + values[index] = PointerGetDatum( + construct_array(options, noptions, TEXTOID, -1, false, 'i')); + else + /* Probably should be {} but that makes for a messy looking view */ + nulls[index] = true; + + /* line_number */ + index++; + values[index] = Int32GetDatum(hba->linenumber); + } + + + #define NUM_PG_HBA_SETTINGS_ATTS 10 + + /* + * SQL-accessible SRF to return all the settings from the pg_hba.conf + * file. See the pga_hba_settings view in the "System Catalogs" section of the + * manual. + */ + + Datum + hba_settings(PG_FUNCTION_ARGS) + { + Tuplestorestate *tuple_store; + TupleDesc tupdesc; + ListCell *line; + MemoryContext old_cxt; + + /* + * We must use the Materialize mode to be safe against HBA file reloads + * while the cursor is open. It's also more efficient than having to look + * up our current position in the parsed list every time. + */ + + ReturnSetInfo *rsi = (ReturnSetInfo *) fcinfo->resultinfo; + + if (!rsi || !IsA(rsi, ReturnSetInfo) || + (rsi->allowedModes & SFRM_Materialize) == 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("set-valued function called in context that " + "cannot accept a set"))); + + rsi->returnMode = SFRM_Materialize; + + /* + * Create the tupledesc and tuplestore in the per_query context as + * required for SFRM_Materialize. + */ + old_cxt = MemoryContextSwitchTo(rsi->econtext->ecxt_per_query_memory); + + tupdesc = CreateTemplateTupleDesc(NUM_PG_HBA_SETTINGS_ATTS, false); + TupleDescInitEntry(tupdesc, (AttrNumber) 1, "type", + TEXTOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 2, "databases", + TEXTARRAYOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 3, "users", + TEXTARRAYOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 4, "address", + INETOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 5, "mask", + INETOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 6, "compare_method", + TEXTOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 7, "hostname", + TEXTOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 8, "method", + TEXTOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 9, "options", + TEXTARRAYOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 10, "line_number", + INT4OID, -1, 0); + BlessTupleDesc(tupdesc); + + tuple_store = + tuplestore_begin_heap(rsi->allowedModes & SFRM_Materialize_Random, + false, work_mem); + + MemoryContextSwitchTo(old_cxt); + + /* + * Loop through the list and deparse each entry as it comes, storing it in + * the tuplestore. Any temporary memory allocations here live only for the + * function call lifetime. + */ + foreach(line, parsed_hba_lines) + { + HbaLine *hba = (HbaLine *) lfirst(line); + Datum values[NUM_PG_HBA_SETTINGS_ATTS]; + bool nulls[NUM_PG_HBA_SETTINGS_ATTS]; + HeapTuple tuple; + + MemSet(values, 0, sizeof(values)); + MemSet(nulls, 0, sizeof(nulls)); + + /* Get the next parsed hba line values */ + hba_getvalues_for_line(hba, values, nulls); + + /* build a tuple */ + tuple = heap_form_tuple(tupdesc, values, nulls); + tuplestore_puttuple(tuple_store, tuple); + } + + rsi->setDesc = tupdesc; + rsi->setResult = tuple_store; + + PG_RETURN_NULL(); + } *** a/src/backend/tcop/postgres.c --- b/src/backend/tcop/postgres.c *************** *** 3996,4001 **** PostgresMain(int argc, char *argv[], --- 3996,4009 ---- { got_SIGHUP = false; ProcessConfigFile(PGC_SIGHUP); + + /* + * Reload authentication config files too to refresh + * pg_hba_settings view data. + */ + if (!load_hba()) + ereport(LOG, + (errmsg("pg_hba.conf not reloaded, pg_hba_settings may show stale information"))); } /* *** a/src/include/catalog/pg_proc.h --- b/src/include/catalog/pg_proc.h *************** *** 3023,3028 **** DATA(insert OID = 2078 ( set_config PGNSP PGUID 12 1 0 0 0 f f f f f f v 3 0 2 --- 3023,3030 ---- DESCR("SET X as a function"); DATA(insert OID = 2084 ( pg_show_all_settings PGNSP PGUID 12 1 1000 0 0 f f f f t t s 0 0 2249 "" "{25,25,25,25,25,25,25,25,25,25,25,1009,25,25,25,23}" "{o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}" "{name,setting,unit,category,short_desc,extra_desc,context,vartype,source,min_val,max_val,enumvals,boot_val,reset_val,sourcefile,sourceline}" _null_ show_all_settings _null_ _null_ _null_ )); DESCR("SHOW ALL as a function"); + DATA(insert OID = 3582 ( pg_hba_settings PGNSP PGUID 12 1 1000 0 0 f f f f t t s 0 0 2249 "" "{25,1009,1009,869,869,25,25,25,1009,23}" "{o,o,o,o,o,o,o,o,o,o}" "{type,databases,users,address,mask,compare_method,hostname,method,options,line_number}" _null_ hba_settings _null_ _null_ _null_ )); + DESCR("view client authentication settings"); DATA(insert OID = 1371 ( pg_lock_status PGNSP PGUID 12 1 1000 0 0 f f f f t t v 0 0 2249 "" "{25,26,26,23,21,25,28,26,26,21,25,23,25,16,16}" "{o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}" "{locktype,database,relation,page,tuple,virtualxid,transactionid,classid,objid,objsubid,virtualtransaction,pid,mode,granted,fastpath}" _null_ pg_lock_status _null_ _null_ _null_ )); DESCR("view system lock information"); DATA(insert OID = 1065 ( pg_prepared_xact PGNSP PGUID 12 1 1000 0 0 f f f f t t v 0 0 2249 "" "{28,25,1184,26,26}" "{o,o,o,o,o}" "{transaction,gid,prepared,ownerid,dbid}" _null_ pg_prepared_xact _null_ _null_ _null_ )); *** a/src/include/utils/builtins.h --- b/src/include/utils/builtins.h *************** *** 1090,1095 **** extern Datum quote_nullable(PG_FUNCTION_ARGS); --- 1090,1096 ---- extern Datum show_config_by_name(PG_FUNCTION_ARGS); extern Datum set_config_by_name(PG_FUNCTION_ARGS); extern Datum show_all_settings(PG_FUNCTION_ARGS); + extern Datum hba_settings(PG_FUNCTION_ARGS); /* lockfuncs.c */ extern Datum pg_lock_status(PG_FUNCTION_ARGS); *** a/src/test/regress/expected/rules.out --- b/src/test/regress/expected/rules.out *************** *** 1315,1320 **** pg_group| SELECT pg_authid.rolname AS groname, --- 1315,1331 ---- WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist FROM pg_authid WHERE (NOT pg_authid.rolcanlogin); + pg_hba_settings| SELECT a.type, + a.databases, + a.users, + a.address, + a.mask, + a.compare_method, + a.hostname, + a.method, + a.options, + a.line_number + FROM pg_hba_settings() a(type, databases, users, address, mask, compare_method, hostname, method, options, line_number); pg_indexes| SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname,
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers