Modeling Unix Group Membership

There are two reasons I have found it advantageous to synchronize local user accounts to an SQL lookup table:

1. Databases frequently keep records based on the user who ran a task; a users table allows you to write queries that include or exclude certain groups
2. Applications need to filter records using access controls that are consistent with file system permissions

Arrays provide a very natural means of associating group membership in a database. Since arrays are ordered, we can assign special meaning to the first member

CREATE TABLE users (
  username varchar(32) PRIMARY KEY,
  unix_groups varchar(32)[]  -- first element is the primary group
);

Collecting Group Membership

Running a periodic task to scrape user entries is a little slopy since the result depends on the host it's run on, but it will run anywhere and doesn't need special permissions. Here is one way to do it:

Create map of users and groups for every user belonging to staff

#/usr/local/bin/python

import pwd
import grp

all_users = [u.pw_name for u in pwd.getpwall()]

# Map of users and their primary group
unix_groups = {}
for user in all_users:
    group_id = pwd.getpwnam(user).pw_gid
    primary_group = grp.getgrgid(group_id).gr_name
    unix_groups[user] = [primary_group]

# Add additional group membership
for group in grp.getgrall():
    for member in group.gr_mem:
        unix_groups[member].append(group.gr_name)

Use the user map to insert/update existing users and disable all others

import psycopg2

conn = psycopg2.connect(PG_URL)
conn.autocommit = True
cur = conn.cursor()

all_staff = grp.getgrnam('staff').gr_mem

# Add or update active users
sql = """
  INSERT INTO users (username, unix_groups)
  VALUES (%s, %s)
  ON CONFLICT (username) DO UPDATE SET unix_groups=%s;
"""

for user in all_staff:
    groups = unix_groups[user]
    cur.execute(sql, [user, groups, groups])

# Blank group membership for all others
sql = """
  UPDATE users
  SET unix_groups=NULL
  WHERE username NOT IN %s;
"""
cur.execute(sql, [tuple(all_staff)])

This script narrows the focus to users who are members of the staff group, but you could choose to ignore some users using other criteria. If referential integrity is not a concern then you may delete from the users table instead of using a field to designate the user as inactive.

Queries

To check membership or select all users belonging to one or more groups. The following PostgreSQL query selects for users who belong to the operator and dialer groups

SELECT username, unix_groups
FROM users
WHERE unix_groups @> ARRAY['operator', 'dialer']::varchar[];

To fetch the primary group for all active users

SELECT username, unix_groups[1] AS groupname
FROM users
WHERE unix_groups IS NOT NULL;