When I heard about foreign tables using the new postgres_fdw foreign data wrapper in PostgreSQL 9.3, I was pretty excited. We hadn’t upgraded to 9.3 so I waited until we did before I did any serious testing. Having done more experimentation with it, I have to say I’m somewhat disappointed. Why? Because of how authentication was implemented.

I’m going to get this out of the way now: The postgres_fdw foreign data wrapper only works with hard-coded plain-text passwords, forever the bane of security-conscious IT teams everywhere. These passwords aren’t even obfuscated or encrypted locally. The only implemented security is that the pg_user_mapping table is limited to superuser access to actually see the raw passwords. Everyone else sees this:

postgres=> SELECT * FROM pg_user_mapping;
ERROR:  permission denied for relation pg_user_mapping

The presumption is that a database superuser can change everyone’s password anyway, so it probably doesn’t matter that it’s hardcoded and visible in this view. And the developers have a point; without the raw password, how can a server-launched client log into the remote database? Perhaps the real problem is that there’s no mechanism for forwarding authentication from database to database.

This is especially problematic when attempting to federate a large database cluster. If I have a dozen nodes that all have the same user credentials, I have to create mappings to every single user, for every single foreign table, on every single independent node, or revert to trust-based authentication.

This can be scripted to a certain extent, but to what end? If a user were to change their own password, this breaks every foreign data wrapper they could previously access. This user now has to give their password to the DBA to broadcast across all the nodes with modifications to the user mappings. In cases where LDAP, Kerberos, GSSAPI, peer, or other token forwarding authentication is in place, this might not even be possible or advised.

Oracle solved this problem by tying DBLINK tables to a specific user during creation time. An access to a certain table authenticates as that user in all cases. This means a DBA can set aside a specific user for foreign table access purposes, and use a password that’s easy to change across the cluster if necessary. Grants take care of who has access to these objects. Of course, since postgres_fdw is read/write, this would cause numerous permissions concerns.

So what are we left with? How can we actually use PostgreSQL foreign tables securely? At this point, I don’t believe it’s possible unless I’m missing something. And I’m extremely confused at how this feature got so far along without any real way to lock it down in the face of malleable passwords. Our systems have dozens of users who are forced by company policy to change their passwords every 90 days, thus none of these users can effectively access any foreign table I’d like to create.

And no, you can’t create a mapping and then grant access to it. In the face of multiple mapping grants, which one would PostgreSQL use? No, if there’s a way to solve this particular little snag, it won’t be that convenient. If anyone has ideas, or would like to go into length at how wrong I am, please do! Otherwise, I’m going to have to use internal users of my own design and materialized views to wrap the foreign tables; extremely large tables will need some other solution.

Foreign Tables are not as Useful as I Hoped
Tagged on:                 

5 thoughts on “Foreign Tables are not as Useful as I Hoped

  • Right!?

    This is only one of a handful of reasons I’m not endorsing FDW for use at our establishment either. I understand that postgres is open-source, so we can’t be too upset about the state of the feature, but it seems to me there should be some underlying mechanism to use public key authentication, or authentication redirection through LDAP, such that, at most, only user names are ever passed around in the clear. In fact, I’m surprised that they aren’t using secure tunneling between postgres instances.

    Additionally, the planner needs to get smarter about constraints. If multiple tables reside on the foreign instance, all the projection and selection criteria for both – including the join – should get pushed down, and only the resulting node should be returned to the calling database.

    Until these kinds of rough edges get polished off, FDW is an intriguing facility to import data into postgres from foreign sources but not an interoperation mechanism.

    1. That’s pretty much it. I’ve gone from wanting to use it everywhere, to restricting it as a data import replacement. I didn’t really mind about the optimization issues, since those would get hashed out eventually. But the authentication system is fundamentally broken for anything but edge-case use.

      And yeah, I know it’s open source and I should “fix it myself, or STFU,” but trust me, nobody wants me coding C. 🙂

  • Shaun, even if you can’t write the code, it would be very helpful for you to get on pgsql-hackers and talk about what you evision as a security model for postgres_fdw. Part of the reason we don’t have more sophisticated connection security is that there’s no consensus on how it should work.

  • Great article! I was considering using the FDW to make our web app more database agnostic (using postgres as our main database) but after reading this I am realizing it still needs a few enhancements.

Comments are closed.