Re: Allow superuser to grant passwordless connection rights on postgres_fdw
On Tue, Dec 3, 2019 at 9:36 AM Stephen Frost wrote: > > > A necessary prerequisite is that Pg be able to cope with passwordless > > user-mappings though. Hence this patch. > > Sure, that part seems like it makes sense to me (and perhaps has now > been done, just catching up on things after travel and holidays and such > here in the US). > It hasn't been done, but I now propose to commit it shortly so other work can proceed. cheers andrew -- Andrew Dunstanhttps://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Allow superuser to grant passwordless connection rights on postgres_fdw
Greetings, * Craig Ringer (cr...@2ndquadrant.com) wrote: > On Mon, 4 Nov 2019 at 12:20, Stephen Frost wrote: > > I've long felt that the way to handle this kind of requirement is to > > have a "trusted remote server" kind of option- where the local server > > authenticates to the remote server as a *server* and then says "this is > > the user on this server, and this is the user that this user wishes to > > be" and the remote server is then able to decide if they accept that, or > > not. > > The original use case for the patch was to allow FDWs to use SSL/TLS client > certificates. Each user-mapping has its own certificate - there's a > separate patch to allow that. So there's no delegation of trust via > Kerberos etc in that particular case. > > I can see value in using Kerberos etc for that too though, as it separates > authorization and authentication in the same manner as most sensible > systems. You can say "user postgres@foo is trusted to vet users so you can > safely hand out tickets for any bar@foo that postgres@foo says is legit". So, just to be clear, the way this *actually* works is a bit different from the way being described above, last time I looked into Kerberos delegations anyway. Essentially, the KDC can be set up to allow 'bar@foo' to request a ticket to delegate to 'postgres@foo', which then allows 'postgres@foo' to connect as if they are 'bar@foo' to some other service (and in some implementations, I believe it's further possible to say that the ticket for 'bar@foo' which is delegated to 'postgres@foo' is only allowed to request tickets for certain specific services, such as 'postgres2@foo' or what-have-you). Note that setting this up with an MIT KDC requires configuring it with an LDAP backend as the traditional KDC database doesn't support this kind of complex delegation control (again, last time I checked anyway). > I would strongly discourage allowing all users on host A to authenticate as > user postgres on host B. But with appropriate user-mappings support, we > could likely support that sort of model for both SSPI and Kerberos. Ideally, both sides would get a 'vote' regarding what's allowed, I would think. That is, the connecting side would have to have a user mapping that says "this authenticated user is allowed to connect to this remote server as this user", and the remote server would have something like "this server that's connecting, validated by the certificate presented by the server, is allowed to authenticate as this user". I feel like we're mostly there by allowing the connecting server to use a certificate to connect to the remote server, while it's also checking the user mapping, and the remote server's pg_hba.conf being configured to allow cert-based auth with a CN mapping from the CN of the connecting server's certificate to authenticate to whatever users the remote server wants to allow. Is that more-or-less the idea here..? > A necessary prerequisite is that Pg be able to cope with passwordless > user-mappings though. Hence this patch. Sure, that part seems like it makes sense to me (and perhaps has now been done, just catching up on things after travel and holidays and such here in the US). Thanks! Stephen signature.asc Description: PGP signature
Re: Allow superuser to grant passwordless connection rights on postgres_fdw
On Sun, Nov 10, 2019 at 4:35 AM Craig Ringer wrote: > > On Mon, 4 Nov 2019 at 12:20, Stephen Frost wrote: >> >> Greetings, >> >> * Andrew Dunstan (andrew.duns...@2ndquadrant.com) wrote: >> > On 11/1/19 12:58 PM, Robert Haas wrote: >> > > On Thu, Oct 31, 2019 at 4:58 PM Andrew Dunstan >> > > wrote: >> > >> This patch allows the superuser to grant passwordless connection rights >> > >> in postgres_fdw user mappings. >> > > This is clearly something that we need, as the current code seems >> > > woefully ignorant of the fact that passwords are not the only >> > > authentication method supported by PostgreSQL, nor even the most >> > > secure. >> > > >> > > But, I do wonder a bit if we ought to think harder about the overall >> > > authentication model for FDW. Like, maybe we'd take a different view >> > > of how to solve this particular piece of the problem if we were >> > > thinking about how FDWs could do LDAP authentication, SSL >> > > authentication, credentials forwarding... >> > >> > I'm certainly open to alternatives. >> >> I've long felt that the way to handle this kind of requirement is to >> have a "trusted remote server" kind of option- where the local server >> authenticates to the remote server as a *server* and then says "this is >> the user on this server, and this is the user that this user wishes to >> be" and the remote server is then able to decide if they accept that, or >> not. > > > The original use case for the patch was to allow FDWs to use SSL/TLS client > certificates. Each user-mapping has its own certificate - there's a separate > patch to allow that. So there's no delegation of trust via Kerberos etc in > that particular case. > > I can see value in using Kerberos etc for that too though, as it separates > authorization and authentication in the same manner as most sensible systems. > You can say "user postgres@foo is trusted to vet users so you can safely hand > out tickets for any bar@foo that postgres@foo says is legit". > > I would strongly discourage allowing all users on host A to authenticate as > user postgres on host B. But with appropriate user-mappings support, we could > likely support that sort of model for both SSPI and Kerberos. > > A necessary prerequisite is that Pg be able to cope with passwordless > user-mappings though. Hence this patch. > > Yeah, I agree. Does anyone else want to weigh in on this? If nobody objects I'd like to tidy this up and get it committed so we can add support for client certs in postgres_fdw, which is the real business at hand, and which I know from various offline comments a number of people are keen to have. cheers andrew -- Andrew Dunstanhttps://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Allow superuser to grant passwordless connection rights on postgres_fdw
On Mon, 4 Nov 2019 at 12:20, Stephen Frost wrote: > Greetings, > > * Andrew Dunstan (andrew.duns...@2ndquadrant.com) wrote: > > On 11/1/19 12:58 PM, Robert Haas wrote: > > > On Thu, Oct 31, 2019 at 4:58 PM Andrew Dunstan > > > wrote: > > >> This patch allows the superuser to grant passwordless connection > rights > > >> in postgres_fdw user mappings. > > > This is clearly something that we need, as the current code seems > > > woefully ignorant of the fact that passwords are not the only > > > authentication method supported by PostgreSQL, nor even the most > > > secure. > > > > > > But, I do wonder a bit if we ought to think harder about the overall > > > authentication model for FDW. Like, maybe we'd take a different view > > > of how to solve this particular piece of the problem if we were > > > thinking about how FDWs could do LDAP authentication, SSL > > > authentication, credentials forwarding... > > > > I'm certainly open to alternatives. > > I've long felt that the way to handle this kind of requirement is to > have a "trusted remote server" kind of option- where the local server > authenticates to the remote server as a *server* and then says "this is > the user on this server, and this is the user that this user wishes to > be" and the remote server is then able to decide if they accept that, or > not. > The original use case for the patch was to allow FDWs to use SSL/TLS client certificates. Each user-mapping has its own certificate - there's a separate patch to allow that. So there's no delegation of trust via Kerberos etc in that particular case. I can see value in using Kerberos etc for that too though, as it separates authorization and authentication in the same manner as most sensible systems. You can say "user postgres@foo is trusted to vet users so you can safely hand out tickets for any bar@foo that postgres@foo says is legit". I would strongly discourage allowing all users on host A to authenticate as user postgres on host B. But with appropriate user-mappings support, we could likely support that sort of model for both SSPI and Kerberos. A necessary prerequisite is that Pg be able to cope with passwordless user-mappings though. Hence this patch. > > To be specific, there would be some kind of 'trust' established between > the servers and only if there is some kind of server-level > authentication, eg: dual TLS auth, or dual GSSAPI auth; and then, a > mapping is defined for that server, which specifies what remote user is > allowed to log in as what local user. > > This would be a server-to-server auth arrangement, and is quite > different from credential forwarding, or similar. I am certainly also a > huge fan of the idea that we support Kerberos/GSSAPI credential > forwarding / delegation, where a client willingly forwards to the PG > server a set of credentials which then allow the PG server to > authenticate as that user to another system (eg: through an FDW to > another PG server). > > Of course, as long as we're talking pie-in-the-sky ideas, I would > certainly be entirely for supporting both. ;) > > Thanks, > > Stephen > -- Craig Ringer http://www.2ndQuadrant.com/ 2ndQuadrant - PostgreSQL Solutions for the Enterprise
Re: Allow superuser to grant passwordless connection rights on postgres_fdw
Greetings, * Andrew Dunstan (andrew.duns...@2ndquadrant.com) wrote: > On 11/1/19 12:58 PM, Robert Haas wrote: > > On Thu, Oct 31, 2019 at 4:58 PM Andrew Dunstan > > wrote: > >> This patch allows the superuser to grant passwordless connection rights > >> in postgres_fdw user mappings. > > This is clearly something that we need, as the current code seems > > woefully ignorant of the fact that passwords are not the only > > authentication method supported by PostgreSQL, nor even the most > > secure. > > > > But, I do wonder a bit if we ought to think harder about the overall > > authentication model for FDW. Like, maybe we'd take a different view > > of how to solve this particular piece of the problem if we were > > thinking about how FDWs could do LDAP authentication, SSL > > authentication, credentials forwarding... > > I'm certainly open to alternatives. I've long felt that the way to handle this kind of requirement is to have a "trusted remote server" kind of option- where the local server authenticates to the remote server as a *server* and then says "this is the user on this server, and this is the user that this user wishes to be" and the remote server is then able to decide if they accept that, or not. To be specific, there would be some kind of 'trust' established between the servers and only if there is some kind of server-level authentication, eg: dual TLS auth, or dual GSSAPI auth; and then, a mapping is defined for that server, which specifies what remote user is allowed to log in as what local user. This would be a server-to-server auth arrangement, and is quite different from credential forwarding, or similar. I am certainly also a huge fan of the idea that we support Kerberos/GSSAPI credential forwarding / delegation, where a client willingly forwards to the PG server a set of credentials which then allow the PG server to authenticate as that user to another system (eg: through an FDW to another PG server). Of course, as long as we're talking pie-in-the-sky ideas, I would certainly be entirely for supporting both. ;) Thanks, Stephen signature.asc Description: PGP signature
Re: Allow superuser to grant passwordless connection rights on postgres_fdw
On 11/1/19 12:58 PM, Robert Haas wrote: > On Thu, Oct 31, 2019 at 4:58 PM Andrew Dunstan > wrote: >> This patch allows the superuser to grant passwordless connection rights >> in postgres_fdw user mappings. > This is clearly something that we need, as the current code seems > woefully ignorant of the fact that passwords are not the only > authentication method supported by PostgreSQL, nor even the most > secure. > > But, I do wonder a bit if we ought to think harder about the overall > authentication model for FDW. Like, maybe we'd take a different view > of how to solve this particular piece of the problem if we were > thinking about how FDWs could do LDAP authentication, SSL > authentication, credentials forwarding... > I'm certainly open to alternatives. cheers andrew -- Andrew Dunstanhttps://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Allow superuser to grant passwordless connection rights on postgres_fdw
On Thu, Oct 31, 2019 at 4:58 PM Andrew Dunstan wrote: > This patch allows the superuser to grant passwordless connection rights > in postgres_fdw user mappings. This is clearly something that we need, as the current code seems woefully ignorant of the fact that passwords are not the only authentication method supported by PostgreSQL, nor even the most secure. But, I do wonder a bit if we ought to think harder about the overall authentication model for FDW. Like, maybe we'd take a different view of how to solve this particular piece of the problem if we were thinking about how FDWs could do LDAP authentication, SSL authentication, credentials forwarding... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Allow superuser to grant passwordless connection rights on postgres_fdw
This patch allows the superuser to grant passwordless connection rights
in postgres_fdw user mappings.
The patch is authored by my colleague Craig Ringer, with slight bitrot
fixed by me.
One use case for this is with passphrase-protected client certificates,
a patch for which will follow shortly.
Here are Craig's remarks on the patch:
postgres_fdw denies a non-superuser the ability to establish a
connection that
doesn't have a password in the connection string, or one that fails
to actually
use the password in authentication. This is to stop the unprivileged
user using
OS-level authentication as the postgres server (peer, ident, trust).
It also
stops unauthorized use of local credentials like .pgpass, a service
file,
client certificate files, etc.
Add the ability for a superuser to create user mappings that
override this
behaviour by setting the passwordless_ok attribute to true in a user
mapping
for a non-superuser. The non-superuser gains the ability to use the
FDW the
mapping applies to even if there's no password in their mapping or
in the
connection string.
This is only safe if the superuser has established that the local
server is
configured safely. It must be configured not to allow
trust/peer/ident/sspi/gssapi auth to allow the OS user the postgres
server runs
as to log in to postgres as a superuser. Client certificate keys can
be used
too, if accessible. But the superuser can already GRANT superrole TO
normalrole, so it's not any sort of new power.
cheers
andrew
--
Andrew Dunstanhttps://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
diff --git a/contrib/postgres_fdw/connection.c b/contrib/postgres_fdw/connection.c
index 57ed5f4b90..4739ab0630 100644
--- a/contrib/postgres_fdw/connection.c
+++ b/contrib/postgres_fdw/connection.c
@@ -17,6 +17,7 @@
#include "access/htup_details.h"
#include "access/xact.h"
#include "catalog/pg_user_mapping.h"
+#include "commands/defrem.h"
#include "mb/pg_wchar.h"
#include "miscadmin.h"
#include "pgstat.h"
@@ -91,7 +92,7 @@ static bool pgfdw_exec_cleanup_query(PGconn *conn, const char *query,
bool ignore_errors);
static bool pgfdw_get_cleanup_result(PGconn *conn, TimestampTz endtime,
PGresult **result);
-
+static bool UserMappingPasswordRequired(UserMapping *user);
/*
* Get a PGconn which can be used to execute queries on the remote PostgreSQL
@@ -274,14 +275,16 @@ connect_pg_server(ForeignServer *server, UserMapping *user)
/*
* Check that non-superuser has used password to establish connection;
* otherwise, he's piggybacking on the postgres server's user
- * identity. See also dblink_security_check() in contrib/dblink.
+ * identity. See also dblink_security_check() in contrib/dblink
+ * and check_conn_params.
*/
- if (!superuser_arg(user->userid) && !PQconnectionUsedPassword(conn))
+ if (!superuser_arg(user->userid) && UserMappingPasswordRequired(user) &&
+ !PQconnectionUsedPassword(conn))
ereport(ERROR,
(errcode(ERRCODE_S_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED),
errmsg("password is required"),
errdetail("Non-superuser cannot connect if the server does not request a password."),
- errhint("Target server's authentication method must be changed.")));
+ errhint("Target server's authentication method must be changed or password_required=false set in the user mapping attributes.")));
/* Prepare new session for use */
configure_remote_session(conn);
@@ -314,12 +317,31 @@ disconnect_pg_server(ConnCacheEntry *entry)
}
}
+/*
+ * Return true if the password_required is defined and false for this user
+ * mapping, otherwise false. The mapping has been pre-validated.
+ */
+static bool
+UserMappingPasswordRequired(UserMapping *user)
+{
+ ListCell *cell;
+
+ foreach(cell, user->options)
+ {
+ DefElem*def = (DefElem *) lfirst(cell);
+ if (strcmp(def->defname, "password_required") == 0)
+ return defGetBoolean(def);
+ }
+
+ return true;
+}
+
/*
* For non-superusers, insist that the connstr specify a password. This
- * prevents a password from being picked up from .pgpass, a service file,
- * the environment, etc. We don't want the postgres user's passwords
- * to be accessible to non-superusers. (See also dblink_connstr_check in
- * contrib/dblink.)
+ * prevents a password from being picked up from .pgpass, a service file, the
+ * environment, etc. We don't want the postgres user's passwords,
+ * certificates, etc to be accessible to non-superusers. (See also
+ * dblink_connstr_check in contrib/dblink.)
*/
static void
check_conn_params(const char **keywords, const char **values, UserMapping *user)
@@ -337,6 +359,10 @@ check_conn_params(const char **keywords, const char **values, UserMapping *user)
return;
}
+ /* ok if the superuser explicitly said so at user mapping creation time */
+ if (!UserMa
