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

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, 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;

And 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 columns instead, where no separate privileges are required. Requires Postgres 10 or later. See:


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: