3

I have an issue while trying to create a readonly user in PostgreSQL.

I want user ckan_default to create tables in database datastore_default. And I want readonlyuser to read tables created by ckan_default in datastore_default. I want this rule to apply to tables that will be created by ckan_default, not only the ones already created.

GRANT CONNECT ON DATABASE datastore_default TO readonlyuser;
GRANT USAGE ON SCHEMA public TO readonlyuser;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonlyuser;
ALTER DEFAULT PRIVILEGES FOR USER readonlyuser IN SCHEMA public GRANT SELECT ON TABLES TO readonlyuser;

acutally the complete script I ran was 

sudo -u postgres psql postgres -f /tmp/set_permissions.sql

--content of /tmp/set_permissions.sql
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE USAGE ON SCHEMA public FROM PUBLIC;
GRANT CREATE ON SCHEMA public TO ckan_default;
GRANT USAGE ON SCHEMA public TO ckan_default;
GRANT CREATE ON SCHEMA public TO ckan_default;
GRANT USAGE ON SCHEMA public TO ckan_default;
REVOKE CONNECT ON DATABASE ckan_default FROM readonlyuser;
GRANT CONNECT ON DATABASE datastore_default TO readonlyuser;
GRANT USAGE ON SCHEMA public TO readonlyuser;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonlyuser;
ALTER DEFAULT PRIVILEGES FOR USER ckan_default IN SCHEMA public
   GRANT SELECT ON TABLES TO readonlyuser;

I read this post How do you create a read-only user in PostgreSQL and I either tried it or it does not work for tables that will be created.

For more details and the complete script check out the pastebin http://pastebin.com/psDifhwd

ddreian
  • 1,766
  • 5
  • 21
  • 29
  • I studied your pastebin link, actual granting of privileges happens in another pastebin referenced in a comment in the first: http://pastebin.com/FDnt6EhS. The content of this file should be ***in*** your question. Links die and then your question stops making sense. Please edit. – Erwin Brandstetter Sep 03 '14 at 23:57

1 Answers1

0

My suspicion is that the search_path of all users is set to the typical default

"$user",public

Which means _foo without schema-qualification would resolve to $user._foo:

  • ckan_default._foo for ckan_default, and
  • readonlyuser._foo for readonlyuser.

Repeat your test with:

sudo -u postgres psql -d datastore_default -U ckan_default -c 'CREATE TABLE public._foo()'
sudo -u postgres psql -d datastore_default -U ckan_default -c 'SELECT * FROM public._foo'
sudo -u postgres psql -d datastore_default -U readonlyuser -c 'SELECT * FROM public._foo'

If that's the case, consider changing the default search_path for each user:

ALTER ROLE ckan_default SET search_path=public;
ALTER ROLE readonlyuser SET search_path=public;

Or whatever suits your needs.
Also consider changing the default setting in postgresql.conf.

More related answers:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thank you for your help, but it appears that it is not a search_path issue. ckan_default sees public._foo but readonlyuser does not. `$ sudo -u postgres psql -d datastore_default -U readonlyuser -c 'SELECT * FROM public._foo' ERROR: permission denied for schema public LINE 1: SELECT * FROM public._foo ^` – ddreian Sep 04 '14 at 16:45
  • if I run \z with users postgres or ckan_default I get this `sudo -u postgres psql -d datastore_default -U` `ckan_default=arwdDxt/ckan_default+| datastore_default=r/ckan_default +| readonlyuser=r/ckan_default | ckan_default=arwdDxt/ckan_default+| datastore_default=r/ckan_default +| readonlyuser=r/ckan_default | ` But readonlyuser gets an empty row. sudo -u postgres psql -d readonlyuser -U More details here http://pastebin.com/CVdHURPJ Any clue? – ddreian Sep 04 '14 at 16:57
  • `sudo -u postgres dropuser readonlyuser` It appears that there are some privileges linked to readonlyuser anyway `dropuser: removal of role "readonlyuser" failed: ERROR: role "readonlyuser" cannot be dropped because some objects depend on it DETAIL: privileges for default privileges on new relations belonging to role postgres in schema public privileges for default privileges on new relations belonging to role ckan_default in schema public privileges for schema public privileges for database datastore_default 4 objects in database datastore_default` – ddreian Sep 05 '14 at 06:09