EGTDC Database Course 2004 Database Users And Security Backing-Up Data Tim Booth : tbooth@ceh.ac.uk Environmental Genomics Thematic Programme Data Centre http://envgen.nox.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 ● Environmental Genomics Thematic Programme Data Centre http://envgen.nox.ac.uk
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? Environmental Genomics Thematic Programme Data Centre http://envgen.nox.ac.uk
Creating Users Administrative users may add other users. 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' Environmental Genomics Thematic Programme Data Centre http://envgen.nox.ac.uk
User Privileges The database administrator has full rights to all database objects. 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. Environmental Genomics Thematic Programme Data Centre http://envgen.nox.ac.uk
Privileges on Tables For a given user, four 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; Environmental Genomics Thematic Programme Data Centre http://envgen.nox.ac.uk
User Groups Setting privileges for every user on every table is tiresome. ● You can put users into groups and grants rights to the group. All users in ● the group, including any added later, will inherit the group rights. You can give permissions on a table to any number of groups. ● 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. A user gets all permissions given to them explicitly, plus any group ● permissions, plus any public permissions. Tables are never owned by a group, always a user. ● Environmental Genomics Thematic Programme Data Centre http://envgen.nox.ac.uk
Modifying Groups CREATE GROUP staff; ● ALTER GROUP staff ADD USER goldie; ● GRANT INSERT ON video TO GROUP staff; ● CREATE USER tim IN GROUP staff; ● Sack Goldie (possibly for problems of bad attitude): ALTER GROUP staff DROP USER goldie; ● DROP USER goldie; ● Make our video list public: GRANT SELECT ON video TO PUBLIC; ● Environmental Genomics Thematic Programme Data Centre http://envgen.nox.ac.uk
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; ● Environmental Genomics Thematic Programme Data Centre http://envgen.nox.ac.uk
Remote Connections to PostgreSQL For security reasons, this is disabled by default. ● Bio-Linux specific details are on the EGTDC website: ● http://envgen.nox.ac.uk/envgen/software/archives/000447.html You need to modify postgresql.conf and pg_hba.conf. ● If security is important, use the built in SSL encryption. ● Environmental Genomics Thematic Programme Data Centre http://envgen.nox.ac.uk
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. Environmental Genomics Thematic Programme Data Centre http://envgen.nox.ac.uk
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 ● Environmental Genomics Thematic Programme Data Centre http://envgen.nox.ac.uk
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 postgres): pg_dumpall > dump.sql Environmental Genomics Thematic Programme Data Centre http://envgen.nox.ac.uk
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> : psql -f globdump.sql; psql -f mydb.sql Environmental Genomics Thematic Programme Data Centre http://envgen.nox.ac.uk
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. Environmental Genomics Thematic Programme Data Centre http://envgen.nox.ac.uk
References and Further Information Any database administrator needs the manual to hand! http://www.postgresql.org/docs/7.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. Environmental Genomics Thematic Programme Data Centre http://envgen.nox.ac.uk
Recommend
More recommend
