PostgreSQL Configuration:
Connection From Outside The Box: The file /var/lib/pgsql/10/data/postgresql.conf needs to be modified to allow PostgreSQL to listen on all IPs. Make sure the following line is in the file:
listen_addresses = '*'
Users: To allow a user to access the database there are a few things that need to be done first. First is we have to edit /var/lib/pgsql/10/data/pg_hba.conf to allow a user to LDAP authenticate to the database. After the line that looks like
local all postgres peer
Add the following lines to allow the LDAP user to authenticate by host or locally: ` host all username 141.210.186.160/27 ldap ldapurl="ldap://ldap.oakland.edu/dc=oakland,dc=edu?uid?sub" ldaptls=1
local all username ldap ldapurl="ldap://ldap.oakland.edu/dc=oakland,dc=edu?uid?sub" ldaptls=1 `
The first line is to allow host based connections from EA VLAN. The second line is to allow local connections to the database if they are on the box.
Once those have been added, PostgreSQL needs to be restarted. To restart PostgreSQL run the following command:
systemctl restart postgresql-10.service
Once that is done, a ROLE for the LDAP user can be created in the database. As the system postgres user run psql. To create the ROLE for the LDAP user run:
CREATE ROLE username LOGIN INHERIT;
Roles: There is a main role for UTS Admins called uts_ea_admin. All users that need to do administrative functionality on the database need to be granted access to this role. In PostgreSQL, a database can only be owned by ONE user. To give access to the database that is owned by the uts_ea_admin you have to run the following command in psql:
GRANT uts_ea_admin TO username.
Once that is done, the LDAP user can connect to that database.