Sunday, September 4, 2011

Setting up User for PostgreSQL

I do not like using superuser 'postgres' as daily database management.
So I want to add my account name 'socrateos' as a postgresql user.

$ su postgres                                             # switch to user postgres
$ psql                                                    # launch psql client
psql (9.0.4)
postgres=# CREATE ROLE socrateos WITH CREATEDB CREATEROLE LOGIN PASSWORD 'secret'; # a new user
CREATE ROLE
postgres=# CREATE DATABASE testdb;                        # create a new database
CREATE DATABASE
postgres=# \q                                             # quit psql
$ exit                                                    # exit from user postgres 
exit
$ whoami                                                  # be sure I am back to myself
socrateos                                                 # yes, I am in my own account
$ psql testdb                                             # now login to postgresql as myself
testdb=> CREATE DATABASE mydb;                            # I can create a database
CREATE DATABASE
testdb=> \c mydb;                                         # connect to the newly created database
You are now connected to database "mydb".
mydb=> CREATE TABLE table1(id SERIAL PRIMARY KEY, name VARCHAR(32)); # test creating a table
CREATE TABLE
mydb=> INSERT INTO table1(name) VALUES('john');           # insert a row
INSERT 0 1
mydb=> INSERT INTO table1(name) VALUES('mary');           # insert another row
INSERT 0 1
mydb=> SELECT * FROM table1;                              # list all rows
 id | name 
----+------
  1 | john
  2 | mary
mydb=> \q                                                 # done with the test and quit psql

OK, so I think I am ready to manage PostgreSQL without using superuser 'postgres'.

No comments:

Post a Comment