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?
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 USERis now an alias forCREATE ROLE. The only difference is that when the command is spelledCREATE USER,LOGINis 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:
