Creating a Superuser Role in PostgreSQL
Published: May 23, 2021
What do you do when you have an error like this: "FATAL: role "root" is not permitted to log in."
Suppose you are getting the error "FATAL: role "root" is not permitted to log in" when you are using PostgreSQL in a docker container (but not necessarily in a container). You must set up a user/role with the necessary permissions, database, and password (to log in outside the container). Let's begin with the PostgreSQL installation from docker. Following the Postgres page in Docker Hub, one of the currently supported versions of PG containers is 13.3-alpine (hint about docker containers: prefer using Alpine Linux images because they are thin, basically, smaller than any other image. It could have only 5MB as the base image). Now, we can create and access a local container from this image by running (considering you have already set up docker on your machine):
docker run --name some-postgres -e POSTGRES_PASSWORD=some-difficult-THING-55922--- -d postgres:13.3-alpine
docker start some-postgres
# -it stands for interactive terminal
docker exec -it some-postgres /bin/bash
psql -U postgres # Sometimes, you need to run `su - postgres` to log in as the Postgres user
CREATE USER root WITH SUPERUSER LOGIN; -- Create a Role with a couple of basic privileges
ALTER USER root PASSWORD 'S0mething...';
\q
createdb -U root root
exit
exit
Now, we can connect from the host to the PostgreSQL server in that docker container. If you are using Ubuntu 20.04 and you don't have the Postgres-client, you'll need to set up a PPA-repository for your package manager (considering it is Apt, and following this source):
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
# We are going to install the client only. It's okay to have the server. On Mac OS X, you must install both and turn off the Postgresql service with Homebrew.
sudo apt update
sudo apt install postgresql-client-13
Consequently, we can get the hostname from the PostgreSQL container like this:
HOSTNAME=$(docker exec -it some-postgres hostname -i)
Finally, you can connect and access the controller (it will ask you for the password-defined above).
psql -U root -h "${HOSTNAME::-1}" # There is a way to set up the container to be connected with the localhost through a specific port, but let it be for another article.
# The::-1 is necessary because the output from docker adds a new line symbol at the end of HOSTNAME.
Done! These steps are enough to connect and run the console in the container from your local machine (the host). Tell us about your journey in the comments!
