Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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:

  • Superuser postgres with non default password
  • An administrator role that does not have superuser privileges for routine maintenance, DB creation, backup, restore. Should be able to do anything with all the customer databases.
  • User roles with just the ability to CRUD in their respective database. More rights on their own DB could be tolerated if it cleans up the implementation.

Implementing that design is where I'm much less confident. Ownership of DB versus table and well as who should inherit from who 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 such:

local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.