Previous 1 2 3 Next 41 Replies Latest reply: Apr 5, 2013 11:36 AM by matthewfromdanvers Go to original post
  • daefromlos angeles Level 1 (5 points)

    Creating a role is just creating a user inside Postgres SQL

    pgAdmin is not installed by default

  • Tim Bloom1 Level 1 (110 points)

    I understand that pgAdmin isn't installed, as I've downloaded it and also Navicat.  But I wouldn't know how to properly create any role in PostgreSQL if I can't login

  • daefromlos angeles Level 1 (5 points)

    You login using

    sudo -u _postgres psql template1

    Untitled.png

  • bitsurfing Level 1 (0 points)

    Hi Tim,

     

    To answer your question about creating a role in postgresql. This can be accomplished in psql (you don't need pgAdmin). To do so:

     

    Start psql with the _postgres user which already has a role assigned:

    sudo -u _postgres psql template1

    (daefromlos angeles has graciously included a screen-shot above)

     

    Then once in, create a new role:

    create role <MYUSERNAME> with login createdb

     

    The <MYUSERNAME> would be whatever you log-in as.

     

    You can see all of the options by typing in:

    \h create role

     

    You can now log out using \q and re-log in using psql or Navicat.

    When logging-in, if no parameters for the database are specified then it will assume that your username is the default database. As a convenience use the createdb command to create a brand new database (instead of psql).

  • MikeLitton Level 1 (0 points)

    This is actually not the right answer if you installed using mac ports. The reason is that the /usr/bin/psql application is not the right tool.

     

    Run which psql and remove it if it's in /usr/bin/psql. Then open a new terminal and run which psql. It will then be the MacPorts psql instead.

     

    It just works™

  • Androidi Level 1 (0 points)

    Hi guys! Just figured out how to enable TCP access to PostgreSQL!

     

    by executing the following in Terminal:

    ps ax | grep postgres

     

    revealed that there are some mysterious command line parameters given to PostgreSQL at start. The most concerning is the "-c listen_addresses=" which effectively turns off all TCP listening!

     

    By trying to kill the postgres process both nicely and forcefully didn't help - the ******* respawns! I tried:

    sudo -u _postgres pg_ctl stop -D /var/pgsql

    and

    sudo kill {enter postgres pid here}

     

    So... I Googled a bit to find out about what happens at OSX's bootup. Found out that there's a system called "launchd" since Tiger (10.4), which handles all the extensions loading, etc.: http://en.wikipedia.org/wiki/Launchd

     

    After initial system boot, the launchd goes over all files found in /System/Library/LaunchDaemons. There's a file called org.postgresql.postgres.plist which includes all the command line parameters. By modifying the parameters, and restarting system, TCP connection to PostgreSQL works!

     

    Also remember to add all IP addresses allowed to access PostgreSQL to /var/pgsql/pg_hba.conf.

     

    Btw. it's a good idea to add e.g. a user named postgres to access the db, because user _postgres uses the same password as system admin...

     

     

    Cheers,

    /Sami

  • slv1970 Level 1 (0 points)

    tell me where are the files postgresql.conf pg_hba.conf

  • Androidi Level 1 (0 points)

    In /var/pgsql.

     

    But check my reply to Yansolo; PostgreSQL initializes with some command line parameters, which override changes to postgresql.conf.

  • slv1970 Level 1 (0 points)

    to connect to another computer, I did

     

    1. edit file /system/library/LaunchDaemons/org.postgresql.postgres.plist

     

         deteteв lines

                    <string>-c</string>

                   <string>listen_addresses=</string>

    2. edit file /var/pgsql/pg_hba.conf

     

         add lines

                   host    all             all             192.168.0.0/24          trust

    3. edit file /var/pgsql/postgresql.conf

        

         edit lines

                   #listen_addresses = 'localhost'

                   listen_addresses = '*'

                  

                   #port = 5432

                   port = 5432

    4. restart mac os x server

     

    connect ok

  • Andy MPC Level 1 (0 points)

    I managed to open an TCP/IP port with the embedded PostgreSQL server so that I can connect to it with PGAdmin using localhost:5432. You need to open file "/System/Library/LaunchDaemons/org.postgresql.postgres.plist" and change the "listen_addresses" property. Then restart (serveradmin stop postgres, serveradmin start postgres) and you have the port open. Try using: telnet localhost 5432.

     

    You can find more info here:

    http://blogs.madplanet.com/2011/08/06/using-embedded-lion-server-postgresql-data base/

     

    - Andy

  • davidh Level 4 (1,890 points)

    The blog post linked to above is the way to go.

     

    Please do read about launchd if you're new to it, as it's been in use since 10.4, so just over 6 years now :-)

     

    Recommended reading:

     

    http://www.afp548.com/article.php?story=20050620071558293

     

    http://www.mactech.com/articles/mactech/Vol.25/25.09/2509MacEnterprise-launchdfo rLunch/index.html

     

    https://developer.apple.com/library/mac/#documentation/MacOSX/Conceptual/BPSyste mStartup/Chapters/Introduction.html

  • mikfromsooke Level 1 (0 points)

    Thanks for the post this worked great!

  • YC Hui Level 1 (0 points)

    Thank you very much~~i am trying to work it out now~~

     

    and the link become this one:

     

    http://blogs.madplanet.com/?p=110

  • YC Hui Level 1 (0 points)

    hi all!!!! it work so great!!!!!!!!!!!!! thanks every of you!!!!! love!!!!

  • Evian Level 1 (0 points)

    I tried. It is not working for me. Interesting becuase:

    1) my server setup is "standard"; no manual changes. All services working

    2) not working using localhost on the server machine

     

    Any idea where to look after or what may I doing wrong?