By Alain Francois, Alibaba Cloud Community Blog author
You can need to manage a database in other to store some information regarding your operations. There are many object-relational database system and one which is famous and powerful among them is PostgreSql. It's a free and open source relational database management system which is mostly in production environment. It can use and extend the SQL language to handle workloads from small to large applications. Most of the Linux professional open source tools work with command line such as postgresql which can give you quite difficulties to manage all your databases if you are a novice in the area.
To solve this issue, there is another open source tool called PhpPgAdmin which can help you to graphically manage your databases. It's a web interface helping you to manage your PostgreSql database in an easy way. If offers many features like the possibility to backup and restore your databases. This can save you some times in your work.
In this article, we will see how you can install PostgreSql and PhpPgAdmin on Ubuntu 18.04 to handle your database through the web interface.
Postgresql is present in the default repositories of Ubuntu 18.04 and for PhpPgAdmin, it works with apache and php which means that if it's not already installed, it will download and install automatically. the default version of php that it will install is php 7.2
When writing this article, the default version of postgresql present by default in the repository is postgresql 10 which means that if you want another version like 9 or 11, you will need to follow the steps of this postresql wiki page recommended by the official website.
By default, postgresql creates a basic user postgres and database administrator with the same name and uses many authentications methods of which the most used are peer and md5. The peer authentication is used for database administrators and is only supported for local connections because it tries to obtain the client's operating system user name from the kernel to use it as the allowed database user name while the md5 authentication works the same but is used across the connection. That one can be particularly helpful on local connections when you have a multi-users machine.
Postrgresql also use sthe system of role to handle efficiently the permissions for the database access which can be a group of DB users, or simply a DB user, etc. The roles allow to assign privileges to database objects they own, enabling access and actions to those objects and have the ability to grant membership to another role.
In our case, we will use the default version of postgresql which means the 10 version so we will not follow the wiki page but you can do it if you want another version depending on what you need.
Now open your terminal and run the installation process
$ sudo apt install postgresql Reading package lists... Done Building dependency tree Reading state information... Done The following additional packages will be installed: libpq5 libsensors4 postgresql-10 postgresql-client-10 postgresql-client-common postgresql-common ssl-cert sysstat Suggested packages: lm-sensors postgresql-doc locales-all postgresql-doc-10 libjson-perl openssl-blacklist isag The following NEW packages will be installed: libpq5 libsensors4 postgresql postgresql-10 postgresql-client-10 postgresql-client-common postgresql-common ssl-cert sysstat 0 upgraded, 9 newly installed, 0 to remove and 28 not upgraded. Need to get 5331 kB of archives. After this operation, 21.0 MB of additional disk space will be used. Do you want to continue? [Y/n] y ... ... syncing data to disk ... ok Success. You can now start the database server using: /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile start Ver Cluster Port Status Owner Data directory Log file 10 main 5432 down postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log update-alternatives: using /usr/share/postgresql/10/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode Setting up postgresql (10+190) ... Processing triggers for ureadahead (0.100.0-20) ... Processing triggers for systemd (237-3ubuntu10.15) ... Processing triggers for libc-bin (2.27-3ubuntu1) ...
As you have seen, it's the version 10 which is install by default. Now check if the postgresql is running
$ systemctl status postgresql ● postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled) Active: active (exited) since Wed 2019-04-10 16:43:59 UTC; 3min 28s ago Main PID: 15875 (code=exited, status=0/SUCCESS) Tasks: 0 (limit: 1152) CGroup: /system.slice/postgresql.service
Now you need to configure postgresql because by default it works only for localhost authentication. You will need to edit the default configuration file
/etc/postgresql/10/main/pg_hba.conf, so open the file with your command line editor and edit the lines of the local IPv4 local connections. So we will comment the line with the localhost address (or you can simply delete it) and add the network address of the clients machines that you will use to connect to the server.
$sudo vim /etc/postgresql/10/main/pg_hba.conf # Database administrative login by Unix domain socket local all postgres peer # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only #local all all peer local all all md5 # IPv4 local connections: #host all all 127.0.0.1/32 md5 host all all 10.10.1.0/24 md5 # IPv6 local connections: host all all ::1/128 md5 # Allow replication connections from localhost, by a user with the # replication privilege. local replication all peer host replication all 127.0.0.1/32 md5 host replication all ::1/128 md5
Notice that if you want to access your database from any computer, instead of specifying the remote network address
10.10.1.0/24 of the clients machines, you can use the default route
Now we need to configure on which interfaces PostgreSql should listen and accept the connections on, by editing the value of the
listen_addresses configuration parameter because the default behavior is to listen for TCP/IP connections only on the local loopback address. There are some others parameters that you can edit to increase the security as the default port or the maximum connections, so you can try it.
$ sudo vim /etc/postgresql/10/main/postgresql.conf # - Connection Settings - #listen_addresses = 'localhost' # what IP address(es) to listen on; listen_addresses = '10.10.1.84' # LISTEN ON THE SPECIFIED IP ADDRESS; # comma-separated list of addresses; # defaults to 'localhost'; use '*' for all # (change requires restart) port = 5432 # (change requires restart) max_connections = 100 # (change requires restart) #superuser_reserved_connections = 3 # (change requires restart) unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories # (change requires restart) #unix_socket_group = '' # (change requires restart) #unix_socket_permissions = 0777 # begin with 0 to use octal notation
In our case, we ask to accept the connections on a specific interface of the server. If you want you server to accept the connections on any interface, instead of the ip address you should use
Now we need to restart postgresql service and check if the service is listening on the default port 5432 with the
$ sudo systemctl restart postgresql $ sudo netstat -antup | grep 5432 tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 24194/postgres tcp6 0 0 :::5432 :::* LISTEN 24194/postgres
You can see that our postgres is listening on the default port 5432 on all the interfaces.
We need to configure the postgres user by changing the default password. Remember that postgresql creates a Linux and database admin user called posgres. It means that we have to change the passwords of the two, so directly on the system and on the psql shell command.
First let's change the password of the Linux postgres user
$ sudo passwd postgres Enter new UNIX password: Retype new UNIX password: passwd: password updated successfully
Now change the password of the database administrator postgres.
$ sudo su -l postgres postgres@ubuntu-bionic:~$ psql psql (10.7 (Ubuntu 10.7-0ubuntu0.18.04.1)) Type "help" for help. postgres=# \password Enter new password: Enter it again: postgres=# \q postgres@ubuntu-bionic:~$ exit
Now you have changed the password. You can continue with the installation of PhpPgAdmin
PhpPgAdmin is present by default in the repositories of Ubuntu 18.04. So we can install it normally.
Now that the installation is finished, we need to complete some configurations.
We need to edit the phppgadmin from apache to allow connections from remote systems. So we will change the parameters for localhost connections
sudo vim /etc/apache2/conf-available/phppgadmin.conf Alias /phppgadmin /usr/share/phppgadmin <Directory /usr/share/phppgadmin> <IfModule mod_dir.c> DirectoryIndex index.php </IfModule> AllowOverride None # Only allow connections from localhost: #Require local Require all granted <IfModule mod_php.c> php_flag magic_quotes_gpc Off php_flag track_vars On #php_value include_path . </IfModule> <IfModule !mod_php.c> <IfModule mod_actions.c> <IfModule mod_cgi.c> AddType application/x-httpd-php .php Action application/x-httpd-php /cgi-bin/php </IfModule> <IfModule mod_cgid.c> AddType application/x-httpd-php .php Action application/x-httpd-php /cgi-bin/php </IfModule> </IfModule> </IfModule> </Directory>
We will edit the file
/etc/phppgadmin/config.inc.php and change some parameters. We will indicate the ip address of the postgresql server, also allow the defaults usernames to log in and just hide the other users databases from the database list but if a user uses a sql query, he will see the other databases.
By default, the default address of Postgresql is localhost but you need to change it and put the ip address of the server otherwise you can face some troubles to connect to the database and you will be not probably able to remotely connect from the remote network addresses of the clients machines that you have specified in the
$ sudo vim /etc/phppgadmin/config.inc.php // Hostname or IP address for server. Use '' for UNIX domain socket. // use 'localhost' for TCP/IP connection on this computer //$conf['servers']['host'] = 'localhost'; $conf['servers']['host'] = '10.10.1.84';
Postgresql doesn't allow some defaults users such as posgres, root, or administrators to login in the database even the users with no password. So we need at least , in our case to allow the default users to log in by editing the same file as below.
// If extra login security is true, then logins via phpPgAdmin with no // password or certain usernames (pgsql, postgres, root, administrator) // will be denied. Only set this false once you have read the FAQ and // understand how to change PostgreSQL's pg_hba.conf to enable // passworded local connections. //$conf['extra_login_security'] = true; $conf['extra_login_security'] = false; // Only show owned databases? // Note: This will simply hide other databases in the list - this does // not in any way prevent your users from seeing other database by // other means. (e.g. Run 'SELECT * FROM pg_database' in the SQL area.) //$conf['owned_only'] = false; $conf['owned_only'] = true;
Now that we have finaliezd the configuration of phppgadmin, you can try to access it throught a browser of one remote machine which is in the network specified in the pg_hba.conf file
Open your browser and put the ip add of the server. Then choose your language and go to the server
There you can see all your database servers and the ip addresses. Click on your postgresql server
Now use your postgres database username and password, not the password of the postgres system user.
Now you can see that you are connected to the database
Now you have all the details that you can need to configure a postgresql server and it's possible for you to increase the security by configuring your firewall with good communication rules. You can also decide to create a new database user instead of using the default one which is postgres.
Normally when you have a postgreSql server and you want to make it accessible from everywhere, it means that it must be opened on Internet. So, it may listen on a public IP address to accept connections from any origin but it can also cause a security issue so you must be careful on the configuration that you will do.
Alibaba Clouder - October 1, 2018
Arslan ud Din Shafiq - May 18, 2020
Alibaba Clouder - June 3, 2020
francisndungu - February 24, 2020
Hiteshjethva - October 31, 2019
Hiteshjethva - March 2, 2020
An online MPP warehousing service based on the Greenplum Database open source programLearn More
An on-demand database hosting service for MySQL, SQL Server and PostgreSQL with automated monitoring, backup and disaster recovery capabilitiesLearn More
Supports data migration and data synchronization between data engines, such as relational database, NoSQL and OLAPLearn More
More Posts by Alibaba Clouder