
I'm just looking at what it would take to add a certain level of security
to the databases that I run on my server(s) ... one of the big problems,
as I see it, is that we have a pretty poor way of restricting users
between them all ...

For instance, if I go into pg_hba.conf and make a database 'passwd' auth
only, then anyone that has a userid/passwd can connect to that database,
regardless ...

Now, if they don't have permissions on the *tables*, they can't do
anything with those tables, but they can still create new ones ...

Is there no way of setting permissions on the database itself, so that,
for instance, we'd have:

GRANT ALL ON DATABASE <database> TO <userid>;



Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Fri, 5 May 2000 23:02:13 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
CREATE DATABASE WITH OWNER '??';
The Hermit Hacker wrote:
> 
> I'm just looking at what it would take to add a certain level of security
> to the databases that I run on my server(s) ... one of the big problems,
> as I see it, is that we have a pretty poor way of restricting users
> between them all ...
> 
> For instance, if I go into pg_hba.conf and make a database 'passwd' auth
> only, then anyone that has a userid/passwd can connect to that database,
> regardless ...

IIRC Oracle has a basic right called CONNECT that a user must have in 
order to connect to database - It would nice if we could have this too

Has anyoune done some research what SQL92 says about what are rights 
that can be GRANTed ?

> Now, if they don't have permissions on the *tables*, they can't do
> anything with those tables, but they can still create new ones ...
> 
> Is there no way of setting permissions on the database itself, so that,
> for instance, we'd have:
> 
> GRANT ALL ON DATABASE <database> TO <userid>;

Or maybe 

GRANT {CREATE|DROP} TO <userid>;

GRANT CREATE {FUNCTION|LANGUAGE|TABLE|xxx} TO <userid>;

with optional ON DATABASE

AFAIK we don't have WITH GRANT OPTION for delegating GRANT rights
either.

------------
Hannu

Sat, 06 May 2000 11:53:00 +0300
Hannu Krosing <hannu@tm.ee>
Re: CREATE DATABASE WITH OWNER '??';
The Hermit Hacker writes:

> For instance, if I go into pg_hba.conf and make a database 'passwd' auth
> only, then anyone that has a userid/passwd can connect to that database,
> regardless ...

You need to make a separate password file for each database and then write
`passwd my_pw_file' in pg_hba.conf. Should be documented somewhere
there. Hmm, then you can't change your password with ALTER USER any
more. Bummer.


-- 
Peter Eisentraut                  Sernanders vï¿½g 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden


Sun, 7 May 2000 23:34:26 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: CREATE DATABASE WITH OWNER '??';
Peter Eisentraut <peter_e@gmx.net> writes:
> You need to make a separate password file for each database and then write
> `passwd my_pw_file' in pg_hba.conf. Should be documented somewhere
> there. Hmm, then you can't change your password with ALTER USER any
> more. Bummer.

Hmm, I had forgotten we had that feature.  Doesn't play too well with
ALTER USER, does it?  Cleaning this up probably ought to be on the TODO
list.

Rather than the separate pw files, maybe pg_shadow needs some kind of
provision for database-specific passwords ...

			regards, tom lane

Sun, 07 May 2000 18:06:12 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: CREATE DATABASE WITH OWNER '??'; 
On Sun, 7 May 2000, Peter Eisentraut wrote:

> The Hermit Hacker writes:
> 
> > For instance, if I go into pg_hba.conf and make a database 'passwd' auth
> > only, then anyone that has a userid/passwd can connect to that database,
> > regardless ...
> 
> You need to make a separate password file for each database and then write
> `passwd my_pw_file' in pg_hba.conf. Should be documented somewhere
> there. Hmm, then you can't change your password with ALTER USER any
> more. Bummer.

Correct, and it also makes the assumption that the pg-admin has access to
that machine...

If I give a second user 'create database' access, how does he add auth
privileges to that database?


Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Sun, 7 May 2000 20:46:39 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: CREATE DATABASE WITH OWNER '??';
Tom Lane writes:

> Rather than the separate pw files, maybe pg_shadow needs some kind of
> provision for database-specific passwords ...

Perhaps the issue is not so much having different passwords for each
database. I don't think this is necessarily a priority. (I think it would
be rather confusing that there would be one user and many passwords.)

The issue is that you can't say "Do password authentication, but only for
these users". It forces you to make separate password files. Perhaps we
could extend the syntax similar to this

| host	all	127.0.0.1	255.255.255.255	passwd	&user1,user2,user3

to mean "do password authentication using the pg_shadow passwords, but
only for the named users". (`&' would be some special character to
distinguish a list of users from a password file name.)


-- 
Peter Eisentraut                  Sernanders vï¿½g 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden


Tue, 9 May 2000 22:51:17 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: CREATE DATABASE WITH OWNER '??'; 
The Hermit Hacker writes:

> If I give a second user 'create database' access, how does he add auth
> privileges to that database?

There are no privileges on databases per se. There are only host-based
access privileges that may apply to all or some databases.

The difficulty with putting the control over this into the SQL environment
is two-fold:

1) You have a bootstrapping problem, because how are you going to set
these privileges if you can't connect?

2) You don't necessarily want to start up a new backend for every rogue
connection attempt.

So we're mostly stuck with what we have.


-- 
Peter Eisentraut                  Sernanders vï¿½g 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden


Tue, 9 May 2000 22:51:27 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: CREATE DATABASE WITH OWNER '??';
On Tue, 9 May 2000, Peter Eisentraut wrote:

> Tom Lane writes:
> 
> > Rather than the separate pw files, maybe pg_shadow needs some kind of
> > provision for database-specific passwords ...
> 
> Perhaps the issue is not so much having different passwords for each
> database. I don't think this is necessarily a priority. (I think it would
> be rather confusing that there would be one user and many passwords.)
> 
> The issue is that you can't say "Do password authentication, but only for
> these users". It forces you to make separate password files. Perhaps we
> could extend the syntax similar to this
> 
> | host	all	127.0.0.1	255.255.255.255	passwd	&user1,user2,user3
> 
> to mean "do password authentication using the pg_shadow passwords, but
> only for the named users". (`&' would be some special character to
> distinguish a list of users from a password file name.)

why can't we extend the whole 'grant table' syntax to a 'grant database'
one also?  as I see it, the owner of a database should be able to
grant/refuse connections to his database without having to go through the
DBA, which the above requires ...

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Tue, 9 May 2000 18:17:02 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: CREATE DATABASE WITH OWNER '??'; 
On Tue, 9 May 2000, Peter Eisentraut wrote:

> The Hermit Hacker writes:
> 
> > If I give a second user 'create database' access, how does he add auth
> > privileges to that database?
> 
> There are no privileges on databases per se. There are only host-based
> access privileges that may apply to all or some databases.
> 
> The difficulty with putting the control over this into the SQL environment
> is two-fold:
> 
> 1) You have a bootstrapping problem, because how are you going to set
> these privileges if you can't connect?

CREATE DATABASE WITH OWNER?

> 2) You don't necessarily want to start up a new backend for every rogue
> connection attempt.

Huh?  So, we want to start up a backend for each connection to a database,
regardless of whether or not that connection has permission to be in that
database?  Geez, let's think ... I'm not supposed to be in payrolls
database, but since all that is protected is the tables and not the
database itself, i can connect and just sit there, using up resources that
way ...



Tue, 9 May 2000 18:19:18 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: CREATE DATABASE WITH OWNER '??';
Peter Eisentraut <peter_e@gmx.net> writes:
> The difficulty with putting the control over this into the SQL environment
> is two-fold:

> 1) You have a bootstrapping problem, because how are you going to set
> these privileges if you can't connect?

Presumably the database superuser can connect to anything anytime, and
I'd also expect a newly-minted database to be preconfigured to let its
owner in.  (We could debate whether that privilege should be revokable,
but worst case is the owner goes to the dbadmin and admits what an idiot
he's been ;-).)  So this doesn't seem like a major objection.

> 2) You don't necessarily want to start up a new backend for every rogue
> connection attempt.

True, but we can still expect the postmaster to have done the same
amount of connection checking it does now, so the worst forms of abuse
can be prevented.  Bear in mind also that things like bad database name
are usually detected by the newly-started backend, so unless you run a
much tighter than normal pg_hba.conf, you have some exposure here
already.  I think having the backend reject an unauthorized connection
attempt from a user that the postmaster has previously checked to be
known to the database is not so bad.

			regards, tom lane

Tue, 09 May 2000 18:18:26 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: CREATE DATABASE WITH OWNER '??'; 