PostgreSQL

Granting user permissions for a PostgreSQL database

If you need to manage databases and permissions for a PostgreSQL server, you can use a WEB, a Desktop or a command line client. I used pgAdmin for WEB and Desktop access.

A PostgreSQL database have an owner. I got an error setting permissions for other users being connected as different user (with root privileges), if you get too – try to grand permissions as database owner!

A databases can contains multiple schemes, each of them contains tables.

 

Setting connection permissions

First of all you have to grant CONNECTION permissions to the database:

GRANT CONNECT ON DATABASE mydb TO xxx;

Then you have to grant permissions per schemas and tables.

Grant permissions only for existing tables in a schema

  1. Grant USAGE permissions per schema:
    GRANT USAGE ON SCHEMA public TO xxx;
  2. Grant SELECT permissions per table:
    GRANT SELECT ON mytable TO xxx;

Grant default permissions for all tables

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO xxx;
Rocket.Chat

Watchdog and start scripts for Rocket.Chat application

I installed recently Rocket.Chat on a server. The next day I checked it – the service was not running. So I wrote a small script that checks if the application port 3000 is in use and run another script if not.

The scrip is quite simple. The core utility nc is used to check the port availability and set the value for the parameter PORTCHECK depending on the state. Next it uses an if statement to just echo a message if the port is in use, otherwise it run the Rocket.Chat starting script.

Here is the code on that script /opt/rocketwatchdog.sh:
#!/bin/sh
PORTCHECK=$(nc -z -w 3 localhost 3000; echo $?)
DATESTAMP=$(date +”%Y-%m-%d-%T”)
if [ “$PORTCHECK” = 0 ]; then
echo “RocketChat server is already running”
echo “$DATESTAMP App is running.” >> /var/log/watchdog.log
else
echo “RocketChat server is not running. Will try to start it.”
echo “$DATESTAMP RocketChat is not running, restart it.” >> /var/log/watchdog.log
/bin/sh /opt/rocketchat.sh
fi

You should add a cronjob that will check each minute is the server is running. Enter crontab -e to open cron jobs list, add at the end:
* * * * * /bin/sh /opt/rocketwatchdog.sh

Here is the script to start Rocket.Chat /opt/rocketchat.sh:
#!/bin/sh
export PORT=3000
export ROOT_URL=https://chat.mydomain.com/
export MONGO_URL=mongodb://localhost:27017/rocketchat
cd /opt/rocket/
nohup /usr/local/bin/node main.js > /var/log/rocketchat.log 2>&1 &