PostgreSQL

Last change on 2021-09-16 • Created on 2020-01-20

What is PostgreSQL?

PostgreSQL is an object-oriented database management system. It is based on POSTGRES, which was developed at the University of Berkeley. For an overview of all PostgreSQL features, please visit the official website https://www.postgresql.org/.

Creating a PostgreSQL database

On the main page of the MySQL database manager, you will see your database list; it displays all the databases which currently exist. If you have not yet created any databases, you will only see the maximum number of allowable databases.

How to create a new PostgreSQL database:

  1. Click on "Add".
  2. On the following page, you will see the recommended (random) access details for your database. If you wish, you can change these.
  3. Select a name for the database as well as a name for the corresponding database user.
  4. The password provides access to the whole database and allows you to create, change, or delete any database information, column, or row.
  5. (Optional setting) Require SSL; this prohibits all unencrypted connections to the database. This can have a negative effect on the accessibility of the database.
  6. Click on Save to create the database.
  7. You should then see a message that you successfully created the database. Click on "MySQL-Databases" to return to your database list.

Manage your database(s).

In the overview, you can create a new database or modify, delete, back up, and access existing databases via phpMyAdmin.

If you wish to delete a database, konsoleH will require you to confirm the deletion. Be careful when you confirm it; you cannot un-do the deletion.

Access your database via SSL

You can access your database in an encrypted manner using SSL. We can provide you with a ready-made certificate that you can use to carry out the encryption. Note: The speed of encrypted connections is slower.

The certificate is:

-----BEGIN CERTIFICATE-----
MIIG7jCCBNagAwIBAgIJAOHaB6/9NNBGMA0GCSqGSIb3DQEBBQUAMIGqMQswCQYD
VQQGEwJERTEPMA0GA1UECBMGQmF5ZXJuMRUwEwYDVQQHEwxHdW56ZW5oYXVzZW4x
GjAYBgNVBAoTEUhldHpuZXIgT25saW5lIEFHMRIwEAYDVQQLEwlEYXRhYmFzZXMx
GzAZBgNVBAMTEnNxbC55b3VyLXNlcnZlci5kZTEmMCQGCSqGSIb3DQEJARYXcm9v
dEBzcWwueW91ci1zZXJ2ZXIuZGUwHhcNMTMxMjEwMTMwOTAzWhcNMjMxMjA4MTMw
OTAzWjCBqjELMAkGA1UEBhMCREUxDzANBgNVBAgTBkJheWVybjEVMBMGA1UEBxMM
R3VuemVuaGF1c2VuMRowGAYDVQQKExFIZXR6bmVyIE9ubGluZSBBRzESMBAGA1UE
CxMJRGF0YWJhc2VzMRswGQYDVQQDExJzcWwueW91ci1zZXJ2ZXIuZGUxJjAkBgkq
hkiG9w0BCQEWF3Jvb3RAc3FsLnlvdXItc2VydmVyLmRlMIICIjANBgkqhkiG9w0B
AQEFAAOCAg8AMIICCgKCAgEAq1bzb3Mm4HlRNzf1xI1ssFIvaRjCu/ZpUYhh2Rk8
grexQWJ2ZNZtRqd8UPkIxGgJFuHbbfyf4N1QhC7uI/QqOf5VyPrE0WU75BBIvAhh
MH/xeBxvrrMHF/8yTg5RDS1heJ3Y5UzwSoVHvoNR8fOpcSQ1N3jHkSpZLNhUIky/
hSGdZMOaOUTk1IQbvCrvyIQB8QalCtc5fkg72PlX+ACeGfQGoM5jj+gyKfydQg6s
bPSatIxsKr7KcQxxsWRrV1BfROtxXDbDUwFDS18V7vEGzp7qQlyPLNvTVdH6Xk+U
eVT9I1TfTBq4nsVOjOaHUNC0QtRhTJpjyR/SE9MpRq0+d78LQ21wpYQNnB+ukjOK
TW4Slgr7Pk83cIGS5mJWDI786GiQ7LXFLrg3gyG1FiEATyDrfTrNkZQ9VRD/YSco
1pw84xUBwSwANKCrO+l4wFo8A7cSrYPQBpL/3RuxFap5BavX2MbuymK2MO2/8W0H
p8+wn5qnSt0BcrpeNV92UlBRaZHbnl+N/6oGXrt7lCScuRhIyHCftkEX/DQhwxvj
/lIWMWQTyceqQl9QLI7UkxcXsL7NoalO83CH6BP905mZs+PCL9DPagyMceKOLSwm
mWt05OIAECbqUotCqtLZhHcTB1Nnlqi/4VJiASEStzs7hkhCAZmaTkV0+X7quiQJ
l3MCAwEAAaOCARMwggEPMB0GA1UdDgQWBBQn/eUUbv2YGYmSMkAi5+0iiRSRaDCB
3wYDVR0jBIHXMIHUgBQn/eUUbv2YGYmSMkAi5+0iiRSRaKGBsKSBrTCBqjELMAkG
A1UEBhMCREUxDzANBgNVBAgTBkJheWVybjEVMBMGA1UEBxMMR3VuemVuaGF1c2Vu
MRowGAYDVQQKExFIZXR6bmVyIE9ubGluZSBBRzESMBAGA1UECxMJRGF0YWJhc2Vz
MRswGQYDVQQDExJzcWwueW91ci1zZXJ2ZXIuZGUxJjAkBgkqhkiG9w0BCQEWF3Jv
b3RAc3FsLnlvdXItc2VydmVyLmRlggkA4doHr/000EYwDAYDVR0TBAUwAwEB/zAN
BgkqhkiG9w0BAQUFAAOCAgEAkXyr2ndDBJ+XN7Vy5K9Xc198eqkE2xXPGgsCy9EK
r3bskuC1cAdFWyR/0ZXRUp3yXAMj6azaKtZ5T/ZRyrgsjviLDA52lYeuE7owYmAZ
iA9MNCP9yn/OvfLMG7JQBDbxiZ5JWRnAMbCwWnijGYGiA8sv0i2VmC/nA2d6l7vu
h/30CQfQG/MjPLloAsyyqbSmUqSyjegUAdHXeZhALRuUPQ9Xlp+CU6vqCd/NjTEc
HPFA/rYoL2XKMg+CUX+CGlZiiSOGhELFklaq5SJrjJHATP5cUBYXeHsLZs8fem48
JncyB82AuWdk7jBTKrKE39Nf9hiV+KOLN+smLKVKZ1zOzyLE4x2Yfy7l2MLbDItU
VzdOOTAmxcqBm+iTRwbKO0CaDGsaeUGLMGXSSncSYyXxaXC+Yju2eyk2r9BMNkfB
C+/bzx1MyozSz524JRRkd7TOmCFmWjH+xpWJsBElzdmt3B0d3svcpnZ8XlBuWB2S
V1ZO6Ryr23GeLyio2njVz54xCXUabJ6vt5lyN95Aw2BL8DNxny3lZtl9HqsDW/SK
wpKVWuJoYWnhX1a+SEtYmADX03j0yVXTLI7cnIBcQLINufrIRV4OITP4xbe0CPLO
YL3qKbYlvZjLJBISw1L3ne9xlewzllfPkRwLQPImdhO9wSoe8BWqtpuz13gAXZPG
0Ac=
-----END CERTIFICATE-----

You can save the certificate locally as a text file (for example, as "sqlca.pem"). Once you have done that, you can make an encrypted connection. Unfortunately, however, it is not possible for you to use your own client certificates.

Example commands for encrypted access to PostgreSQL

  • PostgreSQL command line (CLI)

    • psql -h HOSTNAME -U USERNAME -d DBNAME -W --set=sslmode=verify-full --set=sslrootcert=[PFAD]/sqlca.pem
  • PHP (PDO)

    • $pdo = new PDO('pgsql:host=HOSTNAME;dbname=DBNAME;sslmode=verify-full;sslrootcert=[PFAD]/sqlca.pem;', 'USERNAME', 'PASSWORD');

Is there also phpMyAdmin for PostgreSQL?

phpMyAdmin is written for MySQL and does not support PostgreSQL. However, there is a web-based application for PostgreSQL which is very similar to phpMyAdmin. You can download it at http://phppgadmin.sourceforge.net/. Install the tool on your account and follow the operating instructions to manage your PostgreSQL databases.

Table of Contents