Locking Down Permissions in PostgreSQL and Redshift

PUBLISHED ON OCT 2, 2017 / 5 MIN READ — PROGRAMMING

Creating users in PostgreSQL (and by extension Redshift) that have exactly the permissions you want is, surprisingly, a difficult task. Unbeknownst to many, PostgreSQL users are automatically granted permissions due to their membership in a built-in role called PUBLIC (where a role can, in this context, be thought of as a group of users). The PUBLIC role comes with several default permissions, some of which are problematic if you want to create, for example, a read-only user.

Setup for Examples

We’ll demonstrate the built-in privileges with examples. All of the following were performed on a fresh PostgreSQL 9.3 install, but they apply to Redshift as well (with any exceptions pointed out below). To begin, first create a new user and some tables:

create role mynewuser with login password 'password';
create schema myschema;
create table myschema.foo (x integer);
insert into myschema.foo values (1), (2), (3);
create table public.bar (x integer);
insert into public.bar values (1), (2), (3);

Database / Connection Privileges

Now let’s get into what privilegs are actually granted to the PUBLIC role. For databases, these privileges are:

  • CONNECT
  • TEMPORARY

(For Redshift and older PostgreSQL versions (before version 8.1), the CONNECT privilege does not apply. The CONNECT privilege was added in PostgreSQL version 8.2.)

The first privilege, CONNECT, is one you might not have known could be granted or revoked in PostgreSQL. This privilege controls whether the user can actually login (but it’s not the only thing that controls the ability to log in; see the documentation on the pg_hba.conf file for more details):

$ psql --host 127.0.0.1 --user mynewuser --password postgres
psql (9.3.18)
SSL connection (cipher: DHE-RSA-AES256-GCM-SHA384, bits: 256)
Type "help" for help.

postgres=>

If PUBLIC were not granted this privilege, the above would look more like:

$ psql --host 127.0.0.1 --user mynewuser --password postgres
psql: FATAL:  permission denied for database "postgres"
DETAIL:  User does not have CONNECT privilege.

You would instead have to explicitly grant CONNECT on the database before the user could log in.

The second default privilege, TEMPORARY, gives users the right to create temporary tables, ie. the right to do:

postgres=> create temp table mytemp (x intetger);

Schema Privileges

In addition to permissions on the database itself, the PUBLIC role is also given rights on the public schema. These rights are:

  • USAGE
  • CREATE

The USAGE privilege is the basic privilege a user must have before they can do anything with the tables inside the schema. In other words, even if you are granted SELECT on the tables inside schema x, you will be denied access unless USAGE is given on the schema as well. This privilege is given to PUBLIC on the public schema inside every database. This is demonstrated with:

postgres=> select * from myschema.foo ;
ERROR:  permission denied for schema myschema
LINE 1: select * from myschema.foo ;
postgres=> select * from public.bar;
ERROR:  permission denied for relation bar

The user doesn’t have access to either table, but the error message for schema myschema was different. In the public schema, permission was denied to the table, but in myschema, permission was denied to the whole schema.

In fact, on the public schema, PostgreSQL not only gives usage, but also the ability to create tables:

postgres=> create table newusertable (x integer);
CREATE TABLE

Yikes! This one is a bit nasty if you ever want to create read-only users.

The above privileges are not mentioned in the PostgreSQL documentation, as far as I can see. I emailed the PostgreSQL mailing list about this, but at the time of this writing, am still waiting for the post to be approved.

Additional Privileges

The PostgreSQL documentation on the GRANT statement (link) points out that PUBLIC also gets:

EXECUTE privilege for functions; and USAGE privilege for languages

I won’t go into depth on these, because these permissions do not affect the user’s ability to see or modify data.

Lock it Down

The main problem with locking down these privileges is that any existing users may be relying on the grants that PostgreSQL automatically gives them. Unfortunately there is no way to revoke these privileges without affecting all users.

A strategy you might take would be to explicitly grant these permissions to all users in your database and then revoke it from PUBLIC. Or, create a new role that has these permissions, add all users to it, and then revoke. Both of these strategies could be tricky, as you also have to be careful about default privileges in your databases.

Revoking from PUBLIC

If you want to go ahead and revoke these grants from PUBLIC anyway, doing so is pretty straightforward now that we know what to revoke:

revoke connect, temporary on database postgres from PUBLIC;
revoke usage, create on schema public from PUBLIC;

Note that this only applied to the postgres database, however. If you have more databases, you will have to apply the same statements to each of them.

The other caveat is that this doesn’t cover any additional privileges that you or another user have given to PUBLIC, but that is out of scope for this article. For now, you can be more aggressive with your revoke statements:

revoke all on database postgres from PUBLIC;
revoke all on schema public from PUBLIC;
revoke all on all tables in schema public from PUBLIC; -- repeat for all schemas

You can then explicitly grant what you need and the grants will only apply to those users or roles you wish. For example, to allow our example user to select from the public.bar table:

grant connect on database postgres to mynewuser;
grant usage on schema public to mynewuser;
grant select on table public.bar to mynewuser;

Now login as that user and try it out:

vagrant@vagrant-ubuntu-trusty-64:~$ psql --host 127.0.0.1 --user mynewuser --password postgres
psql (9.3.18)
SSL connection (cipher: DHE-RSA-AES256-GCM-SHA384, bits: 256)
Type "help" for help.

postgres=> select * from public.bar;
 x
---
 1
 2
 3
(3 rows)

Protecting New Databases Going Forward

The revoke statements above do not protect any new databases you create. The good news is that you can begin locking down new databases whether or not you want to run the above revoke statements. Your SQL whenever you create a database becomes:

create database shinynewdb;
grant connect on database shinynewdb to postgres; -- your current user
revoke connect, temporary on database shinynewdb from PUBLIC;
-- now connect to the new db to change the schema privileges
\c shinynewdb
revoke usage, create on schema public from PUBLIC;