PostgreSQL Single, Local User Setup

Question

I used PostgreSQL briefly, many, many years ago for some very simple personal databases but then moved to SQLite for it's administration. However I now want to use some large datasets and would like to use Postgres. I only want a single user on a local machine to have access to Postgres and the databases on an Ubuntu based pc. Nearly all the tutorials I've read assume that multiple, networked users will want access. From what I remember, I think Postgres is installed with only local access. How do I lockdown Postgres to a single, local user?

1. I need an administrator account. According to https://www.postgresql.org/docs/17/postgres-user.html: 'Pre-packaged versions of PostgreSQL will typically create a suitable user account automatically during package installation.' So the administrator is called postgres and created automatically

2. I create non-administrative regular database user (called MyUser here) to access databases but not administer PostgreSQL.

3. I do not have to be concerned with Preventing Server Spoofing (https://www.postgresql.org/docs/17/preventing-server-spoofing.html) because MyUser will be the only user.

4. From https://www.postgresql.org/docs/current/auth-pg-hba-conf.html, pg_hba.conf should ONLY contain:

# Allow any user on the local system to connect to any database with
# any database user name using Unix-domain sockets (the default for local
# connections).
#
# TYPE DATABASE USER ADDRESS METHOD
 local all MyUser trust

5. It is noted in the same page as 3): 'Remote TCP/IP connections will not be possible unless the server is started with an appropriate value for the listen_addresses configuration parameter, since the default behavior is to listen for TCP/IP connections only on the local loopback address localhost.' So I do NOT need to use 'localhost' in the 'pg_hba.conf' file, according to https://stackoverflow.com/questions/11913897/

6. I saw this article, https://stackoverflow.com/questions/24531420/ (I am in a similar situation as this user except I'm using Linux). Do need give access to port 5432, or is this done at setup?

Is there anythjing else I should consider or is there a tutorial for a single local user? Thank you for any help.

Answer 1

That sounds about right. You need a pg_hba.conf for the superuser (postgres) as well. If you don't trust all local users on the database server machine, it would be better to use peer authentication.

• John: Thanks Laurenz! When you say 'You need a pg_hba.conf for the superuser (postgres)...' I suppose you mean a postgres entry in the pg_hba.conf file, is that right? Eg. local all postgres trust
• Laurenz Albe: Yes, something like that.