Postgresql – How to specify a client certificate to psql

The underlying problem here is the renegotiation refusal. This stems from activity over the last year or two to fix a TLS vulnerability, and there were various interim fixes of refusing to renegotiate until a new TLS extension was implemented. So one thing you need to do is ensure your SSL (OpenSSL) and possibly therefore your Apache HTTPD as well is/are as up to date as possible.

I know this is an old question but I will try to answer it even now, as I got to do some research related to this.

What you are trying to do is called multi-tenancy at database level. This can be achieved in two ways:

1. In a single database cluster, somewhat how the OP described, however, my personal choice would be this:

   • postgres user uses peer authentication and is not allowed password connections. MD5 authentication, in my opinion, is a bad practice. If you get into any kind of trouble with database consistency or this kind of things, you will still be able to login if you let postgres use peer auth.
   • Each customer should get their own schema and not database. There are multiple reasons for this:
     • Owning the whole database would grant a lot of privileges.
     • Owning only specific tables would bring problems for developers and would always require to ask admins to add permissions and stuff.
     • As such, in a normal setup, each of them would get access to create things inside their schema, including tables, views, triggers, etc.
     • All of them use the same connection string except the username. In postgres, by default, if you have a schema with the name of your user, it is automatically in your search_path.
   • I would opt for not having an admin user that is able to access each schema, as a security measure. You should do backups either by dumping each schema with their own user or using the PITR technique of PostgreSQL. You would still need to use the postgres user to create new schemas, I would go for a sudo rule and a script for that.
   • Many security good practices recommend dropping the default schema so - there we go.
   • This solution is extremely suitable if the DB for each customer is small and you got tons of customers.
   • If your application handles multi-tenancy, it can use a single connection pool for all customers. Of course, this eliminates many of the security enhancements above but could have performance benefits, specially when you have a large number of customers (if you have like 500-1000 separate datasources and you use connection pooling, it will be quite overwhelming).

2. Each customer gets their own database cluster. This is my preferred solution especially because I usually work with applications that have big databases per each customer.

   • This one brings very good data separation. You can use separate storage volumes for each customer, allocate CPU and Memory limitations (using docker?).
   • Really good flexibility on what each customer needs in their instance. They could be either similar or have distinct features.
   • Very easy to scale on both directions (up and out).
   • I also use separate virtual IPs where each cluster listens for connections, making the scale-out to not needing datasource reconfiguration.
   • PITR backups are per customer so it will be easier to restore a single customer comparing to the per-schema multi-tenancy.
   • On complex setups, each customer might need multiple databases, schemas, users and roles, etc. so this is a way better solution in those cases.

You can also use a combination of the above and use pgBouncer as a router.