Re: Permission Problems:-)? - Mailing list pgsql-general

On Tuesday 27 January 2004 06:41 am, Tom Lane wrote:
> Jerome Lyles <susemail@hawaii.rr.com> writes:
> > I'm trying to build a database.  But none of the users I try to use
> > works.
>
> I think you are confused about the distinction between Postgres users
> and Unix users.  The set of user names known to the database doesn't
> necessarily have anything to do with the set of login names available on
> the local system.  (If you think about cases where the database is used
> by remote users, you'll see why this is a good idea.)  If you want to
> use database user names that match up with local user names, you have to
> specifically create those database user names with the createuser
> script.  Initially the only user name known to the database is the
> "superuser", who has the right to create more database users.

You're correct, I was confused about this point.  As soon as I finish here I
will use the createuser script.  I wish I had read your email before I sent
my last one, I think your explanation and createuser are the answer to my
question in that email.

> Now in order to use createuser you need to connect as the database
>
> superuser, which seems to be a tad messed up in itself:
> > I as postgresql user doesn't work:
> > :~> createdb mydb
> >
> > createdb: could not connect to database template1: FATAL:  user
> > "mypostgre" does not exist
>
> The name given to the initial database superuser is the name of the
> account that ran initdb.  It kinda looks like you changed your mind
> about the name of the account owning the postgres installation --- you
> seem now to be trying to connect as "mypostgre" but the database has no
> such username.

Correct again.  I tried to set up and use the initial database superuser
(mypostgre).  I didn't realise that apt had set up the database superuser
(postgres) when it installed postgresql.  When I did realize it I was able to
use it to set up a database.
>
> > I can only start postgresql as root.
>
> I don't think so --- the postmaster will actively refuse to start if you
> try to run it as root.  Better take another look at exactly what's
> happening.

Well two out of three isn't bad:-).  I'll let you be the judge:
Running the postmaster as root:

adriel@linux:~> su
Password:
linux:/home/adriel # /etc/init.d/postgresql start
Starting PostgreSQL
done
linux:/home/adriel #

I then created a database as the database superuser:

postgres@linux:~> createdb mydb2
CREATE DATABASE

Here is some additional information:

postgres@linux:~> ps auxw | grep postgres
postgres 15646  0.0  0.1  2664 1136 pts/1    S    12:13   0:00 su - postgres
postgres 15647  0.0  0.1  3216 2040 pts/1    S    12:13   0:00 -bash
postgres 18973  0.0  0.2 17960 2296 pts/3    S    14:47
0:00 /usr/bin/postmaster -D /var/lib/pgsql/data
postgres 18975  0.0  0.2  8760 2072 pts/3    S    14:47   0:00 postgres: stats
buffer process
postgres 18976  0.0  0.2  7768 2120 pts/3    S    14:47   0:00 postgres: stats
collector process
postgres 18986  0.0  0.0  2668  708 pts/1    R    14:49   0:00 ps auxw
postgres 18987  0.0  0.0  1828  608 pts/1    R    14:49   0:00 grep postgres
postgres@linux:~>

The only difference that I noticed between the root postmaster and the
postgres postmaster is that the postgres postmaster demanded a password
before opening.
Now to create some users:-).
Thanks,
Jerome