PostgreSQL granted all privileges on schema, but user still cannot insert a record

Question

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?

Answer 1

You need to grant select also. I believe this is so that the uniqueness of uid and id can be verified. A grant insert without select could be misused by trying to insert a value, if it fails the user would know that that value already existed.

Correction, the above is valid for the update.

In your case, the returning is the culprit. Drop that and bar_dba can insert.