RE: I remember why I suggested CREATE FUNCTION...AS NUL L

> > > > 2) Change pg_dump to walk through dependencies?
> > >
> > > The trouble with that is that dependency analysis is a
> monstrous job,
> > > and one that would make pg_dump even more fragile and
> backend-version-
> > > dependent than it is now.
> >
> > One way to get around that might be to make the dumping
> routine a part
> > of the backend instead of a frontend. This is what at least
> MS SQL does.
> > So I can do for example:
> >
> > BACKUP DATABASE mydb TO DISK 'c:\foo.dump'
> > or, if I want to send the backup directly to my backup program
> > BACKUP DATABASE mydb TO PIPE 'somepipe'
> >
> > Then to reload it I just do
> > RESTORE DATABASE mydb FROM DISK 'c:\foo.dump'
> >
> >
> > Doing this might also help with permissions issues, since the entire
> > process can be run inside tbe backend (and skip security
> checks at some
> > points, assuming that it was a user with backup permissions
> who started
> > the operation)?
>
> One issue with this comes to mind ... if I allocate X meg for
> a database,
> given Y meg extra for temp tables, pg_sort, etc ... if someone has the
> ability to dump to the server itself (assuming server is
> seperate from
> client machine), doesn't that run a major risk of filling up
> disk space
> awfully quick? For instance, the dba for that database
> decides to backup
> before and after making changes, or before/after a large
> update ... what
> sorts of checks/balances are you proposing to prevent disk
> space problems?
Hmm. I was actually not thinking of that part :-) More in the way of "if he
has permissions to dump the db, he shuold check for space".

But say there are different permissions for BACKUP TO DISK and BACKUP TO
PIPE (or whatever it would be called). So you could let somebody dump it
through a TCP/IP connection without needing to trust them with handling
local disk?

> also, this is gonig to require database level access controls,
> no? something we don't have right now ... so that too is
> going to have to
> be implemented ... basically, you're gonna want some sort of 'GRANT
> BACKUP to dba;' command so that there can be more then one person with
> complete access to the database, but only one person with access to
> initial a backup ...
Umm. Yeah, that's probably right. One could make a "cheap" solution by
saying only superuser can do it. But I guess there can be situations out
there where non-superusers need to run backups.. Hmm.

In MS SQL, the commands BACKUP and RESTORE can be executed by any member of
the database wide roles db_owner (who is like the postgres superuser, I
think, except on a per-database level) or the db_backupoperator role. I
don't know how SQL standards compliant the "roles" handling in MSSQL is, but
it is a very neat tool to work with :-)

Adding database level access controls probably wouldn't be the hardest part
of doing it, I think...

//Magnus