Install postgresql;
$ prt-get depinst postgresql
# mkdir /srv/pgsql/
# touch /var/log/postgresql
# chown postgres:postgres /srv/pgsql /var/log/postgresql
# sudo -u postgres initdb -D /srv/pgsql/data
Change /etc/rc.d/postgresql;
#
# /etc/rc.d/postgresql: start, stop or restart PostgreSQL server postmaster
#
PG_DATA=/srv/pgsql/data
case "$1" in
start|stop|status|restart|reload)
sudo -u postgres pg_ctl -D "$PG_DATA" -l /var/log/postgresql "$1"
;;
*)
echo "usage: $0 start|stop|restart|reload|status"
;;
esac
# End of file
$ sudo openssl genrsa -des3 -out /etc/ssl/keys/pg.key 2048
Password:
Generating RSA private key, 2048 bit long modulus
..............................+++
............+++
e is 65537 (0x10001)
Enter pass phrase for /etc/ssl/keys/pg.key:
Verifying - Enter pass phrase for /etc/ssl/keys/pg.key:
Create ceritificate signing request. For "Common Name" provide domain name or ip address, leave challange password and optional company name blank;
$ sudo openssl req -x509 -in server.req -text -key /etc/ssl/keys/pg.key -out /etc/ssl/certs/pg.crt
Enter pass phrase for /etc/ssl/keys/pg.key:
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:PT
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:machine.example.org
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
$
Having password is a good idea, but requires it every time pg is restarted. To remove;
$ sudo cp /etc/ssl/keys/pg.key /etc/ssl/keys/pg.key.pass
$ sudo openssl rsa \
-in /etc/ssl/keys/pg.key.pass \
-out /etc/ssl/keys/pg.key
Enter pass phrase for /etc/ssl/keys/pg.key.pass:
writing RSA key
$ sudo chown postgres:postgres /etc/ssl/keys/pg.key*
$ sudo chmod 0600 /etc/ssl/keys/pg.key*
$ sudo chmod 644 /etc/ssl/certs/pg.cert
Create password for super user;
# su postgres
$ psql -U postgres
Edit /srv/pgsql/data/postgresql.conf;
# - Security and Authentication -
#authentication_timeout = 1min # 1s-600s
ssl = on # (change requires restart)
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
# (change requires restart)
#ssl_prefer_server_ciphers = on # (change requires restart)
#ssl_ecdh_curve = 'prime256v1' # (change requires restart)
ssl_cert_file = '/etc/ssl/certs/pg.crt' # (change requires restart)
ssl_key_file = '/etc/ssl/keys/pg.key' # (change requires restart)
#ssl_ca_file = '' # (change requires restart)
#ssl_crl_file = '' # (change requires restart)
password_encryption = scram-sha-256
Edit /srv/pgsql/data/pg_hba.conf;
# TYPE DATABASE USER ADDRESS METHOD
local postgres all trust
host postgres all 127.0.0.1/32 trust
host all all 127.0.0.1/32 scram-sha-256
host all all 0.0.0.0/0 reject
Start server and alter postgres password
# /etc/rc.d/postgresql start
postgres=# alter user postgres with password 'new_password';
Configure Syslog-ng, check Michael at otacoo article. Example;
Edit /pgsql/data/postgresql.conf;
log_destination = 'syslog' # Can specify multiple destinations
syslog_facility='LOCAL0'
syslog_ident='postgres'
log_connections = on
log_disconnections = on
log_duration = on
Create /etc/logrotate.d/postgres;
/var/log/pgsql {
weekly
compress
delaycompress
rotate 10
notifempty
create 660 postgres postgres
}
destination postgres { file("/var/log/pgsql"); };
filter f_postgres { facility(local0); };
log { source(s_log); filter(f_postgres); destination(postgres); };
Create a new user with createuser command;
$ sudo -u postgres createuser --pwprompt --encrypted \
--no-createrole --no-createdb flyspray
Enter password for new user:
Enter it again:
Create a new database for new user with createdb command;
$ sudo -u postgres createdb --template=template0 --encoding=UTF8 \
--owner=flyspray db_flyspray
Deleting database with dropdb command;
sudo -u postgres dropdb db_flyspray
Deleting user with dropuser command;
sudo -u postgres dropuser flyspray
Lets check with psql, login with user postgres;
$ sudo -u postgres psql
First show help;
postgres=# \?
List roles then list databases;
postgres=# \dg
postgres=# \l
postgres=# create database db_flyspray_ext owner flyspray encoding 'UTF-8' template template0;
This example assumes that all tables, are in public schema. First connect to database and list tables;
postgres=# revoke SELECT on db_flyspray from flyspray;
postgres=# \c db_flyspray
postgres=# \dt
Drop all tables on public schema and recreate public schema;
db_flyspray=# drop schema public cascade;
db_flyspray=# create schema public;
$ pg_dumpall -U postgres | gzip > cluster_dump.gz
$ gzip -c cluster_dump.gz | psql -U postgres
Tools Index
This is part of the Hive System Documentation. Copyright (C) 2019 Hive Team. See the file Gnu Free Documentation License for copying conditions.