Solved Postgres root login info?

[ChicagoGregg]

How can I get the Postgres root password for the postgres container?

I've done it with mysql and tried with postgres, but it keeps saying incorrect password at the psql command line.

I've done a "docker inspect postgres | grep IPAddress", obtained the IP, and tried to use psql with --user and --host (password pasted in), but it doesn't accept the password. I've connected remotely from through the postgres terminal as well as through the cloudron server, but still no luck.

This is similar to the steps I've taken as well, found through this link: https://forum.cloudron.io/topic/2200/external-mysql/5

I installed a Directus RC custom container and didn't realize it was using PG as the DB driver. So, I need to dump the PG tables, convert it to use mysql, and reload the schema into mysql.

Thanks!

-gregg

[nebulon]

@chicagogregg you should be able to see that using the following command via SSH on your server:

docker inspect postgresql | grep CLOUDRON_POSTGRESQL_ROOT_PASSWORD

[ChicagoGregg]

@nebulon Thanks.

That's what I thought, too. But I get the following:

user@vt1cloud01:~$ docker exec -ti postgresql bash
root@postgresql:/# env|grep CLOUDRON_POSTGRESQL
CLOUDRON_POSTGRESQL_ROOT_PASSWORD=xxxxxx
CLOUDRON_POSTGRESQL_TOKEN=xxxxxx
root@postgresql:/# psql --host=postgresql --user=root --password
Password: (PASTED PW HERE)
psql: error: FATAL:  password authentication failed for user "root"

Any other ideas?

[nebulon]

@chicagogregg you should also be able to connect within the container using this over SSH:

docker exec -ti postgresql psql --dbname=postgres

[ChicagoGregg]

@nebulon I wasn't specifying any dbname. I'm not totally used to postgres and thought it would just connect into the db instance and I can list databases, etc. from there. Also looks like I can't use a root account to access all databases. It's alright as long as I can get into the schema for directus and transfer things over to MySQL. I'll have to take a primer on postgres.

Thanks for the help!

[girish]

You don't need the root password if you access via localhost since we configured postgres that way.

root@postgresql:/# psql -Uroot  --dbname=postgres
psql (12.7 (Ubuntu 12.7-1.pgdg20.04+1))
Type "help" for help.

postgres=# \list
                                                                                   List of databases
                Name                |                Owner                 | Encoding | Collate | Ctype |                               Access privileges                               
------------------------------------+--------------------------------------+----------+---------+-------+-------------------------------------------------------------------------------
 db3ef70900294e425783b45e1555c27dd4 | user3ef70900294e425783b45e1555c27dd4 | UTF8     | C       | C     | user3ef70900294e425783b45e1555c27dd4=CTc/user3ef70900294e425783b45e1555c27dd4
 postgres                           | postgres                             | UTF8     | C       | C     | 
 template0                          | postgres                             | UTF8     | C       | C     | =c/postgres                                                                  +
                                    |                                      |          |         |       | postgres=CTc/postgres
 template1                          | postgres                             | UTF8     | C       | C     | =c/postgres                                                                  +
                                    |                                      |          |         |       | postgres=CTc/postgres
(4 rows)

Above you can see that db3ef70900294e425783b45e1555c27dd4 is a database. In postgres, there is no "use" like mysql afaik. So you have to reconnect like this:

root@postgresql:/# psql -Uroot  --dbname=db3ef70900294e425783b45e1555c27dd4
psql (12.7 (Ubuntu 12.7-1.pgdg20.04+1))
Type "help" for help.

db3ef70900294e425783b45e1555c27dd4=# \dt
<the tables>

[ChicagoGregg]

@girish I use DBeaver with an SSH tunnel. I just found an option that I forgot to toggle on -- "Show all databases".

Now it shows all the databases!

On a side note, I did first try to configure an SSH tunnel and a post-command to execute "/usr/local/bin/docker exec -ti postgresql bash" and that worked as well.

But, only one tunnel is needed to the cloudron server to login as root that allows access to all databases.

I'm good to go. Thanks for the help!