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.
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;
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs