Unable to create read-only user in PostgreSQL 9.5

Question

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?

Answer 1

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.

Answer 2

So after Daniel's answer here is how I managed to create the read-only user. Indeed it was the dbowner that had to grant the privileges to the readonly user:

sudo -u postgres psql --command="CREATE ROLE dbowner LOGIN ENCRYPTED PASSWORD 'dbownerpassword' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;"
sudo -u postgres psql --command="CREATE DATABASE thedb WITH OWNER dbowner;"
sudo -u postgres psql --command="GRANT ALL PRIVILEGES ON DATABASE thedb TO dbowner;"
sudo -u postgres psql --command="CREATE EXTENSION postgis; CREATE EXTENSION postgis_topology;" thedb

sudo -u postgres psql --command="CREATE ROLE readonly LOGIN ENCRYPTED PASSWORD 'ropassword';"
psql -h localhost -U dbowner thedb
    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;
    CREATE TABLE testtable(col1 VARCHAR(100));
    INSERT INTO testtable values('123');
    SELECT * FROM testtable;

psql -h localhost -U readonly thedb
    thedb=> SELECT * FROM testtable;
     col1
    ------
     123
    (1 row)

Cheers!

Answer 3

psql -h localhost -U **postgres** thedb


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;


\q




psql -h localhost -U **readonly** thedb

select * from test;
 id | name 
----+------
  1 | KK
(1 row)

you need the super user to grant privileges to any user.

Note: GRANT USAGE ON SCHEMA public TO readonly; this is grant privileges on public schema in postgres database not public schema in thedb database. Each database has own public schema.