Postgres is formed from:
shell# .configure shell# gmake shell# gmake install
The previous steps install Postgres under the default
/usr/local/pgsql directory. Also in order to have a working database system (in consequence databases ), you will need to execute the
initdb command which creates a database system.
Postgres will need only one operating system account to make an installation, although this account can be any name, it is recommended that this user be named
postgres , it is this user who will: create the database system, be the owner of the postmaster process and own the database system files which compose the core of the system (database files, system catalogs...), this user is commonly called the postgres superuser and would be analoguous to the oracle/dba user in Oracle8i.
Within every database system there is a file called pg_hba.conf, this text file controls access to every piece of the database system, it is possible to specify a great deal of access controls like : IP Address origin, kerbros authentication, database..etc.(Note: It mentions database, recall that a databasesystem is composed of various databases).
Examples of this file:
#This following line allows any user on the local host to connect to any database using any username. host all 127.0.0.1 255.255.255.255 trust #This next line indicates that any user (client) coming from IP address 192.168.93.x #is allowed access to the trabajos database, (note that this is only a database, not the database system), #and will be authenticated via the password file name passwd located under the database system home ($PGDATA). host trabajos 192.168.93.0 255.255.255.0 password passwd
The passwd file
As mentioned earlier the pg_hba.conf can make use of a password file that will reside in the database system home ($PGDATA), this file by convention only is named
passwd and has entries similar to the
/etc/passwd on *nix systems. While you can edit this file with a text editor, Postgres provides the
pg_passwd to manipulate this file,in order to use this utility you must be in your database system home directory ($PGDATA),as
pg_passwd attempts to write to the current directory.
Depending on the client you are using to connect to postgres (psql or web-driver), you will need to feed parameters in a certain manner(e.g.:
psql uses the
-U flag to indicate which user to connect as.)
The pg_shadow table
pg_hba.conf provides a first level of access control, however there is a table named pg_shadow which contains an extra level of granularity for the whole database system, this granualrity comes in the form of letting a user create databases in the database system , create other users to connect to a database; this would be the DBA_USERS table in Oracle with only two types of access privileges(create new user,create database).
To add a new user to the pg_shadow table the command createuser is used, this command can be issued directly from the shell if the user has the appropriate privileges; another possbilitiy to add a user to the
pg_shadow table is with the create user in
psql , this allows for finer settings for the user like: password expiration, specific userid and group membership.