tips:db:postgres [Galileo Labs]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Postgres ======

===== Install =====

container xenial 16.04 (optional)
<code>
lxc image copy images:ubuntu/xenial/amd64 local: --alias xenial
lxc launch xenial postgres
lxc exec postgres bash
</code>

<code>
sudo apt install postgresql
</code>

eventually open network for remote connections
<file ini /etc/postgresql/9.5/main/postgresql.conf>
listen_addresses = '*'
</file>

===== Basic operations =====

<code>
# list databases
\l

# connect to database
\c giano

# list schema
\dn

# list tables in schema
\dt auth.*

# describe table
\d+ auth.users

# list of installed extensions
\dx

# enable expanded display
\x
</code>

set search path for database
<code>
psql <database>
ALTER DATABASE database_name SET search_path TO schema_name,public;
</code>


drop role gianoauth
<code>
REASSIGN OWNED BY gianoauth TO postgres;
DROP OWNED BY gianoauth;
DROP USER IF EXISTS gianoauth;
</code>

create role gianoauth
<code>
CREATE USER gianoauth WITH
  ENCRYPTED PASSWORD 'xxx'
  LOGIN
  NOSUPERUSER
  INHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION;
</code>

... create database and schemas ...

grant operations on schema auth to role gianoauth
<code>
GRANT ALL PRIVILEGES ON  SCHEMA auth TO gianoauth;
ALTER DEFAULT PRIVILEGES FOR USER postgres IN SCHEMA auth GRANT SELECT ON TABLES TO gianoauth;
ALTER DEFAULT PRIVILEGES FOR USER postgres IN SCHEMA auth GRANT SELECT, USAGE ON SEQUENCES TO gianoauth;
</code>

on database giano set search path on user gianoauth to search first in schema auth
<code>
ALTER USER gianoauth SET search_path TO auth;
ALTER ROLE gianoauth IN DATABASE giano SET search_path TO auth;
</code>

create superuser <admin> with password
<code>
createuser -P -s -e admin
</code>


create user and database for sentry
<code>
CREATE ROLE sentry WITH PASSWORD 'blabla' LOGIN;
CREATE DATABASE sentry ENCODING 'UTF8' OWNER sentry;
</code>
==== developer containers =====

  * pgadmin:
    * user: admin@admin
    * pass: admin@admin

set static IP on containers 
<code>
/var/lib/lxd/networks/lxdbr0/dnsmasq.hosts
├── pgmodeler
└── postgres
</code>

<code>
cat > /var/lib/lxd/networks/lxdbr0/dnsmasq.hosts/pgmodeler <<EOF
00:16:3e:9d:ad:d2,10.8.120.205,pgmodeler
EOF

cat > /var/lib/lxd/networks/lxdbr0/dnsmasq.hosts/postgres <<EOF
00:16:3e:ce:41:53,10.8.120.228,postgres
EOF
</code>

==== pgadmin ====

<code>
apt install wget python3-pip
pip3 install -U pip
pip install https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v4.2/pip/pgadmin4-4.2-py2.py3-none-any.whl

# define ip binding
CONFIG=/usr/local/lib/python3.5/dist-packages/pgadmin4/config.py
sed -i -e "s/DEFAULT_SERVER\s*=.*/DEFAULT_SERVER = '0.0.0.0'/" $CONFIG

# define pgadmin admin user
python3 /usr/local/lib/python3.5/dist-packages/pgadmin4/pgAdmin4.py
</code>

<file ini /etc/systemd/system/pgadmin.service>
[Unit]
Description=pgadmin Service
After=network.target

[Service]
User=root
ExecStart=/usr/bin/python3 /usr/local/lib/python3.5/dist-packages/pgadmin4/pgAdmin4.py

[Install]
WantedBy=multi-user.target
</file>

<code>
systemctl daemon-reload
systemctl start pgadmin
systemctl enable pgadmin
</code>

define admin user in postgresql to use from pgadmin
<code>
su - postgres
createuser --pwprompt --superuser admin
</code>
==== pgmodeler ====

    * [[https://github.com/ksylvan/docker-pgmodeler/blob/master/Dockerfile|reference]]

<code>
PG_VERSION=0.9.1-beta1

# for building, can remove after install
sudo apt install -y make g++ qt5-qmake libxml2-dev libpq-dev pkg-config libqt5svg5-dev

# runtime dependencies
sudo apt install -y qt5-default libqt5svg5 postgresql-server-dev-all
cd /usr/local/src
sudo apt install -y wget
wget https://github.com/pgmodeler/pgmodeler/archive/v${PG_VERSION}.tar.gz
tar xvzf v${PG_VERSION}.tar.gz
cd pgmodeler-${PG_VERSION}/
qmake pgmodeler.pro
make -j 4
make install
sudo mkdir /usr/local/lib/pgmodeler/plugins
cd ..
rm *.tar.gz 
apt clean
</code>

desktop launcher
<code>
mkdir -p ~/.local/share/applications/
cat > ~/.local/share/applications/pgmodeler.desktop <<EOF
#!/usr/bin/env xdg-open

[Desktop Entry]
Type=Application
Terminal=false
Exec=/usr/local/bin/pgmodeler
Name=pgmodeler
EOF

</code>
if in container enable remote connection on host
<file ini /etc/gdm3/custom.conf>
[security]
DisallowTCP=false

[xdmcp]
Enable=true
</file>

run pgmodeler in container
<code>
xhost +
lxc exec pgmodeler -- pgmodeler
</code>