NEBC Database Course 2008 Database Users And Security Backing-Up Data Tim Booth : tbooth@ceh.ac.uk
Overview Areas covered: Controlling who can connect ● Table-level privileges ● Defining user groups ● Remote connections to PostgreSQL running on Bio-Linux ● Backups ● Recovery ●
Importance of Security Unless you are running a major, multi-user database, some of the security facilities are overkill. However, you should consider the following: As the super-user, you are only a typo away from disaster. ● DROP TABLE can't be undone! If you are considering public browsing of the database, you ● should at least have a read-only account. Is there sensitive/unpublished information in the database? ●
Creating Users Administrative users may add other users (aka user roles). In PostgreSQL this is done either by issuing an SQL command or with the createuser command-line program. SQL version: note that everything after the first line is optional. CREATE USER goldie WITH ENCRYPTED PASSWORD 'n14hcniK001' CREATEDB NOCREATEUSER VALID UNTIL '16 Mar 2004'
User Privileges A administrator or 'super-user' has full rights to all database objects. The owner of the object also has this ability. A new user must be granted rights to view or modify objects in the database. Rights may be assigned with the GRANT and REVOKE commands: eg: GRANT UPDATE ON customer TO goldie; Our new user may now update the information held in the customer table.
Privileges on Tables For a given user, four main privileges apply to each table, view or sequence. These correspond to the four SQL statements which manipulate data. SELECT : Ability to see the data UPDATE / DELETE : Ability to modify the data INSERT : Ability to append new rows To allow our user to see data about movies, and alter or delete reservations: GRANT SELECT ON movie TO goldie; GRANT ALL ON reservation TO goldie;
Scope of privileges ● User accounts are global to the database server. ● Server can have many databases ● Database can have many schemas ● Schema can have many objects ● (Table can have many rows)
User Groups Setting individual privileges for every user on every table is ● tiresome. As well as user roles you can define group roles. All users in ● the group, including any added later, will inherit the group rights. A user can be a member of any number of groups. ● You can grant privileges to PUBLIC so that all users have the ● specified permissions.
Modifying Groups CREATE ROLE staff; ● GRANT staff TO goldie; ● GRANT INSERT ON video TO staff; ● CREATE USER tim IN ROLE staff; ● Sack Goldie (possibly for problems of bad attitude): REVOKE staff FROM goldie; ● DROP USER goldie; ● Make our video list public: GRANT SELECT ON video TO PUBLIC; ●
MySQL See the MYSQL documentation. Briefly: Users are created with GRANT ● GRANT ALL on mydb.* TO goldie@% IDENTIFIED BY “password”; DELETE FROM user WHERE User = “goldie”; MySQL does not have groups, but you can assign privileges at ● the table or column level. You can also set different privileges when the same user connects from different hosts. Always remember to FLUSH PRIVILEGES; ●
Remote Connections to PostgreSQL For security reasons, non-local access is disabled by default ● When you do enable it, PostgreSQL defaults to using encrypted ● connections. You need to modify postgresql.conf and pg_hba.conf ●
User Authentication pg_hba.conf controls user authentication. Modify the ● examples in the file to your needs. Basic methods: trust = No authentication at all – use only for debugging! ident sameuser = The default. You can connect as the user with the same name as your Linux login. md5 = Users will be challenged for a password, which will be transmitted securely. Recommended for all remote connections.
Backups You do backup your files, don't you? ● Most sites offer some form of backup facility, or you can use ● the CD writer on the machine. You can lose data for any number of reasons. ● – Human error – Hardware failure – Malicious action/hacking Or you might simply want to copy your database to another ● machine
Backups It is possible to backup a database by simply saving the /home/db/postgres working directories as part of your regular backups. This is fine for disaster recovery, but not ideal because: These files cannot be read by newer or older versions of the ● PostgreSQL server. There is no way to control what data you backup or restore. ● There is no way to see what a backup file contains. ● If you accidentally leave PostgreSQL running when you restore ● then bad things will happen. The one-shot command (as user manager): sudo -u postgres pg_dumpall > dump.sql
Selective Backup and Restoring You can also back up a single database using pg_dump: pg_dump --create mydb > mydb.sql If you want to restore the database onto a new machine, you also need to backup the system tables, to get back all the users and groups – and you will need to restore these first!: pg_dumpall -g > globdump.sql To restore backups, use psql -f dumpfile.sql eg.: ● psql -f globdump.sql
Regaining Control It is possible to lock yourself out of your own database, even if ● you are the manager/root user on your machine! If you have have set a password but forgotten it, you can ● temporarily modify the pg_hba.conf file to 'trust' all local users. Then: sudo pg_ctl reload psql -U postgresql On MySQL, you can stop the server and restart giving the ● --skip−grant−tables option. You will be permitted to login without a password. Set a new password, then FLUSH PRIVILEGES.
References and Further Information Any database administrator needs the manual to hand! http://www.postgresql.org/docs/8.3/interactive/sql-grant.html If you have a PostgreSQL book, such as Worsley/Drake, be aware that there have been several changes to the details of user admin in recent releases of PG – it is always best to consult the specific documentation for your version. Also on Bio-Linux see: zless /usr/share/doc/postgresql-8.3/README.Debian.gz
Recommend
More recommend
