I have a PostgreSQL instance running on Cloud SQL in GCP. I am connecting to it via a psql client from another machine. The connection is working fine.

However I want to know what happens when you run the following command.

Cloud SQL already uses SCRAM-SHA-256 as a password encryption mechanism by default. I have verified it. So I don't know what this command is doing.

CREATE ROLE temprole NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:H45 UIZiJUcEXrB9SHlv5Q==$I0mc87UotsrnezRKv9Ijqn/zjWMGPVdy1zHPARAGfVs=:nSjwT9LGDmAsMo GqbmC2X/9LMgowTQBjUQsl45gZzA=';

Is the string inside ' ' set as password? If not, then what is it?

Secondly, how do I login as the temprole user? Do I need to set the password for it again?

CodePudding user response：

PostgreSQL does not store the clear text password, but a hash of it. The password_encryption parameter determines how the clear text password gets hashed. Now when you create or alter a role, you can not only set the clear text password (which the server will hash), but you also can set the already hashed password, like your example shows. The advantage is that you don't have to transfer the clear text password to the PostgreSQL server. (psql's command \password does it that way.)

To log into the server, you need to know the clear text password. Whoever created the hash in the statement you show will know that password, so you will have to ask there.

CodePudding user response：

The system recognizes that string as already being a valid format for a scram verifier (AKA "encrypted" password) and so just stores it for future password verification work. If it had not recognized it as being a valid format, then it would have treated it as a plain password, digesting it into a verifier before storing it. It is infeasible to go from the verifier back to the password--that is one of the criteria that went into the design of the system. Whoever generated that string should know what password it represents. If they don't know (or won't tell you) then you will need to either guess and get lucky, or reset the password before you can log in as that user.

(And by the way, that password was extremely easy to guess, so if it this is a real situation you had better go change it immediately)