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.
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);
Now let’s get into what privilegs are actually granted to the PUBLIC role.
For databases, these privileges are:
CONNECTTEMPORARY(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);
In addition to permissions on the database itself, the PUBLIC role is also
given rights on the public schema. These rights are:
USAGECREATEThe 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.
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.
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.
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)
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;