Re: Start postgresql for the first time
On Sun, 2004-08-01 at 18:00, Stephen Patterson wrote:
> On Sun, 01 Aug 2004 17:40:06 +0200, Bob Parnes wrote:
> > After using mysql for several years, I decided to try the testing
> > version of postgresql, but I cannot get started. I cannot log in to
> > create users, and I cannot create a database.
> > According to the documentation, the system contains a predefined user,
> > 'postgres'. However, when I run
> > psql -U postgres
> > I get the error message, 'No database specified'. When I run
> > psql -U postgres -l
> > I get the message, 'IDENT authentication failed for user "postgres"'.
> The defualt setup (see /etc/postgresql/pg_hba.conf) is to only allow users
> to access postgresql databases if their postgres username matches
> their linux username.
This is the policy for the Debian package. It represents (in my
opinion) the best compromise between initial security and convenience.
The upstream default has traditionally been full open access with no
security check. In either case, the access policy needs to be reviewed
by the local administrator.
> To create database user accounts, log in as root, su to postgres and
> run createuser. As postgres, you can run createdb to create empty databases.
To expand on that, use "su - postgres" to get the full environment.
Simply using "su postgres" would not do that.
When PostgreSQL is installed for the first time, there are two
databases: template0 and template1. template1 is the template for the
real databases that you will create; any changes you make to it will be
propagated into any new databases created thereafter (for instance, you
might choose to delete the public schema from template1). template0 is
a pristine copy of the database as created by initdb and is not normally
accessible. It can be named specifically as a template for creating a
database. When accessing PostgreSQL for the first time you would
specify template1 as the database:
psql [-d] template1
The Linux commands createdb and createuser are programs that connect to
the template1 database and wrap the SQL commands CREATE DATABASE and
CREATE USER, which you can use from inside psql.
If, when you create a user, you give him the right to create new users,
he is by definition an administrator or superuser in respect to
PostgreSQL, just as the user postgres is in the default installation.
In my own set-up I give myself that privilege and therefore very rarely
need to use the postgres login to access the database.
Oliver Elphick firstname.lastname@example.org
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
"And why call ye me, Lord, Lord, and do not the things
which I say?" Luke 6:46