Difference between revisions of "Database Server Documentation"
imported>Ed |
|||
(40 intermediate revisions by 8 users not shown) | |||
Line 1: | Line 1: | ||
+ | ''For help connecting, see'' [[Center_IT#Connecting_to_the_Database_Server]] | ||
+ | |||
+ | [[Category: McNair Admin]] | ||
+ | |||
General configuration options: | General configuration options: | ||
* hostname: McNairDBServ | * hostname: McNairDBServ | ||
Line 44: | Line 48: | ||
After the system is installed, the installer will prompt for software selection. Choose OpenSSH server, PostgreSQL database, and Samba file server for installation. After it finishes, it will prompt if you want to install the GRUB bootloader onto the master boot record. From my experience with [[Web Server Documentation|setting up the webserver]], choose "No" and on the next screen, install the bootloader to "/dev/sda /dev/sdb /dev/sdc /dev/sdd" and then if all goes well, the installation will finish. Take the CD out and then choose "Continue" and the system will reboot, at which point you should be able to boot into Ubuntu! | After the system is installed, the installer will prompt for software selection. Choose OpenSSH server, PostgreSQL database, and Samba file server for installation. After it finishes, it will prompt if you want to install the GRUB bootloader onto the master boot record. From my experience with [[Web Server Documentation|setting up the webserver]], choose "No" and on the next screen, install the bootloader to "/dev/sda /dev/sdb /dev/sdc /dev/sdd" and then if all goes well, the installation will finish. Take the CD out and then choose "Continue" and the system will reboot, at which point you should be able to boot into Ubuntu! | ||
+ | |||
+ | |||
+ | ==Install Postgres== | ||
+ | |||
+ | apt-get install plperl | ||
+ | |||
+ | ==Install and configure TightVNC and xfce desktop== | ||
+ | ===Installation=== | ||
+ | apt install xfce4 xfce4-goodies tightvncserver | ||
+ | |||
+ | ===Configure VNC server=== | ||
+ | Created a configuration file for VNC startup: | ||
+ | |||
+ | nano ~/.vnc/xstartup | ||
+ | |||
+ | #!/bin/bash | ||
+ | xrdb $HOME/.Xresources | ||
+ | startxfce4 & | ||
+ | |||
+ | *The first command in the file, xrdb $HOME/.Xresources, tells VNC's GUI framework to read the server user's .Xresources file. .Xresources is where a user can make changes to certain settings of the graphical desktop, like terminal colors, cursor themes, and font rendering. | ||
+ | *The second command simply tells the server to launch XFCE, which is where you will find all of the graphical software that you need to comfortably manage your server. | ||
+ | |||
+ | Granted executable privilege: | ||
+ | sudo chmod +x ~/.vnc/xstartup | ||
+ | |||
+ | ===Create a VNC service file=== | ||
+ | First, opened a new service file in /etc/init.d with nano: | ||
+ | sudo nano /etc/init.d/vncserver | ||
+ | |||
+ | The first block of data will be where we declare some common settings that VNC will be referring to a lot, like our username and the display resolution. | ||
+ | |||
+ | #!/bin/bash | ||
+ | PATH="$PATH:/usr/bin/" | ||
+ | export USER="mcnair" | ||
+ | DISPLAY="1" | ||
+ | DEPTH="16" | ||
+ | GEOMETRY="1920x1080" | ||
+ | OPTIONS="-depth ${DEPTH} -geometry ${GEOMETRY} :${DISPLAY} -localhost" | ||
+ | . /lib/lsb/init-functions | ||
+ | |||
+ | Next, we can start inserting the command instructions that will allow us to manage the new service. The following block binds the command needed to start a VNC server, and feedback that it is being started, to the command keyword start. | ||
+ | case "$1" in | ||
+ | vncstart) | ||
+ | log_action_begin_msg "Starting vncserver for user '${USER}' on localhost:${DISPLAY}" | ||
+ | su ${USER} -c "/usr/bin/vncserver ${OPTIONS}" | ||
+ | ;; | ||
+ | |||
+ | The next block creates the command keyword stop, which will immediately kill an existing VNC server instance. | ||
+ | vncstop) | ||
+ | log_action_begin_msg "Stopping vncserver for user '${USER}' on localhost:${DISPLAY}" | ||
+ | su ${USER} -c "/usr/bin/vncserver -kill :${DISPLAY}" | ||
+ | ;; | ||
+ | |||
+ | The final block is for the command keyword restart, which is simply the two previous commands (stop and start) combined into one command. | ||
+ | vncrestart) | ||
+ | $0 vncstop | ||
+ | $0 vncstart | ||
+ | ;; | ||
+ | esac | ||
+ | exit 0 | ||
+ | |||
+ | Made this service script executable: | ||
+ | sudo chmod +x /etc/init.d/vncserver | ||
+ | |||
+ | Now try using the service and command to start a new VNC server instance: | ||
+ | sudo service vncserver vncstart | ||
+ | |||
+ | ===Connect to VNC server=== | ||
+ | First create an SSH connection on your local computer that securely forwards to the localhost connection for VNC. On Linux or OS X (this requires the password for DB Server's non-root user mcnair): | ||
+ | ssh -L 5901:127.0.0.1:5901 -N -f -l mcnair 128.42.44.181 | ||
+ | |||
+ | Now use Remmina/TightVNC Viewer/ETC to connect to the VNC server. | ||
+ | On Remmina, use localhost:5901. No username is fine. Password is: Go-AsK-Ed | ||
+ | |||
+ | [[File:Connection using Remmina.png]] | ||
+ | |||
+ | I am not an expert in Windows Shell and I am not sure how to SSH on Windows. The TightVNC Viewer has a ssh channel option, but I failed to connect to our VNC server on Windows. | ||
==User management== | ==User management== | ||
Line 119: | Line 200: | ||
==Adding accounts for McNair Center Researchers== | ==Adding accounts for McNair Center Researchers== | ||
+ | |||
+ | Note that this section is somewhat redundant. Most McNair Center researchers will log in with the 'researcher' account. This account's creation is described below. | ||
Add a user group: | Add a user group: | ||
Line 143: | Line 226: | ||
In Windows: | In Windows: | ||
Go to my computer -> Map Network Drive | Go to my computer -> Map Network Drive | ||
+ | Connect to: | ||
+ | \\128.42.44.181\bulk | ||
+ | With username: | ||
+ | MCNAIRDB\username | ||
+ | |||
+ | In Mac: | ||
+ | Go to Finder and hit CMD-k | ||
+ | Enter server address: | ||
+ | smb://128.42.44.181/bulk | ||
+ | With username: | ||
+ | MCNAIRDB\username | ||
+ | |||
+ | ==Adding a dbase user and creating a dbase== | ||
+ | |||
+ | Log on to the box as root then: | ||
+ | |||
+ | groupadd -g 112 postgres | ||
+ | useradd -g postgres -s /bin/bash -p xxxx -d /home/postgres -m postgres | ||
+ | passwd postgres | ||
+ | mkdir /var/postgresql/data | ||
+ | chown postgres /var/postgresql/data | ||
+ | |||
+ | Change to postgres and initialize the dbase | ||
+ | su postgres | ||
+ | cd /usr/lib/postgresql/9.5/bin/ | ||
+ | ./initdb -D /var/postgresql/data | ||
+ | |||
+ | /etc/init.d/postgresql stop | ||
+ | |||
+ | Test the right location: | ||
+ | /usr/lib/postgresql/9.5/bin/postgres -D /var/postgresql/data > logile 2>&1 & | ||
+ | |||
+ | As postgres, edit /etc/postgresql/9.5/main/postgresql.conf (see http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server) | ||
+ | |||
+ | data_directory='/var/postgresql/data' | ||
+ | listen_addresses = '*' | ||
+ | port = 5432 | ||
+ | max_connections = 10 | ||
+ | |||
+ | shared_buffers = 100000 MB(~40% of 264 GB) | ||
+ | The recommendation is to use around 25-40% of RAM (we have 256Gb) for high-performance systems like ours. We might need to check that we are running 64bit and that our kernel supports this. | ||
+ | |||
+ | work_mem = 500MB | ||
+ | kranthi: may not do much after some limit, max allowed 2GB | ||
+ | |||
+ | maintenance_work_mem = 2GB | ||
+ | This is how much VACUUM will use - it doesn't make a huge difference | ||
+ | |||
+ | effective_cache_size = 198GB(-3/4 of the current RAM) | ||
+ | |||
+ | Change /etc/postgresql/9.3/main/pg_hba.conf from | ||
+ | # "local" is for Unix domain socket connections only | ||
+ | local all all peer | ||
+ | |||
+ | To: | ||
+ | local all all trust | ||
+ | |||
+ | Restart postgres to reload the configuration: | ||
+ | /etc/init.d/postgresql restart #Not found if properly removed | ||
+ | |||
+ | Delete the postgres-xc account: | ||
+ | userdel -r postgres-xc | ||
+ | vi /etc/group #check the user's group is gone too | ||
+ | |||
+ | ==Create the researcher user== | ||
+ | |||
+ | The password for the researcher account is the 'standard' internal password. | ||
+ | |||
+ | groupadd -g 505 researcher | ||
+ | useradd -g researcher -G smbusers -s /bin/bash -p xxxx -d /home/researcher -m researcher | ||
+ | passwd researcher | ||
+ | smbpasswd -a researcher | ||
+ | |||
+ | Note: if you forget the -G smbusers, the smbusers groups is 1001: | ||
+ | usermod -a -G smbusers researcher | ||
+ | |||
+ | The 'research' user on the dbase server is created as follows (su to postgres first): | ||
+ | createuser -P -s researcher | ||
+ | |||
+ | Or | ||
+ | createuser --interactive researcher | ||
+ | (Answer y or n to whether you want the new role to be superuser, etc.) | ||
+ | |||
+ | The researcher user can create and drop databases with: | ||
+ | createdb DBName | ||
+ | dropdb DBname | ||
+ | |||
+ | ==Notes on individual users== | ||
+ | |||
+ | If the user doesn't have an account on the box, then you'll need to add a database for them (use DBname of 'firstname_data' as a default): | ||
+ | createdb -O username DBname | ||
+ | |||
+ | Otherwise, they can create databases themselves with | ||
+ | createdb DBName | ||
+ | |||
+ | Drop the dbase and the user with: | ||
+ | dropdb DBname | ||
+ | dropuser username | ||
+ | |||
+ | ==Fixing some security== | ||
+ | |||
+ | When the box was built, we created an account called '''mcnair'''. This account isn't in the sudoers file but can sudo su itself. The password on this account was changed on 13th May 2016 to the new admin password. The root account has no password. | ||
+ | |||
+ | ==Editing Users== | ||
+ | |||
+ | NOTE: The Postgres .conf files are in: | ||
+ | /etc/postgresql/9.5/main | ||
+ | |||
+ | |||
+ | To add a user to the database from the shell: | ||
+ | createuser username | ||
+ | |||
+ | To delete a user from the database from the shell: | ||
+ | dropuser username | ||
+ | |||
+ | From the server, you can list the users of the database using: | ||
+ | \du | ||
+ | |||
+ | To update the password for any of these users, from the database use: | ||
+ | \password username | ||
+ | |||
+ | There is a superuser named postgres that has access to the server and the box. If you need a user/password combo for a script that needs access to both, you'll need these credentials. | ||
+ | username: postgres | ||
+ | password: ask Anne (This is not the password) | ||
+ | |||
+ | NOTE: To get this combination to work, we had to change a line in | ||
+ | /etc/postgresql/9.5/main/pg_hba.conf | ||
+ | |||
+ | The line was changed from | ||
+ | local all postgres peer | ||
+ | to | ||
+ | local all postgres md5 | ||
− | |||
− | |||
− | + | [[admin_classification::IT Build| ]] | |
− |
Latest revision as of 16:31, 3 July 2018
For help connecting, see Center_IT#Connecting_to_the_Database_Server
General configuration options:
- hostname: McNairDBServ
- user full name: McNair Center
- username: mcnair
- don't encrypt home directory
- manual partitioning (see below for RAID 10 configuration)
- no automatic updates
- software: OpenSSH server, PostgreSQL database, and Samba file server
Database server drive partition specs:
- RAID 10 the four drives
- 1 MB out of each drive for bootloader
- use all of M.2 drive as swap space
- PCIe express drive format as linux partition (for PostgreSQL data directory)
Contents
- 1 Installing Ubuntu (2/19/2016)
- 2 Install Postgres
- 3 Install and configure TightVNC and xfce desktop
- 4 User management
- 5 Setting up the Samba Server
- 6 Adding accounts for McNair Center Researchers
- 7 Mapping a drive to the Dbase server's bulk directory
- 8 Adding a dbase user and creating a dbase
- 9 Create the researcher user
- 10 Notes on individual users
- 11 Fixing some security
- 12 Editing Users
Installing Ubuntu (2/19/2016)
First try at installation didn't work when the installer got to the partition disks step, because the SSD and M.2 drives weren't being detected for the partitioner. I suspected that there was a driver missing, but I don't see how we'd be able to install a driver without a proper bash shell (the installer does come with a shell, but it's very limited).
Kranthi and I took another look at the PCIe slots on the mobo and it turns out that the two PCIe slots that we had plugged the M.2 drive and SSD into were linked to CPU2, whereas the database server was only configured with one CPU, which was put in CPU1. From pages 2-19 and 2-20 of the mobo datasheeet, we put the M.2 drive in PCIE1 and the SSD in PCIE3, where are both linked to CPU1 and tried the installation process again.
This time, two new drives showed up in the partitioner (/dev/nvme0n1 and /dev/nvme1n1), but they're both listed as unknown (their capacities, however, are displayed as 400.1 GB and 512.1 GB respectively, which technically identifies the first as the SSD and the second as the M.2 drive). Going back to section 2.5 of the mobo datasheet suggests that we should assign each device in the expansion slot an IRQ (from my understanding, an IRQ is a number from 0 to 15 that marks the priority level of the device). To do so, we have to use the BIOS menu, so I aborted the installation and pressed the 'Delete' key during the boot up to get to the BIOS menu.
But through the entire tree of menus, I was only able to find an IRQ for Serial Port 1 and Serial Port 2, under NCT6779D Super IO Configuration. Wikipedia's article on interrupt requests notes that, "PCI Express does not have physical interrupt lines at all, and uses MSI exclusively." So I guess IRQs are no longer a big deal (they "went the way of the dodo," as Ed says). With no other way to label the PCIe drives from the BIOS menu making itself clear, I exited and rebooted the computer to proceed with the installation.
Interesting side note: the mobo datasheet notes that the motherboard comes with utilities to set up RAID arrays. But there doesn't seem to be any benefit of doing it on the motherboard over doing it in the Ubuntu partitioner.
In the partitioner, I deleted the partitions that were automatically generated and started each drive off with a new partition table. Then I partitioned the drives as follows:
- /dev/nvme0n1 (aka the 400 GB Intel SSD)
- entire space (~400 GB) as primary partition. use as ext4 filesystem, mount point: /, bootable flag off.
- /dev/nvme1n1 (aka the 512 GB M.2 drive)
- half (~256 GB) as primary partition. use as swap space (bootable flag off).
- the other half (~256 GB) as primary partition. use as ext4 filesystem, mount: /var/postgresql (choose "Enter it manually" in the mount point selection menu), bootable flag off.
- for each of the four 3.0 TB hard drives (aka /dev/sda, /dev/sdb, /dev/sdc, and /dev/sdd)
- 10 MB primary partition, use as "reserved BIOS boot area" (bootable flag can't be changed, so leave it set to off)
- the rest of the space as a primary partition, use as ext4 filesystem, mount to /bulk (choose "Enter it manually" in the mount point selection menu), bootable flag off
Then choose "configure software RAID" to set up the software RAID device. Confirm the partitions. Then wait for the disks to be partitioned.
Choose "Create MD device" and then "RAID10" to begin. Use 2 active devices and 2 spare devices. For the two active devices, choose /dev/sda2 and /dev/sdb2. For the two spare devices, choose /dev/sdc2 and /dev/sdd2. Confirm the partitions that will be changed. Then wait for the device to be created. When you get back to the software RAID configuration menu, choose "Delete MD device" and then choose the software RAID device that was created (there should only be one, and it should be named something like "md0_raid10"). Note the device name (in this case, it is /dev/md0) and check that the component devices are correct. If they're correct, choose "No" (to not delete the software RAID device). This will take you back to the software RAID configuration menu, where you can choose "Finish" to set up the RAID device, which should now appear at the top of the partitioner.
Choose the 3.0 TB partition in the RAID device and configure it to use as ext4 filesystem and mount point /bulk (choose "Enter it manually" in the mount point selection menu). Then choose "Finish partitioning and write changes to disk" at the bottom of the partitioner menu. Confirm that the partition changes to be made are correct. Then wait for the partitions to be formatted.
After the system is installed, the installer will prompt for software selection. Choose OpenSSH server, PostgreSQL database, and Samba file server for installation. After it finishes, it will prompt if you want to install the GRUB bootloader onto the master boot record. From my experience with setting up the webserver, choose "No" and on the next screen, install the bootloader to "/dev/sda /dev/sdb /dev/sdc /dev/sdd" and then if all goes well, the installation will finish. Take the CD out and then choose "Continue" and the system will reboot, at which point you should be able to boot into Ubuntu!
Install Postgres
apt-get install plperl
Install and configure TightVNC and xfce desktop
Installation
apt install xfce4 xfce4-goodies tightvncserver
Configure VNC server
Created a configuration file for VNC startup:
nano ~/.vnc/xstartup #!/bin/bash xrdb $HOME/.Xresources startxfce4 &
- The first command in the file, xrdb $HOME/.Xresources, tells VNC's GUI framework to read the server user's .Xresources file. .Xresources is where a user can make changes to certain settings of the graphical desktop, like terminal colors, cursor themes, and font rendering.
- The second command simply tells the server to launch XFCE, which is where you will find all of the graphical software that you need to comfortably manage your server.
Granted executable privilege:
sudo chmod +x ~/.vnc/xstartup
Create a VNC service file
First, opened a new service file in /etc/init.d with nano:
sudo nano /etc/init.d/vncserver
The first block of data will be where we declare some common settings that VNC will be referring to a lot, like our username and the display resolution.
#!/bin/bash PATH="$PATH:/usr/bin/" export USER="mcnair" DISPLAY="1" DEPTH="16" GEOMETRY="1920x1080" OPTIONS="-depth ${DEPTH} -geometry ${GEOMETRY} :${DISPLAY} -localhost" . /lib/lsb/init-functions
Next, we can start inserting the command instructions that will allow us to manage the new service. The following block binds the command needed to start a VNC server, and feedback that it is being started, to the command keyword start.
case "$1" in vncstart) log_action_begin_msg "Starting vncserver for user '${USER}' on localhost:${DISPLAY}" su ${USER} -c "/usr/bin/vncserver ${OPTIONS}" ;;
The next block creates the command keyword stop, which will immediately kill an existing VNC server instance.
vncstop) log_action_begin_msg "Stopping vncserver for user '${USER}' on localhost:${DISPLAY}" su ${USER} -c "/usr/bin/vncserver -kill :${DISPLAY}" ;;
The final block is for the command keyword restart, which is simply the two previous commands (stop and start) combined into one command.
vncrestart) $0 vncstop $0 vncstart ;; esac exit 0
Made this service script executable:
sudo chmod +x /etc/init.d/vncserver
Now try using the service and command to start a new VNC server instance:
sudo service vncserver vncstart
Connect to VNC server
First create an SSH connection on your local computer that securely forwards to the localhost connection for VNC. On Linux or OS X (this requires the password for DB Server's non-root user mcnair):
ssh -L 5901:127.0.0.1:5901 -N -f -l mcnair 128.42.44.181
Now use Remmina/TightVNC Viewer/ETC to connect to the VNC server. On Remmina, use localhost:5901. No username is fine. Password is: Go-AsK-Ed
I am not an expert in Windows Shell and I am not sure how to SSH on Windows. The TightVNC Viewer has a ssh channel option, but I failed to connect to our VNC server on Windows.
User management
Adding Root Accounts to the box
Assuming that you have root, you can create user accounts on the box and give them root too. This isn't necessary for regular users - they just need a Postgres user account (see below). To add users to the box, the process is:
First create the users group, checking the last group number (5xx is the next one):
cat /etc/group /usr/sbin/groupadd -g 5xx username
Then add the user (it doesn't matter what you put for -p, it is going to be overwritten):
/usr/sbin/useradd -g username -G root -s /bin/bash -p xxxx -d /home/username -m username where g is the primary group, G is other groups, p sets a password, d declares a home directory and m makes the directory
Change the user's password:
passwd username
And add the user to the sudoers file
echo 'username ALL=(ALL) ALL' >> /etc/sudoers
Deleting a user
To delete a user:
/usr/sbin/userdel -r roger where r removes the home directory
And to remove their group
/usr/sbin/groupdel username
And remove their entry from the sudoers file too if they had root.
Setting up the Samba Server
Based on this Ubuntu page, but this guide is also helpful. Make a backup copy of the smb.conf configuration file before editing it:
$ sudo cp /etc/samba/smb.conf /etc/samba/smb.conf_backup $ sudo vi /etc/samba/smb.conf
Change the workgroup parameter (under the [global] section) to MCNAIRDB. (Note that the security parameter was removed in Samba version 4.0.0).
Then go to the bottom of the configuration file and add a new section, [bulk]:
[bulk] comment = McNair File Server Share path = /bulk browseable = yes guest ok = no read only = no create mask = 0755
Once you're done editing the configuration file, test the file with testparm:
$ testparm /etc/samba/smb.conf
The /bulk directory should already exist from installation, so change its permissions:
$ sudo chmod 770 /bulk $ groupadd smbusers $ chown :smbusers /bulk
You also need to add a user to the Samba database. I used the username "alexjiang" for the commands below, which can be replaced with whatever username needs to be added:
$ smbpasswd -a alexjiang $ usermod -G smbusers alexjiang
Then restart the samba services:
$ sudo restart smbd $ sudo restart nmbd
Then you can try using any Windows Explorer window to check if the file server is set up correctly.
Adding accounts for McNair Center Researchers
Note that this section is somewhat redundant. Most McNair Center researchers will log in with the 'researcher' account. This account's creation is described below.
Add a user group:
cat /etc/group /usr/sbin/groupadd -g 5xx username
Then add the user (it doesn't matter what you put for -p, it is going to be overwritten):
/usr/sbin/useradd -g username -G smbusers -s /bin/bash -p xxxx -d /home/username -m username where g is the primary group, G is other groups, p sets a password, d declares a home directory and m makes the directory passwd username
Add the user's samba password:
$ smbpasswd -a alexjiang
Then restart the samba services:
$ sudo restart smbd $ sudo restart nmbd
Mapping a drive to the Dbase server's bulk directory
In Windows:
Go to my computer -> Map Network Drive Connect to: \\128.42.44.181\bulk With username: MCNAIRDB\username
In Mac:
Go to Finder and hit CMD-k Enter server address: smb://128.42.44.181/bulk With username: MCNAIRDB\username
Adding a dbase user and creating a dbase
Log on to the box as root then:
groupadd -g 112 postgres useradd -g postgres -s /bin/bash -p xxxx -d /home/postgres -m postgres passwd postgres mkdir /var/postgresql/data chown postgres /var/postgresql/data
Change to postgres and initialize the dbase
su postgres cd /usr/lib/postgresql/9.5/bin/ ./initdb -D /var/postgresql/data
/etc/init.d/postgresql stop
Test the right location:
/usr/lib/postgresql/9.5/bin/postgres -D /var/postgresql/data > logile 2>&1 &
As postgres, edit /etc/postgresql/9.5/main/postgresql.conf (see http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server)
data_directory='/var/postgresql/data' listen_addresses = '*' port = 5432 max_connections = 10 shared_buffers = 100000 MB(~40% of 264 GB) The recommendation is to use around 25-40% of RAM (we have 256Gb) for high-performance systems like ours. We might need to check that we are running 64bit and that our kernel supports this. work_mem = 500MB kranthi: may not do much after some limit, max allowed 2GB maintenance_work_mem = 2GB This is how much VACUUM will use - it doesn't make a huge difference effective_cache_size = 198GB(-3/4 of the current RAM)
Change /etc/postgresql/9.3/main/pg_hba.conf from
# "local" is for Unix domain socket connections only local all all peer To: local all all trust
Restart postgres to reload the configuration:
/etc/init.d/postgresql restart #Not found if properly removed
Delete the postgres-xc account:
userdel -r postgres-xc vi /etc/group #check the user's group is gone too
Create the researcher user
The password for the researcher account is the 'standard' internal password.
groupadd -g 505 researcher useradd -g researcher -G smbusers -s /bin/bash -p xxxx -d /home/researcher -m researcher passwd researcher smbpasswd -a researcher
Note: if you forget the -G smbusers, the smbusers groups is 1001:
usermod -a -G smbusers researcher
The 'research' user on the dbase server is created as follows (su to postgres first):
createuser -P -s researcher
Or
createuser --interactive researcher (Answer y or n to whether you want the new role to be superuser, etc.)
The researcher user can create and drop databases with:
createdb DBName dropdb DBname
Notes on individual users
If the user doesn't have an account on the box, then you'll need to add a database for them (use DBname of 'firstname_data' as a default): createdb -O username DBname
Otherwise, they can create databases themselves with createdb DBName
Drop the dbase and the user with:
dropdb DBname dropuser username
Fixing some security
When the box was built, we created an account called mcnair. This account isn't in the sudoers file but can sudo su itself. The password on this account was changed on 13th May 2016 to the new admin password. The root account has no password.
Editing Users
NOTE: The Postgres .conf files are in:
/etc/postgresql/9.5/main
To add a user to the database from the shell:
createuser username
To delete a user from the database from the shell:
dropuser username
From the server, you can list the users of the database using:
\du
To update the password for any of these users, from the database use:
\password username
There is a superuser named postgres that has access to the server and the box. If you need a user/password combo for a script that needs access to both, you'll need these credentials.
username: postgres password: ask Anne (This is not the password)
NOTE: To get this combination to work, we had to change a line in
/etc/postgresql/9.5/main/pg_hba.conf
The line was changed from
local all postgres peer
to
local all postgres md5