Search code examples
sqlpostgresqlrolesrbac

Cannot make granting access to existing tables work

I'm failing at adding a new DB role, that would have a SELECT privilege on tables from particular database.

My problem is that the role is not able to SELECT from a table in existing DB.

Here's my failing test case (written so it can safely be copy-pasted into a /tmp/test.sh and executed):

# --- cleanup objects, if any
psql -U postgres -c "REVOKE SELECT ON ALL SEQUENCES IN SCHEMA public FROM db_reader"
psql -U postgres -c "REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM db_reader"
psql -U postgres -c "REVOKE USAGE ON SCHEMA public FROM db_reader"
psql -U postgres -c "DROP ROLE IF EXISTS db_reader"
psql -U postgres -c "DROP DATABASE IF EXISTS some_existing_db"
# --- test
psql -U postgres -c "CREATE DATABASE some_existing_db"
psql -U postgres some_existing_db -c "CREATE TABLE cats (name varchar(10))"
psql -U postgres some_existing_db -c "INSERT INTO cats (name) VALUES ('a'), ('b')"
psql -U postgres -c "CREATE ROLE db_reader WITH login"
psql -U postgres -c 'GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO db_reader'
psql -U postgres -c 'GRANT SELECT ON ALL TABLES IN SCHEMA public TO db_reader'
psql -U postgres -c 'GRANT USAGE ON SCHEMA public TO db_reader'
psql -U db_reader some_existing_db -c "SELECT COUNT(1) FROM cats"

Looks like I'm missing something extremely, embarrassingly obvious here, as the above fails with the following error:

ERROR:  permission denied for relation cats

Why?

Solution

  • You are missing that databases are logically separated.

    Your GRANT statements are executed in database postgres (if you do not specify a database name, psql will try to connect to a database with the same name as the database user).

    Consequently, the effect of these grants is limited to the database to which you are connected.

    You have to add some_existing_db to the psql invocations where you grant privileges to db_reader.