3

I want to create a read-only user in PostgreSQL.

The intention is to have a publicly accessible data explorer, where users can write custom SQL queries.

I do this to create the user:

CREATE USER MyReadOnlyUser WITH ENCRYPTED PASSWORD 'MY_WEAK_PASSWORD';

GRANT ALL PRIVILEGES ON DATABASE "MY_DB_NAME" to MyReadOnlyUser;

GRANT ALL ON SCHEMA public TO MyReadOnlyUser; 
GRANT SELECT ON ALL TABLES IN SCHEMA public TO MyReadOnlyUser;

The intention is to give the user SELECT access to all tables, then revoke select access on the sensitive tables, and then the user can run custom queries with that db user, without any need for me to sanitize input.

Especially, he may not:

insert, delete, truncate, drop table, drop database, create table, create function/procedure, see/execute/create/drop stored procedure / functions. etc.

Sooo - now my question:
Why does this user have access to information_schema ?
It wasn't granted access to any views, and not to the schema information_schema either...

Stefan Steiger
  • 78,642
  • 66
  • 377
  • 442

1 Answers1

2

Why does this user have access to information_schema

I think select privilege is granted to PUBLIC by default on information_schema.

You should be able to do:

revoke all on all tables in schema information_schema from public;

You probably also need to revoke the select privilege on views/tables in the pg_catalog schema. But I'm not sure what this will break if e.g. psql or other client tools cannot access that information.

Stefan Steiger
  • 78,642
  • 66
  • 377
  • 442
  • 1
    Maybe it'd be useful to create a role, say `ro_user`, grant/revoke all the required permissions and then grant it to the users that should be read only. – vyegorov Jun 26 '12 at 08:53
  • 1
    @vyegorov: Here: http://gkoenig.wordpress.com/2010/01/22/create-a-read-only-user-in-postgres-for-all-relations-in-several-schemas/ – Stefan Steiger Jun 26 '12 at 10:52