Server Fault Asked by JP Beaudry on December 11, 2021
Folks,
I could use your help making my Postgres user access control design better and more aligned with best practices. I’m helping roll out a small production Postgres server but I am not a DB admin, so I know just enough to be dangerous.
There is one server with one install of Postgres v9.2. This install hosts multiple databases, each fully serving a different "customer". In other words, customer1 will not, should not use database2, and so forth. During normal operations, the databases are eached accessed by a matching instance of CakePHP, all co-located on the same server as Postgres. While there may be possible optimizations on this deployment, I’m mostly interested in Psql roles.
Based on what I read, it seems three types of roles would make sense:
Implementing that design is where I’m much less confident. Ownership of DB versus table as well as who should inherit from whom is a bit muddy. Below are my databases and my users. Is that enough info to evaluate the implementation?
Role name | Attributes | Member of
-----------+------------------------------------------------+-------------------
admin | Create role, Create DB | {user1, user2}
postgres | Superuser, Create role, Create DB | {}
user1 | | {}
user2 | | {}
postgres=# l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
admin | postgres | UTF8 | en_US | en_US | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | admin=CTc/postgres
postgres | postgres | UTF8 | en_US | en_US |
template0 | postgres | UTF8 | en_US | en_US | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US | en_US | =c/postgres +
| | | | | postgres=CTc/postgres
user1 | admin | UTF8 | en_US | en_US | =Tc/admin +
| | | | | admin=CTc/admin +
| | | | | user1=CTc/admin
user2 | admin | UTF8 | en_US | en_US | =Tc/admin +
| | | | | admin=CTc/admin +
| | | | | user2=CTc/admin
To prevent external connections and passwords in the clear, pg_hba.conf is as follows:
local all all md5
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
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:
In a single database cluster, somewhat how the OP described, however, my personal choice would be this:
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.
You can also use a combination of the above and use pgBouncer as a router.
Answered by Florin Asăvoaie on December 11, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP