What are the downsides of using PARTITION for multi-tenant access?
10k stores so we're talking about 10GB of data. One possibility that came up is to create partitions for each store so they will be completely isolated. It seems ideal for our use case, but we don't have experience with this many partitions so I don't know if there are gotchas.


10,000 partitions is insanity. Partitions are done for performance not for security. Their use has planner, index, and underlying file-system implications. There is absolutely no reason to ever do that. You can however create 10,000 users, or authenticate against LDAP and manage security like that. For more information see this, and examples.


https://www.postgresql.org/docs/current/static/ddl-rowsecurity.html


Ultimately, I probably wouldn't do either unless I absolutely needed too. It's easier to design abstracted secure app-level interfaces (database-models/middleware etc) to authenticate and to fly with that. I've seen row-level permissions used in production code, but typically that's for a class of data access.

For example. Perhaps your application has two levels of access one for unauthenticated forward-facing web servers; and, one for on-premise access. In this case, you can ensure with pg_hba that admins are on site (or are otherwise hackers clever enough to have compromised on-site equipment).