Sql prompting for password

General
#1

hello -

i do the following command:

cockroach user set markedwards --certs-dir=./certs --password ;

and of course i am prompted for two passwords.

my question: when i do this command:

cockroach sql --user=markedwards --certs-dir=./certs ;

should i be prompted to enter a password? its just letting me in, no password required.

i have also tried this:

cockroach sql --user=root -e ‘DROP USER markedwards;’ --certs-dir=./certs ;
cockroach sql --user=root -e “CREATE USER markedwards WITH PASSWORD ‘aaaaaa’ ;” --certs-dir=./certs ;

no success - still no prompting for password.

Re-posting earlier question about being prompted for password
#2

Hi Mark
thanks for asking – can you share with us for confirmation which version of CockroachDB you are using?

I tried the same exact steps with versions 1.1 and 2.0-beta and the client does request the password properly. I would love to know how to reproduce what you observe.

Try to run which cockroach to see what binary is used when you invoke the command.

#3

Hello and thank you for replying. I am running 1.1.6.

cockroach version; #entered this command
Build Tag: v1.1.6
Build Time: 2018/03/12 17:58:05
Distribution: CCL
Platform: linux amd64
Go Version: go1.8.3
C Compiler: gcc 6.3.0
Build SHA-1: f6b7567d85d54689a7d1b61907336d1bc72b09a4
Build Type: release-gnu

#4

hello Raphael - here are my steps to reproduce the situation:

mkdir ./path-to-ca-key/;

cockroach cert create-ca --certs-dir=./path-to-certs-directory/ --ca-key=./path-to-ca-key/ca-key ;

cockroach cert create-node {ipaddrInternal} {ipaddrExternal} --certs-dir=./path-to-certs-directory/ --ca-key=./path-to-ca-key/ca-key ;

cockroach cert create-client root --certs-dir=./path-to-certs-directory/ --ca-key=./path-to-ca-key/ca-key ;

cockroach cert create-client markedwards --certs-dir=./path-to-certs-directory/ --ca-key=./path-to-ca-key/ca-key ;

cockroach start --certs-dir=./path-to-certs-directory/ --http-host=localhost --background ;

cockroach sql --user=root -e ‘CREATE DATABASE bank;’ --certs-dir=./path-to-certs-directory/ ;
cockroach sql --user=root -e “CREATE USER markedwards WITH PASSWORD ‘aaaaaa’;” --certs-dir=./path-to-certs-directory/ ;
cockroach sql --user=root -e ‘GRANT ALL ON DATABASE bank TO markedwards;’ --certs-dir=./path-to-certs-directory/ ;

cockroach node status --certs-dir=./path-to-certs-directory/ ;
cockroach user set markedwards --certs-dir=./certs --password ;
aaaaaa
aaaaaa

now on the same server, i do the following:

cockroach sql --user=markedwards --certs-dir=./path-to-certs-directory --database=bank;

this is not prompting me to enter the password.

#5

I notice you use two separate directories named certs and path-to-certs-directory.

Then I see you use the command user set with --certs-dir=./certs but then connect the client with:

Have you tried this:

cockroach sql  --user=markedwards  --certs-dir=./certs  --database=bank;
#6

Raphael - i apologize, i probably mistyped something. here are my exact steps (from a bash-shell script) below - and i am still not being prompted for a password when i issue the SQL command on the same server:

#! /bin/bash

# init-cockroach-server.bsh   last update: 2018-03-26

# script to build a cockroach server 
 
dbUser='feathersuser';
dbPass='aaaaaa';
dbPort='26257';
dbName='bank';
 

if  [ '${$(groups)/sudo}' ] ;
then   SUDO='sudo' ; 
elif  [ '${$(whoami)/root' ] ;
then   echo SUDO=''; 
else
     echo 'you either need to be have sudo or be logged in as root!';
      exit;
fi;
 

${SUDO} yum --assumeyes   update ;
 
${SUDO} wget -qO- https://binaries.cockroachdb.com/cockroach-v1.1.6.linux-amd64.tgz | tar  xvz ;

${SUDO} cp --force --verbose  ./cockroach-v1.1.6.linux-amd64/cockroach /usr/local/bin/ ;

which cockroach ;  ##  /usr/local/bin/cockroach
cockroach  version ;   ## v1.1.6 at the time of this writing


mkdir  ./path-to-ca-key/;

cockroach cert  create-ca  --certs-dir=./path-to-certs-directory/  --ca-key=./path-to-ca-key/ca-key  ;

cockroach cert create-node localhost ${ipaddrInternal} ${ipaddrExternal}   \
         --certs-dir=./path-to-certs-directory/     \
         --ca-key=./path-to-ca-key/ca-key    ;

cockroach cert create-client root         --certs-dir=./path-to-certs-directory/    --ca-key=./path-to-ca-key/ca-key  ;

cockroach cert create-client ${dbUser}  --certs-dir=./path-to-certs-directory/    --ca-key=./path-to-ca-key/ca-key  ;

 ## finally, start up server:
cockroach start --certs-dir=./path-to-certs-directory/  --port=${dbPort}  --http-host=localhost --background ;

cockroach sql  --user=root  -e  "CREATE DATABASE ${dbName};"                          --certs-dir=./path-to-certs-directory/  ;
cockroach sql  --user=root  -e  "CREATE USER ${dbUser} WITH PASSWORD '${dbPass}';"    --certs-dir=./path-to-certs-directory/ ;
cockroach sql  --user=root  -e  "GRANT ALL ON DATABASE ${dbName} TO ${dbUser};"       --certs-dir=./path-to-certs-directory/  ;
 
cockroach node status --certs-dir=./path-to-certs-directory/   ;
 
export dbUser;
export dbPass;
expect <(cat <<'END_OF_EXPECT_CR_SET_USER'

    set timeout -1

     spawn cockroach user set $env(dbUser) --certs-dir=./path-to-certs-directory/  --password ; ## prompts for two passwords
     
     expect -re ".*Enter password:.*"
     send -- "$env(dbPass)\r"
     
     expect -re ".*Confirm password:.*"
     send -- "$env(dbPass)\r"
     
     expect eof
END_OF_EXPECT_CR_SET_USER
)

cat >./nodePgTest.js  <<END_OF_NODE_SCRIPT;
// Connect to the "${dbName}" database.
var config = {
  user: '${dbUser}',
  password: '${dbPass}',
  host: '${ipaddrExternal}',  
  database: '${dbName}',
  ssl: true,
  port: ${dbPort}
};
// Create a pool.
const pool = new pg.Pool(config);

// or connect with just one line
const connectionString = 'postgresql://${dbUser}:${dbPass}@${ipaddrExternal}:${dbPort}/${dbName}?ssl=true';
const pool = new pg.Pool({
  connectionString: connectionString,
})
END_OF_NODE_SCRIPT

cat <<END_OF_SCRIPT
to start:          cockroach  start  --port=${dbPort} --http-host=localhost  --certs-dir=./path-to-certs-directory --background ;
to check status:   cockroach  node status                                    --certs-dir=./path-to-certs-directory/     ;
to quit:           cockroach  quit                                           --certs-dir=./path-to-certs-directory/     ;
to log in:         cockroach  sql --user=${dbUser} --database=${dbName}      --certs-dir=./path-to-certs-directory/     ;
Dont forget to do:      cat ./nodePgTest.js ;    to see an example 
END_OF_SCRIPT
#
#7

As I mentioned in your repost of this question, you generated client certificates for both root and markedwards. When client certificates are found in the certs directory they are used for authentication instead of a password.

If you really want to login with a password when client certs are present, you need to either:

  • move the client certs out of the certs directory
  • or specify a different certs directory (still with a ca.crt)
  • or use the fully-specified connection string with a password (eg: --url 'postgresql://markedwards:password@localhost:26257/?sslmode=verify-full&sslrootcert=<path to ca.crt>)
1 Like