So I received help on this issue from a colleague who is an experienced programmer.
In order to add a password to the page:
sudo nano /etc/apache2/conf-enabled/phppgadmin.conf
Then, modify the .conf file to include the following text within the block (I placed it underneath the 'allow from all' line that I uncommented previously):
AuthUserFile /usr/share/phppgadmin/.htpasswd
AuthName "Restricted Area"
AuthType Basic
require valid-user
Save this file and exit.
Change directory to phppgadmin:
cd /usr/share/phppgadmin
Create password file (change 'your username' and get rid of triangle brackets):
sudo htpasswd -c .htpasswd <your username>
Enter password twice when prompted.
Restart the apache service and the page will now be password protected.
In order to add a superuser to access phppgadmin(again, (change 'your username' and get rid of triangle brackets):
sudo su - postgres
createuser -P -s <your username>
Again, enter password twice when prompted. This will now allow you to login to to phppgadmin.
Erwin's answer is also helpful too, it just seems as if he takes a different approach to solving the issue. Although I did not use Erwin's method, it should accomplish the same thing. However, I did not need to adjust listen_address in postgresql.conf in order to make it work.
What you see typically happens when the non-privileged user doesn't have schema level privileges. In this case, they cannot list the objects inside the given schema - with the command you tried. See the below example:
-- as a superuser, for example
CREATE DATABASE test;
\c test
CREATE TABLE public.blah (id integer);
REVOKE ALL ON SCHEMA public FROM PUBLIC;
CREATE USER u WITH PASSWORD 'u'; -- don't do this in production
-- connect as u
\c test u
\dt
No relations found.
-- but:
\dt public.*
List of relations
Schema │ Name │ Type │ Owner
────────┼──────┼───────┼────────
public │ blah │ table │ dezso
See what the documentation says:
USAGE
For schemas, allows access to objects contained in the specified
schema (assuming that the objects' own privilege requirements are also
met). Essentially this allows the grantee to "look up" objects within
the schema. Without this permission, it is still possible to see the
object names, e.g. by querying the system tables. [...]
\dt does the latter, namely queries pg_class and pg_namespace to find matching objects. Apparently, when the schema name is not specified, the system wants to hide objects from view.
Best Answer
The most secure way is to revoke the
selectprivilege on the columns.Then the user will never be able to see the contents no matter what. You can't really hide the body of a function, and you can't hide the table structure from a user (e.g. by querying pg_attribute).
But the information that there is a column named "password" doesn't reveal anything the user didn't know beforehand, so I don't consider this a problem.
A password stored in the database should always be encrypted anyway and with a one way encryption, so it can't be decrypted (e.g. SHA-256).
So even in the extremely unlikely case that the user finds a way to work around the privilege system in Postgres, he would only see the encrypted value and couldn't do anything with it.