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

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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:

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:

Related:


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

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply