Postgresql – Unable to create read-only user in PostgreSQL 9.5

postgresqlread-only-database

I seem to be having trouble creating a read-only user for a database in a fresh install of PostgreSQL 9.5 with PostGIS 2.2 on Ubuntu 16.04. Here is what I've done so far:

Ran this,

CREATE ROLE dbowner LOGIN ENCRYPTED PASSWORD 'dbownerpassword';
CREATE DATABASE thedb WITH OWNER dbowner;
GRANT ALL PRIVILEGES ON DATABASE thedb TO dbowner;
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;

GRANT ALL ON ALL TABLES IN SCHEMA public to dbowner;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public to dbowner;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA public to dbowner;

CREATE ROLE readonly LOGIN ENCRYPTED PASSWORD 'ropassword';
GRANT CONNECT ON DATABASE thedb TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO readonly;

Then I restarted,
sudo service postgresql restart

Now,

psql -h localhost -U dbowner thedb
thedb=> CREATE TABLE testtable(col1 VARCHAR(100));
CREATE TABLE
thedb=> INSERT INTO testtable values('123');
INSERT 0 1
thedb=> SELECT * FROM testtable;
 col1
------
 123
(1 row)

psql -h localhost -U readonly thedb
thedb=> SELECT * FROM testtable;
ERROR:  permission denied for relation testtable

pg_hba.conf already has local all all md5 and host all all 127.0.0.1/32 md5. Anything else that I might be missing?

Best Answer

The question doesn't specify what user executed the following statement:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

but based on the surrounding code, let's guess it's postgres. That's the reason why readonly gets a permission denied trying to read it: it is the future creator of the table, dbowner, that should execute this statement. (to be sure, note that you should also mention to what database you're connected when running the mentioned commands).

As stated by the doc (emphasis mine) at https://www.postgresql.org/docs/9.5/static/sql-alterdefaultprivileges.html :

You can change default privileges only for objects that will be created by yourself or by roles that you are a member of.