Postgresql – Minimal grants for readonly single-table access on PostgreSQL

permissionspostgresql

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?

Best Answer

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;

Related Solutions

Postgresql – Created user can access all databases in PostgreSQL without any grants

At the SQL level, every user can indeed connect to a newly created database, until the following SQL command is issued:

REVOKE connect ON DATABASE database_name FROM PUBLIC;

Once done, each user or role that should be able to connect has to be granted explicitly the connect privilege:

GRANT connect ON DATABASE database_name TO rolename;

Edit: In a multi-tenant scenario, more than just the connect privilege would be removed. For multi-tenancy tips and best practices, you may want to read on the postgresql public wiki: Shared Database Hosting and Managing rights in PostgreSQL.

Sql-server – How to enable a SQL Server 2012 login to create, alter and drop the created databases

First you have to

Create Login with create database permission. Note that the login cannot drop any databases.

USE [master]
GO

CREATE LOGIN [Kin] WITH PASSWORD=N'somePassw0rd', DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
use [master]
GO
-- Grant create any database, but not drop or alter any database
 GRANT CREATE ANY DATABASE TO [Kin]
GO

Map the login to the database that you want to have permission to alter or drop.

-- for drop database, you just need db_owner
USE [test_drop]
GO
CREATE USER [Kin] FOR LOGIN [Kin] 
 GO

Add the user to the db_owner role.

  EXEC sp_addrolemember 'db_owner', 'Kin'

Now test it for create / alter and drop :

EXECUTE AS LOGIN = 'Kin'

create database test_drop


EXECUTE AS LOGIN = 'Kin'
use test_drop

create table test (fname char(1))


EXECUTE AS LOGIN = 'Kin'
select * from test

EXECUTE AS LOGIN = 'Kin'
alter database test_drop 
set offline

EXECUTE AS LOGIN = 'Kin'
alter database test_drop 
set online

EXECUTE AS LOGIN = 'Kin'
drop database [test_drop]

Try to drop some other db :

EXECUTE AS LOGIN = 'Kin'
alter database [test_kin] 
set online

Below is the error :

Msg 5011, Level 14, State 9, Line 2 User does not have permission to alter database 'test_kin', the database does not exist, or the database is not in a state that allows access checks. Msg 5069, Level 16, State 1, Line 2 ALTER DATABASE statement failed.

Best Answer

Related Solutions

Postgresql – Created user can access all databases in PostgreSQL without any grants

Sql-server – How to enable a SQL Server 2012 login to create, alter and drop the created databases

Related Question