Minimal grants for readonly single-table access on PostgreSQL

Question

Following is a list of commands that seems to work to create new user (login) and grant readonly access for one specified table on PostgreSQL.

Let's assume that these commands are executed on login with sufficient privileges (i.e. postgres login in default install).

CREATE ROLE user_name NOSUPERUSER NOCREATEDB 
NOCREATEROLE NOINHERIT LOGIN PASSWORD 'pwd' VALID UNTIL 'infinity';

Now I want to grant select on table tab_abc on database db_xyz, so here it goes (database db_xyz is selected as current via PgAdmin or something like that):

grant select on tab_abc to user_name;

The question is: is this sufficient or there should be more grants (database connect, usage maybe)?

These commands seem to work for me, but my default installation has default security settings. What additional grants should I add if server admin configured stronger security?

It seems that I don't need to grant connect nor usage - are that implicite while granting select? Is it always so?

Answer 1

The question is: is this sufficient or there should be more grants (database connect, usage maybe)?

Security may be hardened from the default mainly on these points:

• The pg_hba.conf file. It filters connections before any database privilege is considered. The default is relatively open for local connections, but it might be restricted to an explicit list of databases, usernames, and network origins.

• The permissions for public, the pseudo-role that anyone has. A user can implicitly connect only if the connect privilege is granted to PUBLIC. See Created user can access all databases in PostgreSQL without any grants. A database might have all privileges revoked from public. See REVOKE in the doc for the major PostgreSQL version you're using.

• The existence of the public schema. It's created with the database by default for convenience but it's not mandatory. Instead of removing public permissions from the public schema, it may make sense for DBAs to just drop the public schema when new users should have no implicit permission at all.

In the case when these defaults have been removed, to read one table a new user should be granted:

GRANT CONNECT ON DATABASE dbname TO username;

At the database level:

GRANT USAGE ON SCHEMA schemaname TO username;
GRANT SELECT ON schemaname.tablename TO username;