How to remote access to postgres within the carto container

[GISman-Lee]

Hi Sverhoeven,

May I ask what is the dbname, username and password to access to the postgresql inside the docker ? I found "carto_db_development', 'postgres' and '' in the config file, which seems to be not right ?

Thanks

[sverhoeven]

The "carto_db_development" is the central db and used for user management. The carto server is running in development mode, that is why the database is called "carto_db_development". Each user/organization has its own database to store datasets. You will need to map the username/organization name to a database by querying "carto_db_development" db. See https://github.com/sverhoeven/docker-cartodb/blob/master/geocoder.sh#L15 how the user database of the geocoder is retrieved.

The postgres user is used because it can connect to any database without problems so it is easiest to use the postgres username.. The PostgreSQL has been configured to trust local users so no need for password.

So to connect to a database of a user for example dev user do:

docker exec -ti <name of carto container> bash
USER_DB=`echo "SELECT database_name FROM users WHERE username='dev'" | psql -U postgres -t carto_db_development`
psql -U postgres $USER_DB

[GISman-Lee]

Thanks a lot Mr.Sverhoeven, this really helps !!!

By the way, is there any way to access to postgresql in the container from outside apps ? I saw an older thread where it said it's not possible ??

Actually is it possible to modify the DOCKFILE, from "EXPOSE 80" to "EXPOSE 80 5432" and then rebuild to achieve this ?

Thank you

[sverhoeven]

The current Dockerfile uses the default PostgreSQL configuration which does not allow remote PostgreSQL connections. So the PostgreSQL configuration needs to be changed as well as exposing 5432 in Docker.

[GISman-Lee]

Hi Serhoeven,

I've investigated the final solution which I would like to share to everyone.

1. As you mentioned in your post, git clone https://github.com/sverhoeven/docker-cartodb.git to download the docker-cartodb.git
2. In DockerFile, we changes "Expose 80" to "Expose 80 5432"
3. Use sudo docker build -t=sverhoeven/cartodb docker-cartodb/ to build.
4. Then run the container with sudo docker run -d -p 80:80 -p 5431:5432 -h <servers-external-ip-address> sverhoeven/cartodb
5. Enter the container as you mentioned: sudo docker exec -ti <name of carto container> bash
6. Edit postgres.conf file with: vi /etc/postgresql/10/main/postgresql.conf, then change listen_addresses = '*'
7. Edit pg_hba.conf file with : vi /etc/postgresql/10/main/pg_hba.conf, then add host all all 0.0.0.0/0 trust at the end of the file.
8. Restart Postgres with: /etc/init.d/postgresql restart

This will work !!!! And we can access to pg within carto container via port 5431.