Re: Permission Problems:-)?

On Tuesday 27 January 2004 06:41 am, Tom Lane wrote:
> Jerome Lyles <susemail(at)hawaii(dot)rr(dot)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(at)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(at)linux:~> createdb mydb2
CREATE DATABASE

Here is some additional information:

postgres(at)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(at)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