Thread: CREATE DATABASE WITH OWNER '??';
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
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
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
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
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
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
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
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
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 ...
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
On Wed, 10 May 2000, Peter Eisentraut wrote: > On Tue, 9 May 2000, The Hermit Hacker wrote: > > > CREATE DATABASE WITH OWNER? > > Databases already have owners, so this syntax extensions (not a bad idea > IMHO) isn't going to change the substance of things really. tables have owners .. right now, as far as I can tell, the database itself doesn't have an owner. Unless I'm missing something, any user that can connect to a database can create new tables in that database, regardless of what they can do to the existing tables in that database ...
On Wed, 10 May 2000, Peter Eisentraut wrote:
> On Tue, 9 May 2000, Tom Lane wrote:
>
> > > 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.
>
> Let's say your database is password protected (as was Marc's original
> setup). Somebody connects. There's no password set up for him (because
> he didn't have access to modify pg_hba.conf). What do you do? Leave
> him through anyway to check whether he's a superuser or database
> owner? How do you know *who* he is at all without doing a password
> check?
Huh? Since when are passwords setup in pg_hba.conf? If a database has
password set, and user doesn't have password to make the initial connect,
they should be refused at the postmaster end of things, and never get to
the backend. If they have a passwd and its right, then they should get
into the backend, where the first check is to see if they are allowed to
access the database itself ...
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
The Hermit Hacker wrote: > > On Wed, 10 May 2000, Peter Eisentraut wrote: > > > On Tue, 9 May 2000, The Hermit Hacker wrote: > > > > > CREATE DATABASE WITH OWNER? > > > > Databases already have owners, so this syntax extensions (not a bad idea > > IMHO) isn't going to change the substance of things really. > > tables have owners .. right now, as far as I can tell, the database itself > doesn't have an owner. Unless I'm missing something, any user that can > connect to a database can create new tables in that database, regardless > of what they can do to the existing tables in that database ... hannu=# select * from pg_database; datname | datdba | encoding | datpath -----------+--------+----------+-----------template1 | 501 | 0 | template1hannu | 501 | 0 | hannu (2 rows) I'm pretty sure that the datdba field is the owner id. That anyone is allowed to do anything is another matter. ----------------- Hannu
The Hermit Hacker writes: > tables have owners .. right now, as far as I can tell, the database itself > doesn't have an owner. peter ~/pg-install/bin$ ./psql -lList of databasesDatabase | Owner -----------+-------peter | petertemplate1 | peter (2 rows) > Unless I'm missing something, any user that can connect to a database > can create new tables in that database, regardless of what they can do > to the existing tables in that database ... That is true, but IMHO schemas are are much better way to handle that. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
The Hermit Hacker writes: > Huh? Since when are passwords setup in pg_hba.conf? The fact that password authenticaton is to be used for a given (set of) databases is communicated through pg_hba.conf. Furthermore, the use of a separate password file can be specified in pg_hba.conf (which is currently the only way to disallow access to certain users when using passwords). > If a database has password set, A database doesn't "have a password set", a user does. A database may be accessible through password authentication (see above). > and user doesn't have password to make the initial connect, they > should be refused at the postmaster end of things, and never get to > the backend. But this is not what you said earlier. You said superusers or database owners should get through either way. I said that doesn't work under this model because you must pass password authentication first to be allowed to pose as a particular user. > If they have a passwd and its right, then they should get into the > backend, where the first check is to see if they are allowed to access > the database itself ... That makes a lot of sense but as I mentioned earlier you would have to start up backends for a much higher fraction of potential connections, to which you objected. Tom pointed out that trying to avoid this might be pretty pointless though. I concur. On the implementation side this would probably mean some shared system relation pg_dbaccess ( dbid, userid ). Then you could overload GRANT and REVOKE to act on this table. We could replace the database name field in pg_hba.conf with some special character (e.g., `-') to indicate "look in pg_dbaccess". I think I'd like that. It cleanly separates host-based authentication from database-based authorization... (On the other hand, what it doesn't accomplish is `use this auth. method only for these users' or `use this method, or failing that this other method'-- as people occasionally request -- but IMHO this is better left to something like PAM.) -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden