Greg Cocks wrote:
I don’t remember changing those… it is maybe because my ‘production’ database is in the PUBLIC schema??
The the schema that your tables are in wouldn't have any effect on the "system" rights granted to roles on the server (such as the login privilege or inheritance).
(would like to change this as I have now read that PUBLIC schemas have ‘default’ user rights for the logged in user rights that overrides those that – but have (manually) emulated the relationships in the ODBC-linked tables in MS Access - to help in Query Manager, etc - and don’t want to set that up again, at least not right now! [aside – I am thinking a long term ‘project’ would be to write some VBA in Access that grabs/tests (??) all the PK/FK relationships in the linked tables and sets them up in the relationships in Access ‘automagically’…; and then maybe if I am feeling even braver something that sets the lookup display controls in the Access linked tables based on the FKs… although maybe they would not save on linked tables?])
It's true that all users that can connect to a database have the CREATE right in the public schema, but you can revoke that right from public (and it's quite easy...(revoke create on schema public from public;), and if you want this to be applied by default for all new databases, just issue it in the template1 databases (this assumes that you always use template1 as your template) . Also, keep in mind that once an object is created in a schema, the owner is the only one with rights to that object...so while you could have rogue users creating objects (tables, sequences, indexes, etc.), they wouldn't be able to access tables (or data in those tables) unless rights had been granted to them.
As such, the fact that your tables (a database is a superset of a schema, so your tables would be created in the public schema inside your database) are in the public schema shouldn't be much of an issue unless you granted the public role rights to them.
--
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC 27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com
Ask me about our expert PostgreSQL & PostGIS Training delivered worldwide.
