Tools Index


1. Install Postgresql

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

1.1. Configure syslog-ng

Configure syslog-ng first, configuration example contains rules for postgresql as is configured in this document.

        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");};

Create /etc/logrotate.d/postgres;

        /var/log/pgsql {
            rotate 10
            create 660 postgres postgres

1.2. Certificates

        $ sudo openssl genrsa -des3 -out /etc/ssl/keys/pg.key 2048
        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) []
        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

2. Configure Server

2.1. Init script

Change /etc/rc.d/postgresql;

        # /etc/rc.d/postgresql: start, stop or restart PostgreSQL server postmaster


        case "$1" in
                sudo -u postgres pg_ctl -D "$PG_DATA" -l /var/log/postgresql "$1"
                echo "usage: $0 start|stop|restart|reload|status"

        # End of file

2.2. Configure postgresql.conf

Edit /srv/pgsql/data/postgresql.conf;

        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:

2.3. Super user password

Create password for the super user postgres, login to postgresql;

        $ sudo -u postgres psql -U postgres

Create password for postgres user;

        postgres=# \password
        Enter new password:
        Enter it again:

Configure pg_hba.conf in the next step to enforce authentication.

2.4. Configure pg_hba.conf

Edit /srv/pgsql/data/pg_hba.conf;

        # 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            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            scram-sha-256
        host    replication     postgres         ::1/128                 scram-sha-256

Restart server to enforce authentication from now on;

        # /etc/rc.d/postgresql start

3. Manage users

3.1. Create user - create role

Create a new user with createuser command;

        $ sudo -u postgres createuser --pwprompt --encrypted \
        --no-createrole --no-createdb user_name
        Enter password for new user:
        Enter it again:

3.2. Remove user - drop role

Deleting user with dropuser command;

        sudo -u postgres dropuser user_name

3.3. Change password

Update password of a user;

        $ sudo -u postgres psql
        postgres=#\password user_name;

This will set password using hash / encryption method selected on postgresql.conf

3.4. List users - roles

        $ sudo -u postgres psql
        postgres=# \dg

4. Manage databases

4.1. Create database

Create a new database named db_name for user_name with createdb command;

        $ sudo -u postgres createdb --template=template0 --encoding=UTF8 \
        --owner=user_name db_name

4.2. Drop database

Deleting database with dropdb command;

        sudo -u postgres dropdb -U postgres db_name

4.3. List databases

List roles then list databases;

        postgres=# \l

4.4. Dump and restore

Dump all databases

        $ pg_dumpall -U postgres | gzip > cluster_dump.gz

Restore dumpfile of all databases;

        $ gzip -c cluster_dump.gz | psql -U postgres 

Restore a database;

        $ psql -U user_name -d db_name -f db_name_dump

5. Psql

Lets check with psql, login with user postgres;

        $ sudo -u postgres psql

First show help;

        postgres=# \?

Connect to a db_name as user_name;

        postgres=# \c db_name user_name

5.2. Create Database

        postgres=# create database db_name owner user_name encoding 'UTF-8' template template0;

5.3. Drop All Tables

This example assumes that all tables, are in public schema. First revoke previously granted privileges from one or more roles;

        postgres=# revoke ALL PRIVILEGES on db_name from user_name;

Drop all tables on public schema and recreate public schema;

        postgres=# \c db_name
        db_name=# drop schema public cascade;
        db_name=# create schema public;
Tools Index

This is part of the LeetIO System Documentation. Copyright (C) 2021 LeetIO Team. See the file Gnu Free Documentation License for copying conditions.