Grants question

hello - newbie here trying out cockroachdb. for some reason, this first statement here didn’t seem to grant insert privileges:

cockroach sql -e ‘GRANT ALL ON DATABASE bank TO maxroach’ ;

my node script from here is giving me this error:

error inserting into and selecting from accounts { error: user maxroach does not have INSERT privilege on table accounts

this seems odd because of what i see here:

show grants on database bank for maxroach ;
±---------±---------±-----------+
| Database | User | Privileges |
±---------±---------±-----------+
| bank | maxroach | ALL |
±---------±---------±-----------+

however, this command fixes everything:

cockroach sql -e ’ REVOKE all ON database bank FROM maxroach’ ;

cockroach sql -e ‘GRANT SELECT, INSERT ON bank.accounts TO maxroach’ ;

SHOW GRANTS ON bank.accounts FOR maxroach;
±---------±---------±--------------+
| Table | User | Privileges |
±---------±---------±--------------+
| accounts | maxroach | INSERT,SELECT |
±---------±---------±--------------+

could somebody kindly explain why the first command granting all did not work? is there some difference between granting at the table level vs. at the database level? i would have thought that anything done at the db level would trickle down to the tables.

but beyond that, cockroachdb is pure magic. easy to install, easy to set up. i am very impressed.

1 Like

@edwardsmarkf, hm, did you follow the sequence in the docs (i.e., create db, grant user privileges on entire db, then create table)? If so, you shouldn’t be getting that error because when a user is granted privileges for a database, new tables created in the database inherit the privileges (more on that here).

Can you drop your current bank database, go through the steps again, and if you still get this error, check the privileges on the accounts table? They should look like this:

root@:26257> SHOW GRANTS ON bank.accounts;
+----------+----------+------------+
|  Table   |   User   | Privileges |
+----------+----------+------------+
| accounts | maxroach | ALL        |
| accounts | root     | ALL        |
+----------+----------+------------+
(2 rows)

I know this is over 3 years old, but since I ran into the same problem: yes, I also created the table first and then created the account, and the reason is simple: I followed the Docker instructions and Step 3 asks for creating a DB.
Then I followed the Node.js sample which re-used that table.

Not a big deal, but I guess many new users who start with CockroachDB the first time will run into the same issue. Luckily the error message is very clear.

1 Like