0

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?

0

1 Answer 1

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.

1
  • :facepalm: Oof. I knew it would be something so seemingly obvious. Thanks for catching that. Jan 8 at 17:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.