Results 1 to 2 of 2

Thread: set search path

  1. #1
    Join Date
    Feb 2011
    Posts
    1

    Unanswered: set search path

    I'm having a problem setting up some default search paths for non privileged user/roles on 8.4

    I have a database called (lets say) AA that will hold certain tables in a schema (lets call it ) XX
    This account is established and has no special privileges.

    I have some read only accounts that need to be created and be able to reference the schema xxx in the database account AA.
    From postgres I create the user and grant the following

    GRANT CONNECT ON DATABASE AA TO readonly _account

    I then want to set the readonly_account search path to schema xx.
    postgres=# ALTER USER reasdonly_account SET search_path to xx;
    for which I get the following
    NOTICE: schema "xx" does not exist

    If I try to use the following (database.schema )
    postgres=# ALTER USER readonly_account SET search_path to AA.XX;
    ERROR: syntax error at or near "."
    LINE 1: ALTER USER xyz SET search_path to AA.XX;

    Is there anyway that I can do this from one superuser account and not have to grant un-necessary privs to the other accounts?

    ^

  2. #2
    Join Date
    May 2008
    Posts
    277
    There doesn't appear to be anything apparently wrong with your commands; it would be better if you could post the actual commands that you're typing into your client.

    But note that search_path has nothing to do with access privileges. All it does it control which schemas are checked to find objects, and the order in which to check them. So if you have happen to have two tables with the same name in different schemas (public.foo and xx.foo), the search path will determine which table it finds. The search path can always be overidden by manually specifying the schema or simply changed by the user himself.

    To make a role "read-only" on a schema, then you need to do the following:
    Code:
    GRANT USAGE ON SCHEMA xx TO some_role;
    REVOKE CREATE ON SCHEMA xx FROM some_role;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •