When setting up a PostgreSQL server by default it will not respond to queries outside of localhost. The settings for this can be found in pg_hba.conf, which is located either in /etc/postgresql/16/main/ or in /var/lib/pgsql/data/, depending on the installation. Additionally, there is a postgresql.conf file that needs to be updated to listen to specific interfaces.
In the pg_hba.conf file we can establish the specific hosts, database, user, CIDR-address and authentication methods we want to use. It is possible to specify different methods for specific hosts:
host all all 192.168.101.20/24 trust
And in postgres.conf we must ensure that listen_address is set to listen on the correct interfaces.
To create a database and user with all permissions, use the following:
sudo -u postgres psql # for linux/mac
# psql -U postgres # for windows users
\l # means show all databases
CREATE DATABASE blogdb;
CREATE USER nofoobar WITH ENCRYPTED PASSWORD 'supersecret';
GRANT ALL PRIVILEGES ON DATABASE blogdb TO nofoobar;
# \c dbname : to connect to a database
# \dt : to see tables in the databaseIf there are errors with loging in and with permissions try updating the pg_hba.conf file to trust the line about Unix sockets. At least for development it should be fine.
Once you are able to log in it is possible that the user you created will be denied access to the database. If that’s the case, there are a few options to try:
ALFTER DATABASE my_db OWNER TO db_user;- GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO admin;
- GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin;