Run Setup With Standard Privileges

[Walda Caesar]

ALTERDEFAULT PRIVILEGES allows you to set the privileges that will be applied to objects created in the future. (It does not affect privileges assigned to already-existing objects.) Privileges can be set globally (i.e., for all objects created in the current database), or just for objects created in specified schemas.

While you can change your own default privileges and the defaults of roles that you are a member of, at object creation time, new object permissions are only affected by the default privileges of the current role, and are not inherited from any roles in which the current role is a member.

As explained in Section 5.7, the default privileges for any object type normally grant all grantable permissions to the object owner, and may grant some privileges to PUBLIC as well. However, this behavior can be changed by altering the global default privileges with ALTER DEFAULT PRIVILEGES.

Currently, only the privileges for schemas, tables (including views and foreign tables), sequences, functions, and types (including domains) can be altered. For this command, functions include aggregates and procedures. The words FUNCTIONS and ROUTINES are equivalent in this command. (ROUTINES is preferred going forward as the standard term for functions and procedures taken together. In earlier PostgreSQL releases, only the word FUNCTIONS was allowed. It is not possible to set default privileges for functions and procedures separately.)

Default privileges that are specified per-schema are added to whatever the global default privileges are for the particular object type. This means you cannot revoke privileges per-schema if they are granted globally (either by default, or according to a previous ALTER DEFAULT PRIVILEGES command that did not specify a schema). Per-schema REVOKE is only useful to reverse the effects of a previous per-schema GRANT.

The name of an existing schema. If specified, the default privileges are altered for objects later created in that schema. If IN SCHEMA is omitted, the global default privileges are altered. IN SCHEMA is not allowed when setting privileges for schemas, since schemas can't be nested.

The name of an existing role to grant or revoke privileges for. This parameter, and all the other parameters in abbreviated_grant_or_revoke, act as described under GRANT or REVOKE, except that one is setting permissions for a whole class of objects rather than specific named objects.

If you wish to drop a role for which the default privileges have been altered, it is necessary to reverse the changes in its default privileges or use DROP OWNED BY to get rid of the default privileges entry for the role.

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

While this application will be the only one hosted on the server at first, I'd prefer to bake in the assumption that it might be hosted on a server with other databases in the future, rather than having to scramble later if that becomes necessary in the future.

I would think that these would be a fairly common set of requirements, but I'm having trouble finding a simple tutorial explaining how to set up a new database in PostgreSQL, with this sort of user/privilege separation. The references go on at length about groups, users, roles, databases,schemas and domain; but I find them confusing.

But I'm not getting the intended semantics. I want to have it configured so only the hostdb_admin can create (and drop and alter) tables; the hostdb_mgr can read, insert, update and delete on all tables by default; and the hostdb_usr can only read all tables (and views).

When I tried this I found that I was able to create tables in hostdb as any of these users; but, for each user, I could only read or modify tables created by that user - unless I use an explicit GRANT.

Postgres 14 adds the predefined, non-login roles pg_read_all_data and pg_write_all_data to give read-only / write-only access to all objects in all schemas. That goes beyond what's being asked here, but may be useful. See:

Note that the user performing the insert, update or delete on the viewmust have the corresponding insert, update or delete privilege on theview. In addition the view's owner must have the relevant privilegeson the underlying base relations, but the user performing the updatedoes not need any permissions on the underlying base relations (seeSection 38.5).

The right to drop an object, or to alter its definition in any way, is not treated as a grantable privilege; it is inherent in the owner, and cannot be granted or revoked.(However, a similar effect can be obtained by granting or revoking membership in the role that owns the object; see below.)The owner implicitly has all grant options for the object, too.

Or create all objects with the role schma_admin to begin with, then you need not set the owner explicitly. It also simplifies default privileges, which you then only have to set for the one role:

The same applies if you create objects with a role that does not have DEFAULT PRIVILEGES set, like the superuser postgres. Reassign ownership to schma_admin and set privileges manually - or set DEFAULT PRIVILEGES for postgres as well (while connected to the right DB!):

PostgreSQL grants default privileges on some types of objects toPUBLIC. No privileges are granted to PUBLIC by default on tables,columns, schemas or tablespaces. For other types, the defaultprivileges granted to PUBLIC are as follows: CONNECT and CREATE TEMP TABLE for databases; EXECUTE privilege for functions; and USAGEprivilege for languages.

In particular, no default privileges are granted to PUBLIC for new schemas. It may be confusing that the default schema named "public" starts with ALL privileges for PUBLIC. That's just a convenience feature to ease the start with newly created databases. It does not affect other schemas in any way. You can revoke these privileges in the template database template1, then all newly created databases in this cluster start without them:

Don't forget to set the search_path. If you only got the one database in the cluster you can just set the global default in postgresql.conf.Else (more likely) set it as property of the database, or just for involved roles or even the combination of both. Details:

In addition to that I will create a password protected 'administrator account' that they can use when and should they need to make changes to the computer requiring admin privileges (for example installing software). My hope is that this setup will increase their safety while browsing the internet, and provides a little more safety buffer when it comes to them doing stupid stuff (like clicking on links in emails). I realize privilege escalation exploits exist, but at least if an adversary/attack tries to run malicious scripts under the current users privileges, that attack vector will be limited.

However this is still vulnerable to exploits, so while they are safer, they are not fool proof safe. After all viruses exists even for Linux and Mac and those are much more restrictive on their users.

With a normal user, most exploits will only affect that user. This means their data and data of any service that reveals information to them is at risk. However there are certain malware that can happen that will escalate the issue to administrative levels of privilege, at which point the entire system is compromised anyways.

Simple answer, yes he is safer but not "safe". If an attacker who has delivered a payload to the machine whether from an iframe or a redirect, the machine is owned and there is little to nothing you can do about it. The problem is not that it would help with some attacks, but it would make little difference in a vast majority of them.

I would say on an at-home computer that I don't use for work purposes at all, that having a separate admin account is over the line of usability vs security in my mind. I reinstall from an image of what I consider "needed" software at home a few times a year and I keep no personal information on it. I do however, have a "work" laptop that has a separate admin user/pass that does have information I want to keep out of the hands of miscreants and I do take much greater care of security measures on that machine.

The questions, as always, is security vs usability. Is banking done on the PC? Social Security #'s? Company info? etc... Once those questions are answered you can arrive at the best answer for the situation.

Using a regular user account instead of an Administrator user account is certainly a good idea from a security perspective. It adds one more layer limiting what something can do on your system but I'm not sure it will work in your situation.

I'm assuming your relative isn't the most technically savvy because you are setting up the computer for them and because you are worried about protecting them from stuff. The problem is if you give them the admin password you have no assurances they won't just type it in any time anything causes the prompt to appear. If they do, then you haven't bought yourself anything. If on the other hand you don't give them the password then you or someone else would need to intervene every time something needed admin permissions.

I've got my wife setup as a regular user on Windows 10 and it hasn't been a problem but I can easily take care of anything that needs admin permissions for her. Are you willing and able to do that for your relative? I assume you aren't because you were planning to give them the password.

Within Windows Explorer, I can right click on an executable file and pick 'Run as administrator' which will launch the selected process with elevated privileges or I can shift-right click on the executable file and click 'Run as different user', specify the username and password which will launch the process with standard privileges using the specified user context.

3a8082e126