How do I create an insert-only user for a Postgres table with an index?

Question

Here's my proposed schema:

CREATE TABLE Surveys (
 id serial primary key,
 user_email citext,
 survey_data jsonb,
 created_at timestamp default current_timestamp
);
CREATE INDEX surveys_email_idx ON Surveys(user_email);
CREATE USER SurveyWriter;

I know I need to:

GRANT INSERT ON dbname.Surveys TO SurveyWriter;

But do I also need to:

GRANT INSERT, UPDATE ON dbname.surveys_email_idx to SurveyWriter;

Is there anything else I'm not thinking of?

Answer 1

You cannot use GRANT on an index. Indexes do not have separate permissions, they are "implementations details" of the owning table.

You already covered the INSERT privilege (as table owner or superuser):

GRANT INSERT ON dbname.surveys TO SurveyWriter;

If you want the role to log in, it needs access to the database.

GRANT CONNECT ON DATABASE my_database TO SurveyWriter;

Plus, the role needs the LOGIN privilege. CREATE USER does that implicitly. The manual:

CREATE USER is now an alias for CREATE ROLE. The only difference is that when the command is spelled CREATE USER, LOGIN is assumed by default, [...]

And access in pg_hba.conf.

(Else you might want to use SET ROLE instead.)

You need at least USAGE on the schema - which is given by default in the public schema, unless you revoked it. The schema in your example has the (misleading!) name "dbname". So:

GRANT USAGE ON SCHEMA dbname TO SurveyWriter;

You are not confusing databases and schemas, are you? See:

• PostgreSQL and default Schemas

Since you use a serial, you also need at least USAGE on the connected SEQUENCE:

GRANT USAGE ON SEQUENCE public.surveys_id_seq TO SurveyWriter;

Or consider an IDENTITY column instead, where no separate privileges are required. Requires Postgres 10 or later. See:

• Prevent explicit insert into serial columns

Related:

• How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?

---

Aside 1:
Use legal, lower-case names everywhere to avoid confusion. SurveyWriter will be created as surveywriter. I suggest survey_writer instead. See:

• Are PostgreSQL column names case-sensitive?

Aside 2:
Consider timestamp with time zone: created_at timestamptz DEFAULT current_timestamp. See:

• Ignoring time zones altogether in Rails and PostgreSQL

• danieltalsky: Wow. Thank you so much for all the detailed help. Most example code on postgres is too basic to be usable, and googling for this issue did not help me AT ALL. I really appreciate the code review and advice. You're a peach.