PostgreSQL<\/a>\u00a0differs itself with its object-oriented and\/or relational database functionality, such as the complete support for reliable transactions, i.e. Atomicity, Consistency, Isolation, Durability (ACID).
\n<\/span><\/p>\nWhat happens if we want to manage the PostgreSQL databases from a graphical interface and not the command line?
\nWe can use phpPgAdmin which is a is a web-based administration tool for PostgreSQL. PhpPgAdmin is an administration interface for PostgreSQL written in PHP. With PhpPgAdmin, we can administer a remote PostgreSQL Server.<\/p>\n
Now let’s go step by step\u00a0<\/strong>with PostgreSQL + phpPgAdmin running with Nginx on an Ubuntu 14.04 server. PhpPgAdmin can use Apache2 and Php5.<\/p>\nSteps to follow:<\/p>\n
Step 1: Installing PhpPgAdmin <\/strong>
\nInstall PhpPgAdmin using the below commands:<\/p>\n[js]
\nsudo apt-get update
\nsudo apt-get install postgresql postgresql-contrib phppgadmin php5-fpm
\n[\/js]<\/p>\n
By default, the installation process configures the Apache2 server.<\/p>\n
Step 2: Setting up PostgreSQL-9.6.1<\/strong>
\nInstalling PostgreSQL on our Ubuntu 14.04 machine<\/p>\n[js]
\nsudo sh -c ‘echo "deb http:\/\/apt.postgresql.org\/pub\/repos\/apt\/ $(lsb_release -cs)-pgdg main" > \/etc\/apt\/sources.list.d\/pgdg.list’
\nwget –quiet -O – https:\/\/www.postgresql.org\/media\/keys\/ACCC4CF8.asc | sudo apt-key add –
\nsudo apt-get update
\nsudo apt-get install postgresql-9.6
\n[\/js]<\/p>\n
Step 3: Configuring a Read Only user\/role for PostgreSQL<\/strong>
\nCreating a read only user\/role on PostgreSQL for a particular database<\/p>\n[js]
\nCREATE ROLE <postgres_user> WITH LOGIN PASSWORD ‘<password>’ NOSUPERUSER INHERIT NOCREATEDB NOREPLICATION NOCREATEROLE VALID UNTIL ‘infinity’;
\nGRANT CONNECT ON DATABASE <database_name> TO <postgres_user>;
\nGRANT USAGE ON SCHEMA public TO <postgres_user>;
\nGRANT SELECT ON ALL TABLES IN SCHEMA public TO <postgres_user>;
\nGRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO <postgres_user>;
\nALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO <postgres_user>;
\n[\/js]<\/p>\n
Step 4: Configuring Apache<\/strong>
\nNow, editing the following lines in the file \/etc\/apache2\/sites-enabled\/000-default.conf<\/em><\/p>\n[js]
\nServerAdmin localhost
\nDocumentRoot \/usr\/share\/phppgadmin\/index.php
\nServerName localhost
\n[\/js]<\/p>\n
Adding the below line in the file \/etc\/apache2\/apache2.conf :<\/em>
\nInclude \/etc\/apache2\/conf.d\/phppgadmin<\/em><\/p>\nInside \/etc\/apache2\/conf.d\/phppgadmin<\/strong> file, it must contain<\/p>\n[js]
\norder deny,allow
\ndeny from all
\nallow from 127.0.0.0\/255.0.0.0 ::1\/128
\nallow from all
\n[\/js]<\/p>\n
Now, Restarting our Apache Web Server<\/p>\n
[js]sudo service apache2 restart[\/js]<\/p>\n
Step 5: Tweaking some parameters for PhpPgAdmin <\/strong><\/p>\nEditing the below line in the file\/etc\/apache2\/sites-enabled\/000-default.conf :
\nconf[\u2018extra_login_security\u2019] = true;
\n<\/em>to
\n<\/em>$conf[\u2018extra_login_security\u2019] = false;<\/strong><\/em><\/p>\nStep6: Getting started with our PhpPgAdmin GUI for PostgreSQL<\/strong>
\nBrowse on web browser http:\/\/localhost:8080\/phppgadmin<\/strong> ![\"Screenshot](\"\/blog\/wp-ttn-blog\/uploads\/2017\/02\/Screenshot-from-2017-02-20-001848.png\")
<\/p>\nStep7: Adding New Remote PostgreSQL Server (Optional)<\/strong>
\nNow, If we want to add a new server to our PhpPgAdmin then it can be done by adding the below line to the file \/etc\/phppgadmin\/<\/em>config.inc. file:<\/em><\/span><\/span><\/p>\n[js]
\n$conf[‘servers’][1][‘desc’] = ‘New_Postgres_Server’;
\n$conf[‘servers’][1][‘host’] = ‘<ip-address\/hostname>’;
\n$conf[‘servers’][1][‘port’] = 5432;
\n\/\/$conf[‘servers’][1][‘sslmode’] = ‘allow’;
\n$conf[‘servers’][1][‘pg_dump_path’] = ‘\/usr\/bin\/pg_dump’;
\n$conf[‘servers’][1][‘defaultdb’] = ‘template1’;
\n$conf[‘servers’][1][‘pg_dumpall_path’] = ‘\/usr\/bin\/pg_dumpall’;
\n[\/js]<\/p>\n
Now, we can log in using the PostgreSQL user we created in Step 3 into the PhpPgAdmin.
\n![\"phppgadmin-login\"](\"\/blog\/wp-ttn-blog\/uploads\/2017\/02\/phppgadmin-login.png\")
<\/p>\n
You can now easily set up PhpPgAdmin for PostgreSQL.<\/p>\n","protected":false},"excerpt":{"rendered":"
PostgreSQL is a powerful, open source object-relational database system (ORDBMS). The version 8.0 and above also comes with the native Windows compatibility. Compared to other RDBMSs, PostgreSQL\u00a0differs itself with its object-oriented and\/or relational database functionality, such as the complete support for reliable transactions, i.e. Atomicity, Consistency, Isolation, Durability (ACID). What happens if we want to […]<\/p>\n","protected":false},"author":919,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":49},"categories":[2348,1],"tags":[4843,1892,4436,942],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/45065"}],"collection":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/users\/919"}],"replies":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/comments?post=45065"}],"version-history":[{"count":0,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/45065\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/media?parent=45065"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/categories?post=45065"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/tags?post=45065"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"posgresql-and-phppgadmin-in-ubuntu-server-13.10\"](\"\/blog\/wp-ttn-blog\/uploads\/2017\/02\/posgresql-and-phppgadmin-in-ubuntu-server-13.10.png\")
<\/p>\n