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.
Asked by John
(133 rep)
Jun 13, 2025, 01:57 AM
Last activity: Jun 13, 2025, 07:52 AM
Last activity: Jun 13, 2025, 07:52 AM