PostgreSql tamed
This morning I had to do one of those thing I procrastinated for years: really put my head in administer a PostgreSql server to make some very very basic stuff. I mean, via console, without using PgAdmin. And I also managed to write down a memo about what is really needed to be done.
These are very, very, basically stuff. But I always forgot them and I have never learned to perform this task fine.My target is, from a scratch installation of Postgres, to create a database and to access it from an application of mine. Let's start.
sudo -u postgres psql postgres
we log into psql console. Eventually we can change the USER "postgres" password with
\password postgres
Now, let's try a couple of useful command that serious programmer knows "as gods gift", while I had to search on google this morning.
\du lists existing roles \l lists existing databases
Now, proceede with creation of a user. Be aware that the psql console has autocomplete, with TAB as usually.
CREATE USER della CREATEDB;
ALTER USER della WITH PASSWORD 'secret';
to change password in future, reference http://postgresql.mirrors-r-us.net/docs/8.2/static/sql-alterrole.html
now we have the user *AND* the role. These are different concepts in postgres. As far as I have understood, ROLE does not give you USER privileges, such as login. So one can ask, why do ever need a ROLE? Couse you can couple postgres roles with linux users. But is too much complicated stuff so we created a postgres USER that just works. Now, go on with database:
CREATE DATABASE newsautom OWNER della; GRANT ALL PRIVILEGES ON DATABASE newsautom to della;
That's all. Now we can acccess database "newsautom" with user "della" and password "secret" from our aplpication.
http://www.cyberciti.biz/faq/howto-add-postgresql-user-account/
