2

I want to create a read-only role in PostgreSQL and use it to assign it to multiple new users, who should have read-only access. It makes sense to me to also grant the read-only permissions to the role on the target DB. My concern is, whether the role, created with 'NOLOGIN' should have the 'CONNECT ON DATABASE' permission. Or is it better for some reason to grant the connect permission only to individual users with logins?

The role:

CREATE ROLE role_readonly WITH 
    NOLOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';

Permissions:

# should this be here?
GRANT CONNECT ON DATABASE db_name TO role_readonly;
#
GRANT USAGE ON SCHEMA public TO role_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO role_readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO role_readonly;
1

It is fine to grant CONNECT on a database to a NOLOGIN role. The effect is that all members of the role are allowed to connect to the database. That is independent from the question whether the role can login or not.

Note that by default, everybody (PUBLIC) is allowed to connect to a database. You would have to REVOKE that privilege to make the above privilege meaningful.

2
  • Thanks! You've cleared it up nicely. – Ondrej Sotolar Mar 9 at 9:21
  • Worth mentioning here that roles and users are the same in Postgres. A user may be thought of as a role that is allowed to login — achievable as you have described. – Colin 't Hart Mar 9 at 11:22

Your Answer

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.