1

The default for PostgreSQL is that roles are created per cluster not per database. I would like to have per database roles. I could have sworn I once read about a way to set that up but now I can't find any reference to it. I think it was an initdb option or a createcluster.conf setting or similar but I've read the initdb documentation thoroughly and can't find anything on this.

Is there a way to set per database roles in PostgreSQL?

2

The thing you remembered and are looking for is db_user_namespace. It is not a very good solution, but I wouldn't hold my breath waiting for something better.

  • That is definitely what I read previously and couldn't find. They've really hidden that one. Its not mentioned in the manual pages on roles at all; thank you for pointing me to the right spot. – Gregory Arenius Feb 11 '18 at 3:57
2

There is no direct way. Roles are stored in the cluster-wide system table pg_authid And, quoting the manual:

Because user identities are cluster-wide, pg_authid is shared across all databases of a cluster: there is only one copy of pg_authid per cluster, not one per database.

Bold emphasis mine.

There are also no per database settings in that table or related system tables. So there are no "per database roles".

There are ways to restrict access for each role to a single database. Most effectively in the pg_hba.conf file. But you still can only use each role name once in the whole DB cluster.

I obviously missed the db_user_namespace setting @jjanes remembered. It's the answer to the question, but I wouldn't use it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.