How create username postgresql container

Hi at all

I would like to know what is the command that allows me to create other usernames in postgresql obviously inside the docker container.

Example during creation I ran the command: “” docker run --name postgresql -e POSTGRES_USER = myusername -e POSTGRES_PASSWORD = mypassword -p 5432: 5432 -v / data: / var / lib / postgresql / data -d postgres " "With this command I created the postgresql container instance but with only one user logging in. I would like to add other users in postgresql by invoking the docker container created

Who can help me?

Thank you

Hello,
the user created during initial startup of postgres-container is the super-duper-admin-user. Afterwards this information is not used as the data is stored within the database).
Using this user you can establish a connection to the database and create other users - see PostgreSQL: Documentation: 15: CREATE USER for the SQL-command.
If you prefer a more graphical way you can use a tool of your choice - PgAdmin (also available as a docker-image) is the one I use.

Hello thanks for intervening; I tried but the command fails I tell you in detail what I do:

  1. View the containers available with the command “Docker ps”
  2. I access the container with the Docker exec -it bash command
  3. At this point the command prompt looks like this: root @ “idcontainer”: / #
  4. I access postgresql (currently without DB), with the user created during creation as SUPERUSER “erica.maselli”
    with the command psql -U erica.maselli
  5. At this point the command prompt looks like this: erica.maselli = #

Small important note: when I type psql -U erica.maselli, the command prompt does not ask me for the password created when I created the SUPERUSER user during creation, it automatically changes the prompt to heather.maselli = #

  1. I execute the command “\ du” and a table Role name erica.masell appears; List of roles: Superuser, Create role, Create DB, Replication, Bypass RLS; Member of {}

  2. At this point I execute the command: "CREATE USER giuseppe.matropasqua SUPERUSER;

(I put the; at the end of SUPERUSER and I also tried to put after the; CREATE ROLE I tried even without CREATE ROLE the result does not change and it is this ERROR: syntax error at or near “.”
LINE 1: CREATE USER giuseppe.mastropasqua SUPERUSER)

  1. At this point I try to execute the command “CREATE USER giuseppe.matropasqua SUPERUSER without the”; "it does not give me any error but the command prompt changes to: erica.maselli- # ie - # while before executing the CREATE command without the; there was = #

  2. I execute the \ du command and the only user that appears is only erica.maselli without adding giuseppe.mastropasqua.

At this point I am wondering is it possible to create more than one SUPERUSER?

Or in the list of roles of erica.maselli (as specified in point 6), is the CREATE USER role missing?

=# means that you can start your SQL-command or any PostgreSQL-specific command (i.e. \du).
-# means that you have already started a SQL-command, which is not yet finished with the ; so you can continue your SQL-command or issue any PostgreSQL-specific command starting with a backslash (i.e. \dt).

I have established a connection to the PostgreSQL-database from my bash
using this command
psql -h 127.0.0.1 -U postgres -W
which asked for my user’s password and after typing the correct password I was logged in with this prompt:
postgres=#
Then I created a user test.user with a password using this command:
create user "test.user" superuser login with encrypted password 'password';
I have enclosed the username with " as it contains a . (otherwise I received an errormessage) and added the password password. Also I allowed login for this account.
Afterwards I tried to login from the bash using
psql -h 127.0.0.1 -U test.user -W postgres
to login as user test.user to database postgres (as no database test.user is present - at least not yet) and entered password as password and was successfully logged in :slight_smile:
What is the difference to your command: I have added the login during creation, specified a password for this user and enclosed the user’s name with " as it contains a ..

1 Like

So I tried to write everything in lowercase: “” "
create user giuseppe.marcotrigiano superuser login with encrypted password ‘password’; “” “”
It returns me syntax error

I tried in uppercase CREATE USER giuseppe.marcotrigiano SUPERUSER LOGIN WITH ENCRYPTED PASSWORD ‘123’;
It returns me syntax error

I tried in uppercase without “ENCRYPTED”: “” CREATE USER giuseppe.marcotrigiano SUPERUSER LOGIN WITH ENCRYPTED PASSWORD ‘123’;

It returns me syntax error.

The only thing that works without error and creates me the user is:

CREATE USER “giuseppe.marcotrigiano” SUPERUSER;

If I add to the latter after SUPERUSER:
CREATE USER “giuseppe.marcotrigiano” SUPERUSER LOGIN WITH ENCRYPTED PASSWORD ‘123’ or only with WITH PASSOWORD or without LOGIN and therefore WITH ENCRYPTED PASSWORD ‘123’ always goes into error.

The only one that works is

CREATE USER “giuseppe.marcotrigiano” SUPERUSER;

Could it be a version problem?

I have the latest

Hello,
I have used the postgres:latest-image for my test which identifies itself as version 15.0.
Uppercase or lowercase for the command should not make any difference.

That is expected because the username is not enclosed in ".

Unfortunately, my crystal ball is currently broken :slight_smile: so it would be better if you can provide the errormessage. Often the errormessage is helpful in troubleshooting what was entered wrong (e.g., forgot a ), a ;, had a typo in the command, …). At least in the command you have provided here the trailing ; is missing.
Every normal SQL-command at the database’s CLI has to finished with a ;. Exceptions are some database-specific commands (e.g., the commands starting with the backslash \ for PostgreSQL or the commands starting with the dot . in SQLite3).

1 Like