3900 - mu - Soul of a tiny new machine. More thorough tests → More comprehensible and rewrite-friendly software → More resilient society.

about summary refs log tree commit diff stats

diff options

author	Kartik K. Agaram <vc@akkartik.com>	2017-06-02 00:44:32 -0700
committer	Kartik K. Agaram <vc@akkartik.com>	2017-06-02 00:44:32 -0700
commit	bfa3f5ba3930fb3919d94e97929a2b962d104e10 (patch)
tree	6444a12d4b644427c644317d578519a381e0509a /html/edit
parent	71190ea5df7a77364e1efdbbf02d3d99afe813e3 (diff)
download	mu-bfa3f5ba3930fb3919d94e97929a2b962d104e10.tar.gz

3900

Diffstat (limited to 'html/edit')

0 files changed, 0 insertions, 0 deletions

13'>113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359

<!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 LeetIO System Documentation.
        Copyright (C) 2021
        LeetIO Team.
        See the file <a href="../fdl-1.3-standalone.html">Gnu Free Documentation License</a>
        for copying conditions.</p>
    </body>
</html>


context:
space:
mode: