This is maddening.
I'm running PostgreSQL 14 in a Docker container using a standard image.
# docker-compose.yml
version: "3.7"
services:
pg:
image: postgres
environment:
- POSTGRES_PASSWORD=localdev
ports:
- "5432:5432"
volumes:
- ./pg/init:/docker-entrypoint-initdb.d
I bootstrap a couple of schemas (foo
and bar
) and a couple of users (foo_dba
and bar_dba
); and make each user the owner of their respective schema.
CREATE USER foo_dba WITH PASSWORD 'localdev';
CREATE SCHEMA foo AUTHORIZATION foo_dba;
GRANT CONNECT ON DATABASE postgres TO foo_dba;
GRANT ALL PRIVILEGES ON SCHEMA foo TO foo_dba;
CREATE USER bar_dba WITH PASSWORD 'localdev';
CREATE SCHEMA bar AUTHORIZATION bar_dba;
GRANT CONNECT ON DATABASE postgres TO bar_dba;
GRANT ALL PRIVILEGES ON SCHEMA bar TO bar_dba;
While logged in as foo_dba
, I create a table called foo.things
(in the foo
schema) and grant bar_dba
the ability to insert records into it.
CREATE TABLE IF NOT EXISTS foo.things (
id SERIAL PRIMARY KEY
, uid UUID NOT NULL UNIQUE DEFAULT gen_random_uuid()
, name TEXT NOT NULL
, created_at TIMESTAMPTZ NOT NULL DEFAULT timezone('utc', now())
, updated_at TIMESTAMPTZ
);
GRANT CREATE ON SCHEMA foo TO bar_dba;
GRANT USAGE ON SCHEMA foo TO bar_dba;
GRANT INSERT ON TABLE foo.things TO bar_dba;
GRANT USAGE ON SEQUENCE foo.things_id_seq TO bar_dba;
I verify the privileges in the appropriate information_schema
views (e.g. role_table_grants
, role_usage_grants
). Just for sanity, I execute the following query as foo_dba
:
INSERT INTO foo.things (name)
VALUES ('foo was here!')
RETURNING *;
...and it succeeds. So far so good.
Next, I connect to the database as bar_dba
and attempt to run a similar query:
INSERT INTO foo.things (name)
VALUES ('bar was here!')
RETURNING *;
...and I get access denied.
What I've tried:
- RTFM to understand GRANT and Privileges
- Eliminating everything I can to keep this as simple as possible
- GRANT CREATE ON SCHEMA
- GRANT USAGE ON SCHEMA
- GRANT INSERT ON TABLE
- GRANT USAGE ON SEQUENCE for the SERIAL
id
column - GRANT ALL PRIVILEGES ON SCHEMA (!!)
- Confirming that
bar_dba
can run SELECT gen_random_uuid() - Shutting it off and turning it back on again
- docker-compose logs pg
- docker image prune --all -f
- docker volume prune -f
What I expect will happen:
The owner of foo
schema should be able to grant permission to another user, to perform an INSERT on a table in the schema they own.
What is actually happening:
postgres.public> INSERT INTO foo.things (name)
VALUES ('bar was here!')
RETURNING *
[2022-01-08 02:02:43] [42501] ERROR: permission denied for table things
I'm just not seeing it. What am I missing here?