2

We're switching from MongoDB to Postgres at work, and I'm setting up a BDR group.

At this step I'm looking at security and locking down where possible. Hence, I was hoping to set up a replication user (role) and let BDR use that for its replication stuff. I already had the group happily running when using either trusted connections or the postgres user for authentication. Neither seems 100% secure to me, and I particularly don't like how the postgres user's password would appear in clear text in the DSN connections strings in bdr.bdr_nodes. So I tried using this replication user instead.

That is, e.g. in pg_hba.conf:

hostssl appdb,replication replication 172.17.1.2 md5

And the matching DSN connection string,

host=172.17.1.1 dbname=appdb user=replication password=secret

Problem with this approach is illustrated by the following error when issuing bdr.bdr_group_create:

ERROR:  local dsn does not have superuser rights
DETAIL:  The dsn 'host=172.17.1.1 dbname=appdb user=replication password=secret' connects successfully but does not grant superuser rights

And this is where I'm a bit puzzled. Does BDR really need superuser rights for replication? Or can I grant specific privileges to my replication role that'll allow it to do its business without being all-powerful?

Maybe it's just me being paranoid, but I feel that the postgres user should only be used for sysadmin stuff, and that the password to it most definitely shouldn't appear in a table in a cleartext DSN connection string, even if you require special privileges to read from said table.

DanielSmedegaardBuus
  • 977
  • 1
  • 10
  • 18

1 Answers1

2

At this time you can't use BDR without a superuser connection for replication. It's required by logical decoding. We're hoping to address that in the 9.6 edition of BDR.

Please think very carefully about whether you truly need multi-master before going down this path. Are you sure an active/standby setup, with read replicas, isn't sufficient for your needs? Please read the manual section on BDR's limitations and the additional application development considerations carefully.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • Thank you, Craig, for the info :) And it's good to know that it might be addressed in the future. I'll continue using a `replication` user for the BDR connections, then, and grant it superuser rights. That way, once lesser privileges are needed, I can just revoke rights that aren't needed. We'll be adding geographically separate datacenters in the near future, which is why we settled on BDR out of the different replication options available for Postgres. – DanielSmedegaardBuus Sep 02 '15 at 08:23
  • @DanielSmedegaardBuus Cool. Please test carefully first, and consider the consequences of *asynchronous* multi-master carefully. The conflicts chapter in the manual is really required reading. – Craig Ringer Sep 02 '15 at 08:24
  • Oh, DDL locking too. You *really* want to avoid doing DDL during normal operations. – Craig Ringer Sep 02 '15 at 08:29
  • Thanks, @Craig. I've been studying the different options over the past month, with an emphasis on BDR. While the conflicts issues are indeed a bit worrying, I feel that out of the viable solutions for us out there (which unfortunately disqualifies synchronous solutions), BDR is the most applicable to our application. Luckily, since we're rewriting our entire data management system, we should be able to design around most problems and discover conflict issues early on. I'm at least hopeful of a more-or-less smooth transition :) And yeah, I have DDL locking on my "watch out" list :) – DanielSmedegaardBuus Sep 02 '15 at 08:31
  • Sounds good. Keep in touch, and please feel free to file bugs on the github project, too. Time and resources for 9.4 are a bit limited since we're targeting the 9.6 integration of more parts of BDR at the moment, but feedback is always welcome. – Craig Ringer Sep 02 '15 at 08:51
  • Thank you, that's very kind of you :) – DanielSmedegaardBuus Sep 02 '15 at 09:36