<!DOCTYPE html>
<html dir="ltr" lang="en">
<head>
<meta charset='utf-8'>
<title>Postgresql</title>
</head>
<body>
<a href="index.html">Tools Index</a>
<h1>Postgresql</h1>
<h2 id="install">1. Install Postgresql</h2>
<p>Install postgresql;</p>
<pre>
$ prt-get depinst postgresql
</pre>
<pre>
# mkdir /srv/pgsql/
# touch /var/log/postgresql
# chown postgres:postgres /srv/pgsql /var/log/postgresql
# sudo -u postgres initdb -D /srv/pgsql/data
</pre>
<h3 id="syslog-ng">1.1. Configure syslog-ng</h3>
<p><a href="syslog-ng.html">Configure syslog-ng</a> first, configuration example contains rules for postgresql as is configured in this document.</p>
<pre>
destination d_postgres { file("/var/log/pgsql"); };
filter f_postgres { facility(local0) and program("postgresql)"; };
log { source(s_src); filter(f_postgres); destination(d_postgres);};
filter f_messages { level(info,notice,warn)
and not facility(auth,authpriv,cron,daemon,mail,news,local0); };
filter f_daemon { facility(daemon, local0)
and not filter(f_debug)
and not program("vh_tribu")
and not program("vh_tribu_error");};
</pre>
<p>Create /etc/logrotate.d/postgres;</p>
<pre>
/var/log/pgsql {
weekly
compress
delaycompress
rotate 10
notifempty
create 660 postgres postgres
}
</pre>
<h3 id="gencert">1.2. Certificates</h3>
<pre>
$ 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:
</pre>
<p>Create ceritificate signing request. For "Common Name"
provide domain name or ip address, leave challange password
and optional company name blank;</p>
<pre>
$ 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 []:
$
</pre>
<p>Having password is a good idea, but requires it every
time pg is restarted. To remove;</p>
<pre>
$ 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
</pre>
<pre>
Enter pass phrase for /etc/ssl/keys/pg.key.pass:
writing RSA key
</pre>
<pre>
$ 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
</pre>
<h2 id="server">2. Configure Server</h2>
<h3 id="init">2.1. Init script</h3>
<p>Change <a href="conf/etc/rc.d/postgresql">/etc/rc.d/postgresql</a>;</p>
<pre>
#
# /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
</pre>
<h3 id="config">2.2. Configure postgresql.conf</h3>
<p>Edit <a href="conf/srv/pgsql/data/postgresql.conf">/srv/pgsql/data/postgresql.conf</a>;</p>
<pre>
ssl = on # (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)
password_encryption = scram-sha-256
jit = off
log_destination = 'syslog'
syslog_facility = 'LOCAL0'
log_connections = on
log_disconnections = on
log_duration = on
log_hostname = on
log_line_prefix = 'd=$d u=% %m [%p] ' # special values:
</pre>
<h3 id="pass">2.3. Super user password</h3>
<p>Create password for the super user postgres, login to postgresql;</p>
<pre>
$ sudo -u postgres psql -U postgres
</pre>
<p>Create password for postgres user;</p>
<pre>
postgres=# \password
Enter new password:
Enter it again:
postgres=#
</pre>
<p>Configure pg_hba.conf in the next step to enforce authentication.</p>
<h3 id="pg_hba">2.4. Configure pg_hba.conf</h3>
<p>Edit
<a href="conf/srv/pgsql/data/pg_hba.conf">/srv/pgsql/data/pg_hba.conf</a>;
</p>
<pre>
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all postgres scram-sha-256
#local all postgres trust
# IPv4 local connections:
host all postgres 127.0.0.1/32 scram-sha-256
# IPv6 local connections:
host all postgres ::1/128 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication postgres scram-sha-256
host replication postgres 127.0.0.1/32 scram-sha-256
host replication postgres ::1/128 scram-sha-256
</pre>
<p>Restart server to enforce authentication from now on;</p>
<pre>
# /etc/rc.d/postgresql start
</pre>
<h2 id="users">3. Manage users</h2>
<h3 id="createuser">3.1. Create user - create role</h3>
<p>Create a new user with createuser command;</p>
<pre>
$ sudo -u postgres createuser --pwprompt --encrypted \
--no-createrole --no-createdb user_name
Enter password for new user:
Enter it again:
</pre>
<h3 id="dropuser">3.2. Remove user - drop role</h3>
<p>Deleting user with dropuser command;</p>
<pre>
sudo -u postgres dropuser user_name
</pre>
<h3 id="userpass">3.3. Change password</h3>
<p>Update password of a user;</p>
<pre>
$ sudo -u postgres psql
</pre>
<pre>
postgres=#\password user_name;
</pre>
<p>This will set password using hash / encryption method selected on postgresql.conf</p>
<h3 id="listuser">3.4. List users - roles</h3>
<pre>
$ sudo -u postgres psql
</pre>
<pre>
postgres=# \dg
</pre>
<h2 id="databases">4. Manage databases</h2>
<h3 id="createdb">4.1. Create database</h3>
<p>Create a new database named db_name for user_name with createdb command;</p>
<pre>
$ sudo -u postgres createdb --template=template0 --encoding=UTF8 \
--owner=user_name db_name
</pre>
<h3 id="dropdb">4.2. Drop database</h3>
<p>Deleting database with dropdb command;</p>
<pre>
sudo -u postgres dropdb -U postgres db_name
</pre>
<h3 id="listdb">4.3. List databases</h3>
<p>List roles then list databases;</p>
<pre>
postgres=# \l
</pre>
<h3 id="backup">4.4. Dump and restore</h3>
<p>Dump all databases</p>
<pre>
$ pg_dumpall -U postgres | gzip > cluster_dump.gz
</pre>
<p>Restore dumpfile of all databases;</p>
<pre>
$ gzip -c cluster_dump.gz | psql -U postgres
</pre>
<p>Restore a database;</p>
<pre>
$ psql -U user_name -d db_name -f db_name_dump
</pre>
<h2 id="psql">5. Psql</h2>
<p>Lets check with psql, login with user postgres;</p>
<pre>
$ sudo -u postgres psql
</pre>
<p>First show help;</p>
<pre>
postgres=# \?
</pre>
<p>Connect to a db_name as user_name;</p>
<pre>
postgres=# \c db_name user_name
</pre>
<h3 id="psqldb">5.2. Create Database</h3>
<pre>
postgres=# create database db_name owner user_name encoding 'UTF-8' template template0;
</pre>
<h3 id="droptables">5.3. Drop All Tables</h3>
<p>This example assumes that all tables,
are in public schema. First revoke previously granted privileges from one or more roles;</p>
<pre>
postgres=# revoke ALL PRIVILEGES on db_name from user_name;
</pre>
<p>Drop all tables on public schema and recreate public schema;</p>
<pre>
postgres=# \c db_name
db_name=# drop schema public cascade;
db_name=# create schema public;
</pre>
<a href="index.html">Tools Index</a>
<p>
This is part of the Tribu System Documentation.
Copyright (C) 2020
Tribu Team.
See the file <a href="../fdl-1.3-standalone.html">Gnu Free Documentation License</a>
for copying conditions.</p>
</body>
</html>