It is always a bit of a surprise that many people do not know peer authentication in PostgreSQL. You might ask why that is important as initdb creates a default pg_hba.conf which does not allow any connections from outside the PostgreSQL server. While that is true there is another important point to consider.
Let’s assume you executed initdb without any options like this:
[email protected]:/home/postgres/ [pgdev] mkdir /var/tmp/test [email protected]:/home/postgres/ [pgdev] initdb -D /var/tmp/test The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale "en_US.UTF-8". The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english". Data page checksums are disabled. fixing permissions on existing directory /var/tmp/test ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... posix selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting default time zone ... Europe/Zurich creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok initdb: warning: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. Success. You can now start the database server using: pg_ctl -D /var/tmp/test -l logfile start
Did you ever notice the warning at the end of the output?
initdb: warning: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
You might think that this is not important as only the DBAs will have access to the operating system user postgres (or whatever user you used when you executed initdb). Although this might be true in your case, the server eventually might have other local users. Before creating a new user lets start the instance:
[email protected]:/home/postgres/ [pgdev] export PGPORT=9999 [email protected]:/home/postgres/ [pgdev] pg_ctl -D /var/tmp/test/ start -l /dev/null waiting for server to start.... done server started
You really need to be aware of is this:
[email protected]:/home/postgres/ [pgdev] sudo useradd test [email protected]:/home/postgres/ [pgdev] sudo su - test [[email protected] ~]$ /u01/app/postgres/product/DEV/db_1/bin/psql -p 9999 -U postgres postgres psql (13devel) Type "help" for help. postgres=#
… and you are in as the superuser! So any local user can connect as the superuser by default. What you might want to do is this:
[email protected]:/home/postgres/ [pgdev] sudo chmod o-rwx /u01/app/postgres/product [email protected]:/home/postgres/ [pgdev] sudo su - test Last login: Tue Oct 22 21:19:58 CEST 2019 on pts/0 [[email protected] ~]$ /u01/app/postgres/product/DEV/db_1/bin/psql -p 9999 -U postgres postgres -bash: /u01/app/postgres/product/DEV/db_1/bin/psql: Permission denied
This prevents all other users on the system from executing the psql binary. If you can guarantee that nobody installs psql in another way on the system that might be sufficient. As soon as psql is available somewhere on the system you’re lost again:
[email protected]:/home/postgres/ [pgdev] sudo dnf provides psql Last metadata expiration check: 0:14:53 ago on Tue 22 Oct 2019 09:09:23 PM CEST. postgresql-10.6-1.module_el8.0.0+15+f57f353b.x86_64 : PostgreSQL client programs Repo : AppStream Matched from: Filename : /usr/bin/psql [email protected]:/home/postgres/ [pgdev] sudo dnf install -y postgresql-10.6-1.module_el8.0.0+15+f57f353b.x86_64 [[email protected] ~]$ /usr/bin/psql -p 9999 -U postgres -h /tmp postgres psql (10.6, server 13devel) WARNING: psql major version 10, server major version 13. Some psql features might not work. Type "help" for help. postgres=#
Not really an option. This is where peer authentication becomes very handy.
[email protected]:/home/postgres/ [pgdev] sed -i 's/local all all trust/local all all peer/g' /var/tmp/test/pg_hba.conf
Once you switched from trust to peer for local connections only the operating system user that created the instance will be able to connect locally without providing a password:
[email protected]:/home/postgres/ [pgdev] pg_ctl -D /var/tmp/test/ reload server signaled [email protected]:/home/postgres/ [pgdev] psql postgres psql (13devel) Type "help" for help. [local]:9999 [email protected]stgres=#
Other local users will not be able to connect anymore:
[email protected]:/home/postgres/ [pgdev] sudo su - testLast login: Tue Oct 22 21:25:36 CEST 2019 on pts/0 [[email protected] ~]$ /usr/bin/psql -p 9999 -U postgres -h /tmp postgres psql: FATAL: Peer authentication failed for user "postgres" [[email protected] ~]$
So, please, consider enabling peer authentication or at least go for md5 for local connections as well.