Creating a user with database access

Hi,

Can we create a cockroachdb user and map a specific database to that user , i do not want to give access of my database to root and admin user. i want to create a new user with password who can only access my database . i am using free version of cockroachdb.

Thanks.

Hi @muhammad.habib,

You can create a new user and grant only that user access to the database via privileges.

Read about granting privileges here.

However, the root user automatically belongs to the admin role and has ALL privileges for new databases.

You can read more about revoking privileges for other users in your database [here].(https://www.cockroachlabs.com/docs/v19.1/revoke.html).

Let me know if this was helpful.

Thanks,
Matt

i have created a user habib and grant privileges to mydb database like below

GRANT ALL ON DATABASE mydb TO habib;

but come up with following error on select statement

“user habib does not have SELECT privilege on relation users”

my question is if i already give all privilege to database then why we need select privilege to single table ?

Hey @muhammad.habib

When you GRANT ALL ON DATABSE mydb TO habib , only new tables created in mydb will inherit the privileges.

So if you were to create a new table for which you would perform a select statement on, your user habib who has all priveleges would then have access to the table.

I believe you are not able to query the table because the table was created before you granted the priveleges to habib.

As the root user, try running GRANT ALL ON TABLE mydb.<tablename> to habib

You should be able to run the query successfully after that.

Let me know if it worked.

Best,
Matt

When you GRANT ALL ON DATABSE mydb TO habib , only new tables created in mydb will inherit the privileges.

FYI I have created docs issue #5083 to put more emphasis on this in the GRANT docs.

We have set up a secure cluster with cockroachdb certificate. i have created a new user with password because i can’t connect to secure cluster without password. Now issue is that when i connect with my new username and password via DBeaver and try to creaate database it gives error

“ERROR: only superusers are allowed to CREATE DATABASE”

and how to set up root user password if i want to connect with secure cluster with root user ?

Hi @muhammad.habib,

Only users with the admin role can create new databases. Granting the admin role to another user is an enterprise feature.

As a core user, only the root user has the admin role.

You should create the database as the root user and grant all privileges to your user before creating any tables, that way you will be able to access them.

You can only connect to a secure cluster as the root user by authenticating with certificates not a password, read more about this here

Let me know if you have any other questions.

Thanks,
Matt

ok go it.

Now i want to connect with my secure cluster with root user using GORM with following connection string

“postgresql://root@ipaddress:26257/dbtemp?ssl=true&sslmode=require&sslrootcert=certs/ca.crt&sslkey=certs/client.root.key&sslcert=certs/client.root.crt”

and my certs folder path is /home/myuser/certs

but when my code try to connect to database following error occurred

“pq: user root must use certificate authentication instead of password authentication”

i have checked and root certificates key and crt are there inside certs folder. any idea whats wrong with this ?

Also i have successfully loged in with password other than root user .

Can you show me how you’re generating your certificates for the root user?

Also, see this file for a tutorial on how to connect to a secure cluster using GORM. Let me know if its helpful.

@mattvardi we are following same GORM tutorial you shared and we have created secure cluster and create certificate using this tutorial.

I saw you were using Dbeaver before, our docs for DBeaver require changing the SSL certificate key from a .key file to a .pk8 file.

Now that you are using GORM, can you able to confirm that your cert extensions are the correct ones?

Thanks,
Matt

@mattvardi yes i was using both Dbeaver for directly connecting to database engine and GORM with my go language code, and i have issue with both of them while connecting with root user. A normal user having password working fine but when i try to connect with root user it fails.

my certs folder contains following files

ca.crt
client.root.crt
client.root.key
node.crt
node.key

Now i successfully connect via Dbeaver with root user by changing key to .pk8 file. but still not able to connect with gorm and root user.

Also my cockroachdb and go code are on seprate machine within network. should we need to copy certificate (cert folder) on machine where go code running ?

@mattvardi now i have copied my certs to machine where my go code deployment and its working.

Thanks for your valuable time.

Hey @muhammad.habib,

Glad you were able to figure it out!

Let me know if you have any other questions.

Best,
Matt

@mattvardi Thanks.

I want to know about enterprise pricing where can i find this info. Every time it redirect to trial page but i do not want trial i just want to check pricing.

Fill in your information here.

Someone from our team will contact you.

Thanks,
Matt

My email address is Muhammad.habib@moftak.com

Hey @muhammad.habib,

We received your information and will reach out to you.

Thanks again,
Matt