On Mon, Mar 2, 2015 at 1:42 PM, Greg Stark <[email protected]> wrote:
> So earlier someone commented that using lists list_nth() seemed odd and a
> tuplestore might be better. In fact using lists this way is O(n^2). I've
> done some quick tests and it doesn't start being a problem until about
> 10,000 lines which obviously isn't a terribly common way to use
> pg_hba_settings. However we have in the past had people doing multi-tenant
> clusters with hundreds or thousands of databases in a cluster complaining
> about scalability of certain operations. It would be a shame to introduce a
> new one.
>
> It does seem annoying to use a tuplestore as IIRC the function scan node
> also materializes the results in recent years. But at least it would scale
> linearly.
>
So I didn't get the memo about SFRM_Materialize. Here's a rewrite of this
using that interface which seems to test ok up to 100k. At that point I
start running into memory errors on reading the HBA file so I guess that's
an indication that's large enough to stop worrying about it.
--
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>database</structfield></entry>
+ <entry><type>text[]</type></entry>
+ <entry>List of database names</entry>
+ </row>
+ <row>
+ <entry><structfield>user</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,44 ----
#include <arpa/inet.h>
#include <unistd.h>
+ #include "access/htup_details.h"
#include "catalog/pg_collation.h"
+ #include "catalog/pg_type.h"
+ #include "catalog/objectaddress.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)
--- 2226,2632 ----
{
check_hba(port);
}
+
+ /*
+ * 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;
+ HbaToken *tok;
+ int index = 0;
+ List *options = NULL;
+
+ /* 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:
+ nulls[index] = true;
+ break;
+ }
+
+ /* database */
+ index++;
+ if (list_length(hba->databases) != 0)
+ {
+ List *names = NULL;
+
+ foreach(dbcell, hba->databases)
+ {
+ tok = lfirst(dbcell);
+ names = lappend(names, tok->string);
+ }
+
+ values[index] = PointerGetDatum(strlist_to_textarray(names));
+ }
+ else
+ nulls[index] = true;
+
+ /* user */
+ index++;
+ if (list_length(hba->roles) != 0)
+ {
+ List *roles = NULL;
+
+ foreach(dbcell, hba->roles)
+ {
+ tok = lfirst(dbcell);
+ roles = lappend(roles, tok->string);
+ }
+
+ values[index] = PointerGetDatum(strlist_to_textarray(roles));
+ }
+ 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++;
+ switch (hba->ip_cmp_method)
+ {
+ case ipCmpMask:
+ values[index] = CStringGetTextDatum("mask");
+ break;
+ case ipCmpSameHost:
+ values[index] = CStringGetTextDatum("samehost");
+ break;
+ case ipCmpSameNet:
+ values[index] = CStringGetTextDatum("samenet");
+ break;
+ case ipCmpAll:
+ values[index] = CStringGetTextDatum("all");
+ break;
+ default:
+ nulls[index] = true;
+ break;
+ }
+
+ /* 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:
+ nulls[index] = true;
+ break;
+ }
+
+ /* options */
+ index++;
+
+ if (hba->auth_method == uaGSS || hba->auth_method == uaSSPI)
+ {
+ initStringInfo(&str);
+
+ if (hba->include_realm)
+ appendStringInfoString(&str, "include_realm=true,");
+
+ if (hba->krb_realm)
+ {
+ appendStringInfoString(&str, "krb_realm=");
+ appendStringInfoString(&str, hba->krb_realm);
+ appendStringInfoString(&str, ",");
+ }
+
+ if (str.len > 1)
+ str.data[str.len - 1] = '\0';
+
+ options = lappend(options, str.data);
+ }
+
+ if ((hba->usermap)
+ && (hba->auth_method == uaIdent || hba->auth_method == uaPeer
+ || hba->auth_method == uaCert || hba->auth_method == uaGSS
+ || hba->auth_method == uaSSPI))
+ {
+ initStringInfo(&str);
+ appendStringInfoString(&str, "map=");
+ appendStringInfoString(&str, hba->usermap);
+ options = lappend(options, str.data);
+ }
+
+ if (hba->auth_method == uaLDAP)
+ {
+ initStringInfo(&str);
+ if (hba->ldapserver)
+ {
+ appendStringInfoString(&str, "ldapserver=");
+ appendStringInfoString(&str, hba->ldapserver);
+ appendStringInfoString(&str, ",");
+ }
+
+ if (hba->ldapport)
+ {
+ snprintf(buffer, sizeof(buffer), "%d", hba->ldapport);
+ appendStringInfoString(&str, "ldapport=");
+ appendStringInfoString(&str, buffer);
+ appendStringInfoString(&str, ",");
+ }
+
+ if (hba->ldaptls)
+ appendStringInfoString(&str, "ldaptls=true,");
+
+ if (hba->ldapprefix)
+ {
+ appendStringInfoString(&str, "ldapprefix=");
+ appendStringInfoString(&str, hba->ldapprefix);
+ appendStringInfoString(&str, ",");
+ }
+
+ if (hba->ldapsuffix)
+ {
+ appendStringInfoString(&str, "ldapsuffix=");
+ appendStringInfoString(&str, hba->ldapsuffix);
+ appendStringInfoString(&str, ",");
+ }
+
+ if (hba->ldapbasedn)
+ {
+ appendStringInfoString(&str, "ldapbasedn=");
+ appendStringInfoString(&str, hba->ldapbasedn);
+ appendStringInfoString(&str, ",");
+ }
+
+ if (hba->ldapbinddn)
+ {
+ appendStringInfoString(&str, "ldapbinddn=");
+ appendStringInfoString(&str, hba->ldapbinddn);
+ appendStringInfoString(&str, ",");
+ }
+
+ if (hba->ldapbindpasswd)
+ {
+ appendStringInfoString(&str, "ldapbindpasswd=");
+ appendStringInfoString(&str, hba->ldapbindpasswd);
+ appendStringInfoString(&str, ",");
+ }
+
+ if (hba->ldapsearchattribute)
+ {
+ appendStringInfoString(&str, "ldapsearchattribute=");
+ appendStringInfoString(&str, hba->ldapsearchattribute);
+ appendStringInfoString(&str, ",");
+ }
+
+ if (hba->ldapscope)
+ {
+ snprintf(buffer, sizeof(buffer), "%d", hba->ldapscope);
+ appendStringInfoString(&str, "ldapscope=");
+ appendStringInfoString(&str, buffer);
+ appendStringInfoString(&str, ",");
+ }
+
+ if (str.len > 1)
+ str.data[str.len - 1] = '\0';
+
+ options = lappend(options, str.data);
+ }
+
+ if (hba->auth_method == uaRADIUS)
+ {
+ initStringInfo(&str);
+ if (hba->radiusserver)
+ {
+ appendStringInfoString(&str, "radiusserver=");
+ appendStringInfoString(&str, hba->radiusserver);
+ appendStringInfoString(&str, ",");
+ }
+
+ if (hba->radiussecret)
+ {
+ appendStringInfoString(&str, "radiussecret=");
+ appendStringInfoString(&str, hba->radiussecret);
+ appendStringInfoString(&str, ",");
+ }
+
+ if (hba->radiusidentifier)
+ {
+ appendStringInfoString(&str, "radiusidentifier=");
+ appendStringInfoString(&str, hba->radiusidentifier);
+ appendStringInfoString(&str, ",");
+ }
+
+ if (hba->radiusport)
+ {
+ snprintf(buffer, sizeof(buffer), "%d", hba->radiusport);
+ appendStringInfoString(&str, "radiusport=");
+ appendStringInfoString(&str, buffer);
+ appendStringInfoString(&str, ",");
+ }
+
+ if (str.len > 1)
+ str.data[str.len - 1] = '\0';
+
+ options = lappend(options, str.data);
+ }
+
+ if (options)
+ values[index] = PointerGetDatum(strlist_to_textarray(options));
+ else
+ nulls[index] = true;
+
+ /* line_number */
+ index++;
+ values[index] = Int32GetDatum(hba->linenumber);
+ }
+
+ #define NUM_PG_HBA_SETTINGS_ATTS 10
+
+ Datum
+ hba_settings(PG_FUNCTION_ARGS)
+ {
+ Tuplestorestate *tuple_store;
+ TupleDesc tupdesc;
+ ListCell *line;
+ MemoryContext old_cxt;
+
+ 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;
+
+ old_cxt = MemoryContextSwitchTo(rsi->econtext->ecxt_per_query_memory);
+
+ /*
+ * need a tuple descriptor representing NUM_PG_HBA_SETTINGS_ATTS
+ * columns of the appropriate types
+ */
+ tupdesc = CreateTemplateTupleDesc(NUM_PG_HBA_SETTINGS_ATTS, false);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 1, "type",
+ TEXTOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 2, "database",
+ TEXTARRAYOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 3, "user",
+ 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);
+
+ 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(WARNING,
+ (errmsg("pg_hba.conf not reloaded")));
}
/*
*** 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,database,user,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/libpq/hba.h
--- b/src/include/libpq/hba.h
***************
*** 103,107 **** extern int check_usermap(const char *usermap_name,
const char *pg_role, const char *auth_user,
bool case_sensitive);
extern bool pg_isblank(const char c);
-
#endif /* HBA_H */
--- 103,106 ----
*** 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.database,
+ a."user",
+ a.address,
+ a.mask,
+ a.compare_method,
+ a.hostname,
+ a.method,
+ a.options,
+ a.line_number
+ FROM pg_hba_settings() a(type, database, "user", 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
