5

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?

New contributor
danieltalsky is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
0
8

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:

1
  • 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. – danieltalsky Jun 9 at 13:51

Your Answer

danieltalsky is a new contributor. Be nice, and check out our Code of Conduct.

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.