Postgresql Tips

Connect to a remote postgresql server

In order to connect to a remote postgres server you need to first edit the

/etc/postgresql/9.3/main/postgresql.conf

set the option listen_address as * or any ip address/hostname you want to login form

listen_addresses = “*”

next you need to set the client authentication settings in

/etc/postgresql/9.3/main/pg_hba.conf

add the following line to login from anywhere or set the specific ip/subnet

host all all 0.0.0.0/0 md5

Now restart postgresql server and login using the command below

$ psql -h example.com -p 5432 -U postgres -W <databasename>

Other important tips

Create new user

CREATE USER user_name;

Change permissions

GRANT ALL|SUPERUSER|CREATE ON db_name TO user_name;

Change owner of a database

ALTER DATABASE db_name OWNER TO new_user_name;

Change password of a user

ALTER USER user_name WITH ENCRYPTED PASSWORD ‘new password’;