PostgreSQL or CockroachDB for a Security-Critical CRM with Millions of Documents?

For the files themselves, I would generally use private object storage rather than putting document contents in PostgreSQL. Store the object key, checksum, size, content type, version, and related business metadata in the database, then issue short-lived signed URLs only after the user has passed your application-level authorization checks. Keep the bucket private and enable the provider's encryption and versioning features. Object storage is cheaper and better suited to large blobs, but it still needs its own retention, recovery, access logging, and disaster-recovery plan—signed URLs are not a replacement for authorization or backups.

PostgreSQL plus a properly designed HA setup and object storage would be my default choice. Millions of documents do not necessarily mean millions of large database records; if the files are external, the metadata and relationship tables are usually quite manageable. CockroachDB becomes more compelling when you genuinely need multi-region writes, continued operation through a regional failure, or strong data-residency placement. Otherwise, its distributed behavior and operational tradeoffs may add complexity without solving a real requirement.

Use defense in depth for tenant and document authorization. PostgreSQL row-level security can be a strong second barrier, but do not rely on it casually: application roles should not own the tables, table owners bypass RLS unless forced, and superusers or BYPASSRLS roles can bypass it entirely. Test policies using the same restricted roles used in production.

I would enforce coarse tenant isolation in the database and keep detailed document permissions explicit in the data model, with application authorization checking ownership, matter membership, classification, and user attributes. RLS can also enforce document-level rules, but complex policies can become difficult to reason about and may hurt query performance, so keep them small, well-tested, and observable. Add immutable audit events for reads, downloads, permission changes, administrative actions, and failed access attempts; encrypt data in transit and at rest; use managed keys or a KMS with rotation and separation of duties; and regularly test backups, restores, failover, and disaster-recovery procedures.

For a CRM, PostgreSQL is unlikely to become a volume bottleneck merely because the system references millions of documents. The first limits are more likely to be query design, indexing, connection management, workload spikes, storage latency, and the operational ceiling of the primary writer. Read replicas, partitioning where justified, caching, background jobs, and a well-sized primary can go a long way. CockroachDB's main benefit here is distributed availability rather than automatically better performance.

A PostgreSQL application also needs to account for CockroachDB's different transaction behavior. Its serializable isolation can cause normal retryable transaction errors under contention, so application code must retry transactions safely. Features and assumptions familiar from PostgreSQL may also differ; for example, notification mechanisms such as LISTEN/NOTIFY are not interchangeable. Test the actual workload before committing.