Should Roles Without Login Have Connect Permission on a Database? – PostgreSQL Permissions

permissionspostgresqlroleusers

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;

Best Answer

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.

Related Solutions

When creating views, why do users need direct object permissions if they already have the same permissions via a role

While you may have a lot of users, it would be unusual for them to require their own views. The views should be in one schema (possibly the one owning the tables) and the users should query them by either prefixing the schemaname (eg vwowner.view) or using the

ALTER SESSION SET_CURRENT_SCHEMA=vwowner

Roles are transient. You can do a SET ROLE NONE to turn them all off. You can have multiple sessions for the same account with different roles enabled. That isn't compatible with the way that Oracle handles objects (where they are either valid or they are not; they can't be valid for some sessions and not for others).

PostgreSQL – Group Permissions Not Inheriting to Table Level

The simple test:

postgres=# create schema foo;
CREATE SCHEMA
postgres=# create table foo.t(x int);
CREATE TABLE
postgres=# create role foomaster nosuperuser nologin inherit;
CREATE ROLE
postgres=# create role fooslave password '111' login inherit in role foomaster;
CREATE ROLE
postgres=# grant usage on schema foo to foomaster;
GRANT
postgres=# set role fooslave;
SET
postgres=> select * from foo.t;
ERROR:  permission denied for relation t
postgres=> set role postgres;
SET
postgres=# grant select on foo.t to foomaster;
GRANT
postgres=# set role fooslave;
SET
postgres=> select * from foo.t;
 x 
---
(0 rows)

It seems that you are doing something wrong.

Upd: About newely created table:

postgres=> set role postgres;
SET
postgres=# create table foo.tt(xx int);
CREATE TABLE
postgres=# set role fooslave;
SET
postgres=> select * from foo.tt;
ERROR:  permission denied for relation tt
postgres=> set role postgres;
SET
postgres=# grant select on foo.tt to foomaster;
GRANT
postgres=# set role fooslave;
SET
postgres=> select * from  foo.tt;
 xx 
----
(0 rows)

PPS: Hope that my investigations was helpful at least for somebody.

Best Answer

Related Solutions

When creating views, why do users need direct object permissions if they already have the same permissions via a role

PostgreSQL – Group Permissions Not Inheriting to Table Level

Related Question