-1

I want to create a new user in my database PostreSQL database hosted on Azure.

So, following the documentation from Microsoft for pgAdmin, I went to PGAdmin and ran the following command:

CREATE ROLE rikkiWITH LOGIN NOSUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION PASSWORD 'Fete321@';

GRANT azure_pg_admin TO rikki;

FLUSH PRIVILEGES;

It worked well. Yet, when trying to create a new server with this new login I had some issues

enter image description here

  • I disabled SSL connection, so I don't have this error anymore
  • For the password error, I just copied and paste the password I just created but it gives back this error.

So can I create new users that would be able to connect remotely to my PostgreSQL database?

I tried to connect myself through command line and got

C:\Program Files\PostgreSQL\12\bin>psql --host=######.postgres.database.azure.com --port=5432 --username=rikki@######--dbname=capi
Password for user rikki@######:
psql: error: could not connect to server: FATAL:  password authentication failed for user "rikki"
FATAL:  password authentication failed for user "rikki"
4
  • Why do you think the MySQL documentation is relevant for PostgreSQL? – a_horse_with_no_name Nov 22 '19 at 10:24
  • @a_horse_with_no_name Sorry, I didn't realize. I apologize – Revolucion for Monica Nov 22 '19 at 10:25
  • you adjusted the first query for postgres - the other 2 lines are right in the manual page that Phil gave you – eagle275 Nov 22 '19 at 10:31
  • @eagle275 Yes. Despite of this I can't connect to the server because of SSL options which they don't talk about in the link. For the password error, I just copied and paste the password I just created but it gives back this error – Revolucion for Monica Nov 22 '19 at 10:33
2

Read the title of the documentation link you have given. "Create users in Azure Database for MySQL server". You're using MySQL syntax to try and create a user in Postgres.

You'll be wanting to read this.

1
  • Thank you, my bad. I apologize. I am not very good in database administration. I updated my question. I can't connect to the server because of SSL options which they don't talk about in the link. For the password error, I just copied and paste the password I just created but it gives back this error – Revolucion for Monica Nov 22 '19 at 10:33
0

adding up on my comment:

you created a new user in server #1 - This user doesn't exist when creating a server #2 Why do you think you need another server - usually you want a new database-instance in server #1 for your new user

CREATE DATABASE <newdb>;

CREATE ROLE <db_user> WITH LOGIN NOSUPERUSER INHERIT CREATEDB NOCREATEROLE NOREPLICATION PASSWORD '<StrongPassword!>';

GRANT CONNECT ON DATABASE <newdb> TO <db_user>;

from Phil's linked manual page

if you want the new user with advanced privileges

Using an admin account, you may need to grant additional privileges to secure the objects in the database. Refer to the PostgreSQL documentation for further details on database roles and privileges. For example:

GRANT ALL PRIVILEGES ON DATABASE <newdb> TO <db_user>;
5
  • I thought I needed to create another server instance because as far as when I connect to my first server in pgAdmin I have to add the admin credentials I thought one had to create another server instance in pgAdmin. Whatever the user I just want to test if I'm able to connect to my database being another user – Revolucion for Monica Nov 22 '19 at 11:05
  • to connect use and adjust Azure-CLI >psql --host=mydemoserver.postgres.database.azure.com --port=5432 --username=db_user@mydemoserver --dbname=newdb – eagle275 Nov 22 '19 at 11:08
  • Ah, there is now way to do it through pgAdmin? – Revolucion for Monica Nov 22 '19 at 11:09
  • should work the same way ... I suggest you dig through the page Phil linked - and some more of the azure documentation by microsoft – eagle275 Nov 22 '19 at 11:10
  • Yes, I read it all and now watching this video, _Connect; 2017 Overview Azure Database for PostgreSQL and MySQL _. Yet I don't get why I can't connect with this new user providing the password I provided in the command line in the doc – Revolucion for Monica Nov 22 '19 at 11:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.