Category: Postgres

Posted on

How To Install pgAdmin 4 on CentOS 7 / RHEL 7 & Fedora 29 / Fedora 28

Install pgAdmin 4

Once you have PostgreSQL repository configured on your system, run the following command to install pgAdmin 4.

### RHEL / CentOS ###

yum -y install pgadmin4

### Fedora ###

dnf -y install pgadmin4

Configure pgAdmin 4

We would need to do a few configuration changes prior to accessing the pgAdmin 4.

Copy the pgAdmin 4 sample configuration.

cp /etc/httpd/conf.d/pgadmin4.conf.sample /etc/httpd/conf.d/pgadmin4.conf

Create a pgAdmin log and data directories.

mkdir /var/log/pgadmin4/
mkdir /var/lib/pgadmin4/

Create/Edit config_local.py file.

vim /usr/lib/python2.7/site-packages/pgadmin4-web/config_local.py

Add the following settings. A

LOG_FILE = '/var/log/pgadmin4/pgadmin4.log'
SQLITE_PATH = '/var/lib/pgadmin4/pgadmin4.db'
SESSION_DB_PATH = '/var/lib/pgadmin4/sessions'
STORAGE_DIR = '/var/lib/pgadmin4/storage'

after the existing one:

import os
DATA_DIR = os.path.realpath(os.path.expanduser(u'~/.pgadmin/'))

Change permissions of directories so that Apache can write data into it. B

chown -R apache:apache /var/lib/pgadmin4/*
chown -R apache:apache /var/log/pgadmin4/*

Run the following command to create a user account for the pgAdmin 4 web interface. C

python /usr/lib/python2.7/site-packages/pgadmin4-web/setup.py

Output:

NOTE: Configuring authentication for SERVER mode.

Enter the email address and password to use for the initial pgAdmin user account:

Email address: admin@itzgeek.local
Password: xxxxxxxxx
Retype password: xxxxxxxxx
pgAdmin 4 - Application Initialisation
======================================

Restart the Apache web service.

systemctl restart httpd

My https configuration file for Apache

For CentOS 7:

<VirtualHost *:80>
        ServerName pg.saic.it
        Redirect permanent / https://pg.saic.it/pgadmin4/
</VirtualHost>
<VirtualHost *:443>
        ServerName pg.saic.it
        LoadModule wsgi_module modules/mod_wsgi.so
        SSLEngine on
        SSLCertificateFile /etc/ssl/certs/pg.saic.it.crt
        SSLCertificateKeyFile /etc/ssl/certs/pg.saic.it.key
        SSLCACertificateFile /etc/ssl/certs/saic.sslforfree.ca
        WSGIDaemonProcess pgadmin processes=1 threads=25
        WSGIScriptAlias /pgadmin4 /usr/lib/python2.7/site-packages/pgadmin4-web/pgAdmin4.wsgi

        <Directory /usr/lib/python2.7/site-packages/pgadmin4-web/>
                WSGIProcessGroup pgadmin
                WSGIApplicationGroup %{GLOBAL}
                <IfModule mod_authz_core.c>
                        # Apache 2.4
                        Require all granted
                </IfModule>
                <IfModule !mod_authz_core.c>
                        # Apache 2.2
                        Order Deny,Allow
                        Deny from All
                        Allow from 127.0.0.1
                        Allow from ::1
                </IfModule>
        </Directory>
</VirtualHost>

if you have this error in your Apache log file : Fatal Python error: PyEval_AcquireThread: NULL new thread state

is because mod_python have to removed from the modules

After the installation when you do an update the steps are: A, B, C

 

Posted on

How to set Postres Read Only Role for a Read only user script

connect by command console to the database:
1) /usr/pgsql-10/bin/psql -U postgres
select the current db for which you want to create the read only user:
2) \c <database>
3) create the role
4) grant the permission after the connection to the right db

Reference taken from this blog:

Script to Create Read-Only user:

CREATE ROLE Read_Only_User WITH LOGIN PASSWORD 'Test1234' 
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';

Assign permission to this read only user:

GRANT CONNECT ON DATABASE YourDatabaseName TO Read_Only_User;
GRANT USAGE ON SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO Read_Only_User;
Posted on

Drop active postgres connection

down vote
accepted
This will drop existing connections except for yours:

Query pg_stat_activity and get the pid values you want to kill, then issue SELECT pg_terminate_backend(pid int) to them.

PostgreSQL 9.2 and above:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = ‘TARGET_DB’ — ← change this to your DB
AND pid <> pg_backend_pid();

PostgreSQL 9.1 and below:

SELECT pg_terminate_backend(pg_stat_activity.procpid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = ‘TARGET_DB’ — ← change this to your DB
AND procpid <> pg_backend_pid();

Once you disconnect everyone you will have to disconnect and issue the DROP DATABASE command from a connection from another database aka not the one your trying to drop.