So… After the first part (Link) where we talk about the installation,
the next step would be to create root user and to change postgres and root password.
[root@XTdata init.d]# su postgres bash-3.2$ createuser -s root bash-3.2$ createdb root --owner=root exit [root@XTdata data]# psql psql (9.2.4) Type "help" for help. root=# ALTER USER postgres WITH PASSWORD 'SomePAASWDe348'; ALTER ROLE root=# ALTER USER root WITH PASSWORD 'SomePAASWDe3489898'; ALTER ROLE root=# \q |
Now, the next step would be to allow remote connections.
postgresql.conf is the main PostgreSQL config file. To be able to reach the server remotely, find the commented line
#listen_addresses = 'localhost' # what IP address(es) to listen on; |
uncomment the line and replace the localhost with the servers IP address. (or replace it with * which means – listen on all interfaces)
listen_addresses = '*' # what IP address(es) to listen on; |
PostgreSQL, by default, refuses all connections it receives from any remote host. The remote hosts can be controled via pg_hba.conf file (located in the same dir like postgresql.conf).
Add the next line
host all all 192.168.10.57/32 md5 |
where 192.168.10.57 is the remote host IP address.
Also, you can allow any host by replacing the 192.168.10.57/32 with 0.0.0.0/0.
The line syntax is
local DATABASE USER METHOD [OPTIONS] host DATABASE USER ADDRESS METHOD [OPTIONS] hostssl DATABASE USER ADDRESS METHOD [OPTIONS] hostnossl DATABASE USER ADDRESS METHOD [OPTIONS] |
which is documented inside the pg_hba.conf. Save the file and restart the server.
I prefer the pgAdmin III tool which can be used for remote management. Fire it up, select File, Add Server… Enter name, host, Username and password.
This should be enough for now…
WHOA!!! You are not supposed to access Postgres as root. Things are set up that way for a reason. The naming scheme is set up a certain way for a reason as well. The documentation covers this — I suggest you read up on it. The Postgres docs are good. So good, in fact, that there aren’t many tutorials out there covering the basics (most Postgres tutorials cover rather esoteric aspects of relational theory or data design).
The way to initialize Postgres is:
1- “su -” to root for installation (obviously)
2- From root “su postgres” to become the postgres user. This is where you perform initialization commands like “initdb” and “createuser [your_normal_username]”.
3- exit su back to root, exit su again back to your normal non-root user account, and “psql” in to the database. If you used “createuser” to make yourself a superuser within Postgres, then you can do whatever you want from then on.
Postgres can, by itself, be programmed to serve as an application server if you know what you’re doing. It maintains its own internal access controls independent (but can be parallel to) the host operating system.
When it comes to non-trivial data schemas the difference between MySQL and Postgres are so vast they aren’t really worth discussing.
Thanks for your comment.
Just like I sad, I want to learn a little about PostgreSQL and as I noticed on other blogs, the comments about naming scheme was always the same (mostly).
This post stuck in my head because you’re going to so much trouble swimming against the current. Postgres is easy to get started on, but it seems like those who already know, already know, so there isn’t much information outside the official docs (but like I wrote above, read the docs — they are amazing).
I checked my history to see what I did when I upgraded to 9.2. Here is the result:
[ceverett@taco ~]$ su –
[root@taco ~]# service postgresql-9.1 stop
[root@taco ~]# chkconfig postgresql-9.1 off
[root@taco ~]# service postgresql-9.2 initdb
[root@taco ~]# service postgresql-9.2 start
[root@taco ~]# chkconfig postgresql-9.2 on
[root@taco ~]# su – postgres
-bash-4.1$ psql
postgres=# create role ceverett superuser;
postgres=# create database ceverett owner ceverett;
postgres=# \q
-bash-4.1$ exit
[root@taco ~]# exit
[ceverett@taco ~]$ psql
If you want to do anything other than locally authenticate using your host credentials then you will need to edit
/var/lib/pgsql/9.2/data/pg_hba.confand tell postgres whatever you need it to know (and then restart the server daemon). Other than these kinds of changes (access issues, memory limits, etc.) you can administer Postgres from your normal user account within psql now.