Thread: BUG #13607: Creating "Readonly" User for public Shema.

The following bug has been logged on the website:

Bug reference:      13607
Logged by:          Ugur YILMAZ
Email address:      ugurlu2001@hotmail.com
PostgreSQL version: 9.4.0
Operating system:   Widows 10 Pro x64
Description:

I was create a readonly user on a testdb for my new project. There are two
user types at the project: postgres and readonly. The postgres user is
working in my program with admin rights and handling all concepts
(Transactions, Create, Drop, Maintenance etc...) . And I added readonly user
to my project; protect my tables against  sql injections. readonly user only
runs SELECT STATEMENTS for reports. But when I test some tables; There is no
protect of my tables against "ALTER TABLE" commands. I think; that is an
importand bug on PostgreSQL. I hope you can add "ALTER" keyword for
"GRANT/REVOKE" command to PostgreSQL The script at the abouve that I used :


With my best regards.

http://stackoverflow.com/questions/32352508/create-a-user-for-select-only-on-postgresql-restrict-alter-table

CREATE ROLE readonly LOGIN PASSWORD 'thePwd';

-- Existing objects

GRANT CONNECT ON DATABASE the_db 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;

ugurlu2001@hotmail.com writes:
> I was create a readonly user on a testdb for my new project. There are two
> user types at the project: postgres and readonly. The postgres user is
> working in my program with admin rights and handling all concepts
> (Transactions, Create, Drop, Maintenance etc...) . And I added readonly user
> to my project; protect my tables against  sql injections. readonly user only
> runs SELECT STATEMENTS for reports. But when I test some tables; There is no
> protect of my tables against "ALTER TABLE" commands. I think; that is an
> importand bug on PostgreSQL.

No, it's a mistake on your part.  Hard to tell exactly what, since you
provided no details of what you did to arrive at that conclusion; but
it's easy to show that this is rejected:

regression=# create table sample (f1 int);
CREATE TABLE
regression=# create user foo;
CREATE ROLE
regression=# grant select on table sample to foo;
GRANT
regression=# \c - foo
You are now connected to database "regression" as user "foo".
regression=> select * from sample;
 f1
----
(0 rows)

regression=> alter table sample add column f2 int;
ERROR:  must be owner of relation sample

If you've found a way for a non-superuser non-owner of a table to do ALTER
on it, please show an exact and complete example.

            regards, tom lane