On Sep 13, 2013, at 8:26 AM, Scott Cherf <cherf@ambient-light.com> wrote:

Does anyone have a cheap trick for adding a "role" to the postgres DB CalendarServer uses? I installed the server under one user ID and wanted to move it to another but had to export the data, reinstall then import so I could run it with different permissions. There must be a simple way to just add a new role to the DB but it wasn't obvious?

Official docs are here: http://www.postgresql.org/docs/9.2

It’s hard for me to predict what your exact steps would need to be, but one simple approach would be:

* create the new user (role) in postgres

* grant the new user the same rights as the existing user

Example below. Note that in this example, I don’t have postgres installed system-wide (it’s installed to ~/pg), which is why I’m saying ./bin/psql instead of just psql. YMMV. I’m also not setting any passwords for the new role; if your postgres service can be reached over the network, you may want passwords.

# First, list the current roles.

{38} admin@linuxbuilder [~/pg] % ./bin/psql template1 -c '\du'

                             List of roles

 Role name |                   Attributes                   | Member of 

-----------+------------------------------------------------+-----------

 admin     | Superuser, Create role, Create DB, Replication | {}

 caldav    | Superuser, Create role, Create DB              | {}

Let’s assume caldav is the ‘old’ account.

# Create a new role, validate it

{39} admin@linuxbuilder [~/pg] % ./bin/createuser newman      
{40} admin@linuxbuilder [~/pg] % ./bin/psql template1 -c '\du'
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 admin     | Superuser, Create role, Create DB, Replication | {}
 caldav    | Superuser, Create role, Create DB              | {}
 newman    |                                                | {}


# Give newman the same access as caldav, validate it.

{41} admin@linuxbuilder [~/pg] % ./bin/psql template1 -c 'grant caldav to newman'
GRANT ROLE
{42} admin@linuxbuilder [~/pg] % ./bin/psql template1 -c '\du'                   
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 admin     | Superuser, Create role, Create DB, Replication | {}
 caldav    | Superuser, Create role, Create DB              | {}
 newman    |                                                | {caldav}

Note that newman is now shown as a member of caldav. This means newman is allowed to do all the things that the caldav role can do. You don’t need to delete the caldav role.

Also, be advised that postgres roles and permissions are not at all related to filesystem permissions or system user accounts; except that if you don’t supply a postgres username when connecting, it will pick your current system user account name as the default.

HTH,
-dre