I suggest you ...

support PostgreSQL schemas

PostGreSQL schema support is a management feature that would allow me to have my mail user DB parallel to the Davical DB and have joins against the mail db for user authentication.

36 votes
Vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    anonymous shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    7 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • Javi Merino commented  ·   ·  Flag as inappropriate

        I forgot to specify the target file at:

        # sed -i 's/SET search_path = public, pg_catalog;/SET search_path = davical;/'

        It should be:

        # sed -i 's/SET search_path = public, pg_catalog;/SET search_path = davical;/' davical_populate.sql

      • Javi Merino commented  ·   ·  Flag as inappropriate

        Note that there is no support in DAViCal for putting the tables into a non-default schema in the first place.

        So you can make initial work in a local database, in a public schema and then (as root)

        # su -c "pg_dump davical" postgres > davical_populate.sql
        # cat > davical_init.sql << 'EOSQL'
        CREATE ROLE davical_app;
        ALTER ROLE davical_app WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION;
        CREATE ROLE davical_dba;
        ALTER ROLE davical_dba WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION;

        CREATE SCHEMA davical AUTHORIZATION davical_dba;

        GRANT ALL ON SCHEMA davical TO davical_dba;
        GRANT ALL ON SCHEMA davical TO davical_app;

        ALTER USER davical_app SET search_path TO davical;
        ALTER USER davical_dba SET search_path TO davical;
        EOSQL

        # sed -i 's/SET search_path = public, pg_catalog;/SET search_path = davical;/'
        # sed -i 's/ON SCHEMA public/ON SCHEMA davical/' davical_populate.sql
        # sed -i 's/^\(ALTER [A-Z]* \)public\./\1davical./' davical_populate.sql
        # scp *.sql root@dbmachine:/var/lib/postgresql

        Then, at dbmachine as postgres:

        $ psql database
        \i davical_init.sql
        \connect - davical_dba
        -- be very careful to be davical_dba and not postgres at this stage
        \i davical_populate.sql

        Once you have tested everithing is alright (the schema has been correctly created, pg_hba.conf lets you connect and so on) you chan change the pg_connect string forgiving the schema in the destination database.

        Well, it has worked for me, but be very carefull as I have checked that all "sed -i" were doing what I wanted and not anything less nor more.

      • Christian Paminger commented  ·   ·  Flag as inappropriate

        No, the db-user has nothing to do with the db-schema. You have to set the "search path" if you want to use a different schema than public.
        I also need this feature and would be very happy, when I can set the schema in the config file. Now I have to patch the source-code every time when I update.

        Thanks a lot.

      • dahepe commented  ·   ·  Flag as inappropriate

        Shouldn't schema support come automatically by using different users for different purposes?

        That is if you create the DAViCal tables in a schema named davical and use a user named davical for the connection to the database, automatically the tables in the davical schema should be read first. Only if a specified table does not exist, the public schema should be consulted...

      • jeffk commented  ·   ·  Flag as inappropriate

        This would be helpful for integration with billing systems using Postgresql backends.

      Feedback and Knowledge Base