330
edits
Changes
Jump to navigation
Jump to search
To work as ==Create the postgres account.researcher user==
Now add 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 usingon the dbase server is created as follows (su to postgres first): createuser -P -s researcher Or createuser --interactive joeresearcher
The 'research' user is created as follows: createuser -P -s research==Fixing some security==
Change ==Editing Users== NOTE: The Postgres .conf files are in: /etc/postgresql/9.35/main/pg_hba.conf To add a user to the database fromthe shell: # "local" is for Unix domain socket connections only local all all peercreateuser username
Restart postgres to reload From the configurationserver, you can list the users of the database using: /etc/init.d/postgresql restart\du
==McNair Center Users==To update the password for any of these users, from the database use: \password username
Use 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'research' account to create dbases and log into them using psqlll need these credentials. username: postgres createdb -O research Testpassword: ask Anne (This is not the password)
The issue here seems to be that we have installed Postgres-XC and an entire database cluster...
→Connect to VNC server
''For help connecting, see'' [[Center_IT#Connecting_to_the_Database_Server]]
[[Category: McNair Admin]]
General configuration options:
* hostname: McNairDBServ
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==
==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:
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
(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:
dropuser username
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.
Todelete a user from the database from the shell: local all all trustdropuser username
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@McNairDBServ:~$ pg_ctl restart -D /var/lib/postgres-xc/DN2 -Z datanode peerto pg_ctl: could not open PID file "/var/lib/local all postgres-xc/DN2/postmaster.pid": Permission denied md5
[https[admin_classification://help.ubuntu.com/community/PostgreSQL:IT Build| ]]
