passwd-fdw

Synopsis

The passwd-fdw and group-fdw extensions provide access to the database server's user information. This is traditionally /etc/passwd and /etc/group but could include network resources via LDAP.

Description

These extensions provide access to the database server's user information.

In a well-managed system there's a 1-to-many relation from the password to group tables, via the 'gid' field, and a many-to-many relation from the group to passwd table, via the 'members' field. This may not be the case on a poorly configured system.

There is a third table, 'shell', that contains programs that can be used as user shells. The 'shell' field in the password table should only refer to entries in this table but this is widely violated in practice, esp. for services.

Usage

We must define the passwd and group tables. Note: the group table is named 'grp' to avoid collisions with the SQL word.

FIXME: how to pass parameter to FDW to indicate if it's password or group...

```{sql} CREATE SERVER passwd_svr FOREIGN DATA WRAPPER passwd_fdw;

CREATE FOREIGN TABLE passwd( name text, passwd text, uid int, gid int, gecos text, dir text, shell text ) SERVER passwd_svr;

CREATE FOREIGN TABLE grp( name text, gid int, members text[] ) SERVER passwd_svr; ```

We can now access the system files via standard SQL queries

```{sql} $ SELECT * FROM passwd;

   name        | passwd |  uid  |  gid  |               gecos                |            dir             |       shell       

-------------------+--------+-------+-------+------------------------------------+----------------------------+------------------- root | * | 0 | 0 | root | /root | /bin/bash daemon | * | 1 | 1 | daemon | /usr/sbin | /usr/sbin/nologin bin | * | 2 | 2 | bin | /bin | /usr/sbin/nologin sys | * | 3 | 3 | sys | /dev | /usr/sbin/nologin sync | * | 4 | 65534 | sync | /bin | /bin/sync .... (38 rows)

SELECT * FROM grp;

  name       |  gid  |     members     

-----------------+-------+----------------- root | 0 | daemon | 1 | bin | 2 | sys | 3 | adm | 4 | {bgiles,syslog} ... postgres | 128 | (68 rows) ```

It is possible to specify the minimum UID that will be reported in the password table, typically 1000. This will exclude services.

Security

The password and group tables can reveal sensitive information - valid usernames with their home directories, installed services, etc. This FDW should only be used when necessary and, if possible, the min_uid value should be set to a sufficiently large value to exclude all services.

Support

There is issues tracker? Github? Put this information here.

Author

[Bear Giles bgiles@coyotesong.com]

Copyright and License

Copyright (c) 2015 Bear Giles bgiles@coyotesong.com.