On Sat, Dec 26, 2009 at 10:55 AM, Fujii Masao <masao.fu...@gmail.com> wrote: > On Fri, Dec 25, 2009 at 9:56 PM, Andrew Dunstan <and...@dunslane.net> wrote: >> I don't see the use case for it - .pgpass is for single users, not a whole >> cluster. And it does support wildcards, which takes care of the 'all' case. >> In the case of pg_hba.conf we don't know in advance who will actually be >> connecting. But in the case of .pgpass we do, so the extra utility of >> 'sameuser', 'samerole' and 'samegroup' in this case is not apparent to me. > > OK, I might need to focus only on the use of replication, without > being avaricious.
The attached patch supports new keyword 'replication' on .pgpass file. This keyword is used to specify the password for the standby server to connect to the primary server. Without this keyword, since replication doesn't correspond to the real database, the password cannot be supplied in .pgpass file, instead, must be specified in the conninfo string or the environment variable PGPASSWORD. For example, if the primary is running on host IP 192.168.1.50, port 5432, the superuser's name for replication is foo, and the password is foopass, the following line should be added to the .pgpass file on the standby so that it can connect to the primary without prompting for password. 192.168.1.50:5432:replication:foo:foopass Thought? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
*** a/doc/src/sgml/libpq.sgml --- b/doc/src/sgml/libpq.sgml *************** *** 6024,6030 **** myEventProc(PGEventId evtId, void *evtInfo, void *passThrough) <replaceable>hostname</replaceable>:<replaceable>port</replaceable>:<replaceable>database</replaceable>:<replaceable>username</replaceable>:<replaceable>password</replaceable> </synopsis> Each of the first four fields can be a literal value, or ! <literal>*</literal>, which matches anything. The password field from the first line that matches the current connection parameters will be used. (Therefore, put more-specific entries first when you are using wildcards.) If an entry needs to contain <literal>:</literal> or --- 6024,6031 ---- <replaceable>hostname</replaceable>:<replaceable>port</replaceable>:<replaceable>database</replaceable>:<replaceable>username</replaceable>:<replaceable>password</replaceable> </synopsis> Each of the first four fields can be a literal value, or ! <literal>*</literal>, which matches anything. Also <replaceable>database ! </replaceable> can be <literal>replication</literal>. The password field from the first line that matches the current connection parameters will be used. (Therefore, put more-specific entries first when you are using wildcards.) If an entry needs to contain <literal>:</literal> or *************** *** 6032,6038 **** myEventProc(PGEventId evtId, void *evtInfo, void *passThrough) A host name of <literal>localhost</> matches both TCP (host name <literal>localhost</>) and Unix domain socket (<literal>pghost</> empty or the default socket directory) connections coming from the local ! machine. </para> <para> --- 6033,6041 ---- A host name of <literal>localhost</> matches both TCP (host name <literal>localhost</>) and Unix domain socket (<literal>pghost</> empty or the default socket directory) connections coming from the local ! machine. Quoting one of the keywords <literal>*</literal> or ! <literal>replication</literal> makes the name lose its special character, ! and just matches each field with that name. </para> <para> *** a/doc/src/sgml/log-shipping.sgml --- b/doc/src/sgml/log-shipping.sgml *************** *** 439,447 **** host replication foo 192.168.1.100/32 md5 </programlisting> </para> <para> ! The host name and port number of the primary, user name to connect as, ! and password are specified in the <filename>recovery.conf</> file or ! the corresponding environment variable on the standby. For example, if the primary is running on host IP <literal>192.168.1.50</>, port <literal>5432</literal>, the superuser's name for replication is <literal>foo</>, and the password is <literal>foopass</>, the administrator --- 439,449 ---- </programlisting> </para> <para> ! The host name and port number of the primary, and user name to connect as ! are specified in the <filename>recovery.conf</> file or the corresponding ! environment variable on the standby. The password can be supplied in the ! <filename>.pgpass</> file by using the <literal>replication</> keyword ! (see <xref linkend="libpq-pgpass">). For example, if the primary is running on host IP <literal>192.168.1.50</>, port <literal>5432</literal>, the superuser's name for replication is <literal>foo</>, and the password is <literal>foopass</>, the administrator *************** *** 449,456 **** host replication foo 192.168.1.100/32 md5 <programlisting> # The standby connects to the primary that is running on host 192.168.1.50 ! # and port 5432 as the user "foo" whose password is "foopass". ! primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass' </programlisting> </para> </sect2> --- 451,466 ---- <programlisting> # The standby connects to the primary that is running on host 192.168.1.50 ! # and port 5432 as the user "foo". ! primary_conninfo = 'host=192.168.1.50 port=5432 user=foo' ! </programlisting> ! ! In addition to that, the following line should be added to the ! <filename>.pgpass</> file on the standby so that it can connect to ! the primary without prompting for password. ! ! <programlisting> ! 192.168.1.50:5432:replication:foo:foopass </programlisting> </para> </sect2> *** a/src/interfaces/libpq/fe-connect.c --- b/src/interfaces/libpq/fe-connect.c *************** *** 83,88 **** static int ldapServiceLookup(const char *purl, PQconninfoOption *options, --- 83,90 ---- #define PGPASSFILE "pgpass.conf" #endif + #define PWDFLINELEN NAMEDATALEN*5 + /* * Pre-8.5 servers will return this SQLSTATE if asked to set * application_name in a startup packet. We hard-wire the value rather *************** *** 273,280 **** static void defaultNoticeProcessor(void *arg, const char *message); static int parseServiceInfo(PQconninfoOption *options, PQExpBuffer errorMessage); static char *pwdfMatchesString(char *buf, char *token); static char *PasswordFromFile(char *hostname, char *port, char *dbname, ! char *username); static void default_threadlock(int acquire); --- 275,284 ---- static int parseServiceInfo(PQconninfoOption *options, PQExpBuffer errorMessage); static char *pwdfMatchesString(char *buf, char *token); + static char *pwdfMatchesDbName(char *buf, char *token, bool replication); + static char *nextPwdfToken(char *buf, char *token); static char *PasswordFromFile(char *hostname, char *port, char *dbname, ! char *username, bool replication); static void default_threadlock(int acquire); *************** *** 516,522 **** connectOptions2(PGconn *conn) if (conn->pgpass) free(conn->pgpass); conn->pgpass = PasswordFromFile(conn->pghost, conn->pgport, ! conn->dbName, conn->pguser); if (conn->pgpass == NULL) conn->pgpass = strdup(DefaultPassword); } --- 520,527 ---- if (conn->pgpass) free(conn->pgpass); conn->pgpass = PasswordFromFile(conn->pghost, conn->pgport, ! conn->dbName, conn->pguser, ! conn->replication); if (conn->pgpass == NULL) conn->pgpass = strdup(DefaultPassword); } *************** *** 3905,3960 **** defaultNoticeProcessor(void *arg, const char *message) } /* ! * returns a pointer to the next token or NULL if the current * token doesn't match */ static char * pwdfMatchesString(char *buf, char *token) { char *tbuf, *ttok; bool bslash = false; if (buf == NULL || token == NULL) return NULL; tbuf = buf; ttok = token; ! if (tbuf[0] == '*' && tbuf[1] == ':') ! return tbuf + 2; ! while (*tbuf != 0) { if (*tbuf == '\\' && !bslash) { tbuf++; bslash = true; } ! if (*tbuf == ':' && *ttok == 0 && !bslash) ! return tbuf + 1; ! bslash = false; ! if (*ttok == 0) ! return NULL; ! if (*tbuf == *ttok) { tbuf++; ! ttok++; } ! else ! return NULL; } ! return NULL; } /* Get a password from the password file. Return value is malloc'd. */ static char * ! PasswordFromFile(char *hostname, char *port, char *dbname, char *username) { FILE *fp; char pgpassfile[MAXPGPATH]; struct stat stat_buf; char *passfile_env; ! ! #define LINELEN NAMEDATALEN*5 ! char buf[LINELEN]; if (dbname == NULL || strlen(dbname) == 0) return NULL; --- 3910,4014 ---- } /* ! * Returns a pointer to the next token or NULL if the current * token doesn't match */ static char * pwdfMatchesString(char *buf, char *token) { + char pwdftok[PWDFLINELEN]; + + if (buf == NULL || token == NULL) + return NULL; + if ((buf = nextPwdfToken(buf, pwdftok)) == NULL) + return NULL; + if (strcmp(pwdftok, "*\n") == 0 || + strcmp(pwdftok, token) == 0) + return buf; + return NULL; + } + + /* + * Returns a pointer to the next token or NULL if the given + * database name doesn't match + */ + static char * + pwdfMatchesDbName(char *buf, char *token, bool replication) + { + char pwdftok[PWDFLINELEN]; + + if (buf == NULL || token == NULL) + return NULL; + if ((buf = nextPwdfToken(buf, pwdftok)) == NULL) + return NULL; + if (strcmp(pwdftok, "*\n") == 0 || + (strcmp(pwdftok, "replication\n") == 0 && replication) || + strcmp(pwdftok, token) == 0) + return buf; + return NULL; + } + + /* + * Grab one token out of 'buf'. Return a pointer to the next + * token or NULL if there is no token. + */ + static char * + nextPwdfToken(char *buf, char *token) + { char *tbuf, *ttok; bool bslash = false; + bool in_quote = false; + bool saw_quote = false; if (buf == NULL || token == NULL) return NULL; tbuf = buf; ttok = token; ! for (;;) { + if (*tbuf == 0) + return NULL; if (*tbuf == '\\' && !bslash) { tbuf++; bslash = true; } ! if (*tbuf == '"' && !bslash) { tbuf++; ! bslash = false; ! in_quote = !in_quote; ! saw_quote = true; } ! if (*tbuf == ':' && !bslash && !in_quote) ! break; ! bslash = false; ! *ttok++ = *tbuf++; } ! ! *ttok = '\0'; ! if (!saw_quote && ! (strcmp(token, "*") == 0 || ! strcmp(token, "replication") == 0)) ! { ! /* append newline to a magical keyword */ ! *ttok++ = '\n'; ! *ttok = '\0'; ! } ! return tbuf + 1; } /* Get a password from the password file. Return value is malloc'd. */ static char * ! PasswordFromFile(char *hostname, char *port, char *dbname, char *username, ! bool replication) { FILE *fp; char pgpassfile[MAXPGPATH]; struct stat stat_buf; char *passfile_env; ! char buf[PWDFLINELEN]; if (dbname == NULL || strlen(dbname) == 0) return NULL; *************** *** 4040,4046 **** PasswordFromFile(char *hostname, char *port, char *dbname, char *username) if ((t = pwdfMatchesString(t, hostname)) == NULL || (t = pwdfMatchesString(t, port)) == NULL || ! (t = pwdfMatchesString(t, dbname)) == NULL || (t = pwdfMatchesString(t, username)) == NULL) continue; ret = strdup(t); --- 4094,4100 ---- if ((t = pwdfMatchesString(t, hostname)) == NULL || (t = pwdfMatchesString(t, port)) == NULL || ! (t = pwdfMatchesDbName(t, dbname, replication)) == NULL || (t = pwdfMatchesString(t, username)) == NULL) continue; ret = strdup(t); *************** *** 4050,4057 **** PasswordFromFile(char *hostname, char *port, char *dbname, char *username) fclose(fp); return NULL; - - #undef LINELEN } /* --- 4104,4109 ----
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers