I have a group role that consists of around 1,000 users. Those users do not inherit group role permissions, and I would like to make them all inherit them instead. What would be the way to do it without writing 1,000 lines of PostgreSQL code?

I am using SQL Server 2017 and the latest SSMS version. When I click securables of login in SSMS, I see this error: >Object reference not set to an instance of an object. (SqlMgmt) > >------------------------------ >Program Location: > > at Microsoft.SqlServer.Management.SqlMgmt.PermissionsData.Securable.GetImage(SecurableType type)\ at Microsoft.SqlServer.Management.SqlMgmt.PermissionsSecurableSelector.InitializeBitmaps() at Microsoft.SqlServer.Management.SqlMgmt.PermissionsSecurableSelector..ctor(IHelpProvider helpProvider, IServiceProvider serviceProvider, Principal principal, PermissionsDetailsGrid permissionsGrid, Object connectionInfo, Boolean yukonOrGreater)\ at Microsoft.SqlServer.Management.SqlMgmt.PermissionsOfPrincipalsCore.InitializeSecurableSelector()\ at Microsoft.SqlServer.Management.SqlMgmt.PermissionsOfPrincipalsCore..ctor(IHelpProvider helpProvider, IServiceProvider serviceProvider, Principal principal, Object connectionInfo, Boolean yukonOrGreater, Boolean showColumnPermissionsInDetailsGrid)\ at Microsoft.SqlServer.Management.SqlMgmt.PermissionsServerPrincipal.InitializePermissionsCore()\ at Microsoft.SqlServer.Management.SqlMgmt.PermissionsServerPrincipal.OnInitialization()\ at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.SetView(Int32 index, TreeNode node)\ at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.OnBeforeSelection(Object sender, TreeViewCancelEventArgs e) What can be the problem? I first thought it might be permission problem, but even if I use sysadmin login, I see this error every time.

I had a question regarding the extension postgres_fdw. I am trying to use postgres_fdw to import foreign tables from DB A to DB B. These databases are on the same host. In Database B, I have a set of users that have read_write access for each schemas. I want them also have access to these foreign tables and any subsequent views and tables that are created from these tables. My issue is: All the postgres_fdw commands, I've had to run as superuser. I've run the following:

CREATE EXTENSION postgres_fdw
CREATE SERVER DB_A_server
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'host_name', dbname 'Database_A');
CREATE USER MAPPING FOR read_write_user
  SERVER DB_A_server
  OPTIONS (user 'readonly_DB_A', password 'readonly_DB_A');
GRANT USAGE ON FOREIGN SERVER  DB_A_server to read_write_user;
SET ROLE read_write_user;

I'm unable to run these commands as the read_write_user for Databaase B. My end goal is to have: read_write users import foreign tables as they please. IMPORT FOREIGN SCHEMA completed LIMIT TO (Database_A_table1, Database_A_table2 ) FROM DB_A_server INTO public What would I have to do in order to give the read_write role access to these foreign tables? Can I do something like: GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO ; https://learn.microsoft.com/en-us/answers/questions/741419/create-foreign-data-wrapper-without-superuser-priv.html Thanks!

I'm trying to create a database with a few roles for my app but I'm struggling to get things, work. I want to have: - an owner user that run the migrations and who can run actions in the database bypassing RLS - an application user that is not able to create/drop tables and is limited by RLS - a few roles that I'll use in the app switching base on business logic The user is created using the postgres user, after that I run one migration using the postgres user where I create my users/roles

-- Create user with login
create role ${owner.user} with login password '${owner.password}' bypassrls;
create role ${app.user} with login password '${app.password}' noinherit;

grant connect on database ${database} to ${owner.user};
grant connect on database ${database} to ${app.user};

grant ${owner.user} to postgres;
grant ${app.user} to postgres;

-- Revoke default permissions
revoke all on schema public from public;

alter default privileges revoke all on sequences from public;
alter default privileges revoke all on functions from public;

-- Assign db to owner
alter database ${database} owner to ${owner.user};

-- grant permissions
grant all on database ${database} to ${owner.user};
grant all on schema public to ${owner.user};

-- Create roles used by the app
create role anonymous;
create role logged_user;
create role admin;

grant anonymous TO ${app.user};
grant logged_user TO ${app.user};
grant admin TO ${app.user};

grant pg_read_all_data to ${owner.user};
grant pg_write_all_data to ${owner.user};

grant usage on schema public to ${owner.user};
alter default privileges in schema public grant execute on functions to ${owner.user};
alter default privileges in schema public grant usage, select on sequences to ${owner.user};

-- app
grant usage on schema public to anonymous;
grant usage on schema public to logged_user;
grant usage on schema public to admin;

alter default privileges in schema public grant usage, select on sequences to anonymous;
alter default privileges in schema public grant usage, select on sequences to logged_user;
alter default privileges in schema public grant usage, select on sequences to admin;

Then I run this migration with the owner user

-- Extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;
-- Functions
GRANT EXECUTE ON FUNCTION uuid_generate_v4() TO anonymous, logged_user, admin;

but the migration thrown this error:

Error migrating the database:  error: permission denied for function uuid_generate_v4

what am I doing wrong? Does this permissions make a sense? I want to give as less permissions as possible

I've done what I consider to be sufficient research with no answers to justify posting this question here. I am attempting to grant users permission to execute certain stored procedures. And because I have to grant permission for several of these roles, but not all of them, I've chosen to do this using roles. But when I grant one of these roles to a test user and try to call a stored procedure, I see this error message: 1370 (42000): execute command denied to user 'me'@'localhost' for routine 'db.MyProc' The SQL script to create a role looks like this: CREATE ROLE 'MyRole'; GRANT USAGE ON db.* TO 'MyRole'; GRANT EXECUTE ON PROCEDURE db.MyProc TO 'MyRole'; -- grant execute on more procedures FLUSH PRIVILEGES; And then I grant the role to the test user and set it as default: GRANT MyRole TO 'me'@'localhost'; SET DEFAULT ROLE MyRole TO 'me'@'localhost'; FLUSH PRIVILEGES; Naturally, I do all of this as root. I think that the grants have worked because of the output of SHOW GRANTS. SHOW GRANTS FOR MyRole; +--------------------------------------------------------------+ | Grants for MyRole@% | +--------------------------------------------------------------+ | GRANT USAGE ON *.* TO MyRole@% | | GRANT EXECUTE ON PROCEDURE db.MyProc TO Contributor@%| |(grant execute on more procedures) | +--------------------------------------------------------------+ SHOW GRANTS FOR 'me'@'localhost'; +---------------------------------------+ | Grants for contrib@localhost | +---------------------------------------+ | GRANT USAGE ON *.* TO me@localhost| | GRANT MyRole@% TO me@localhost| +---------------------------------------+ I have found no one else with this problem. And nothing I read from MySQL documentation suggested that this would not work. What is going wrong? I'm using MySQL 8.0.11 on Windows 10.

For context, I am using: - postgREST : a thin REST client on top of Postgres (which does all the heavy lifting) - Azure AD : which handles identity and access of the organization Since users, roles, groups, etc (and even password reset, etc) are handled in Azure AD, all I want to do with my API is allow the user that provided a validated JWT in. When a user first faces the API, their role doesn't exist. And when they first face the API, they effectively have the most basic permissions (that of anonymous user). I want an anonymous user to be able to POST to an /rpc/* endpoint with a valid id token from Azure AD. This would do some very privileged stuff like creating and granting roles (but controlled by the function). i.e. Anonymous user would not be able to create a role, but anonymous user would be able to execute function (with valid security JWT as input), and indirectly create roles through the function.

I'm green as a dba somewhat. Not really a DBA actually. So we have a bunch of databases/ datatables ... we need to grant a small subsection of access (say 3 tables, show half the fields for PPI reasons). For some reason the project lead initially thought we should create an entirely different database (DB_service_account) and either replicate or ETL-filter-write the limited data necessary to this. The thinking would be this simplifies management -- the service account has access to everything in that database. Is this a common design pattern? I mean --- an alternative would be create a limited/ secure views on these tables ... maybe with a 'schema' name (more of a logical than physical separation) .. right? That would avoid write jobs, additional storage? Then grant access to this schema ... right? Project lead also wants a separate warehouse (compute) for cost monitoring -- again probably not the most efficient but that can be done at the User level anyway --- is it possible or necessary to use "Shares" or "Replication" or are these completely different use cases and over-complicating matters? Just wondering thoughts -- thanks!

Does dropping and recreating a postgres database (or schema or table) cause a loss of any granular permissions that were formerly associated with that resource? For comparison, is it like a Posix filesystem (or Linux FACL) where the metadata is attached to the entries and so deleting and recreating a director tree will undo any granular permission changes, or is the metadata kept elsewhere (and with no foreign key constraint) so that the granular policies can be retained and still apply if any future database resources match those same names?

I have 2 SQL Servers running SQL Server 2019. They each have a sql login that uses windows authentication (an AD Group) "SG-GroupA" and the group has 5 windows users in it. Each of the servers has A database on it called DbFoo and the login for SG-GroupA is mapped to DbFoo and the role membership for the db is public, and db_datareader. I have verified the login exists, and the user exists on both of the databases. When A user from that group logs into the SQL Server and tries to query DbFoo, on Server A they get a result just fine. When they run the same query on Server B, they get an error "The database DBFoo is not accessible." As an additional test on Server B I created a completely new database with a single user SG-GroupA in it, and again an error is thrown when anyone tries to query a table in the DB. Performing the same test on Server A throws no errors and everything is fine. Where can I begin to debug this issue?

Sometimes I find the AG Cluster role in offline state which results the AG in resolving state. It doesn't allow the application to access the databases during this time. And then after sometime it comes to online state. Why is that happening? If one of the cluster networks is down, then the cluster role goes to offline state?

I'm more of a data engineer than administrator and my role/schema management knowledge is more or less frozen in time since 8.4. I'm aware that pg15 tightened public schema restrictions, and pg14 introduced "predefined roles " as well as scram-sha-256 password encryption by default. This is how I have been creating DBs and roles so far (typically 1:1 name/ownership).

-- as superuser role
CREATE USER test;
ALTER USER test WITH PASSWORD test;
CREATE DATABASE test WITH OWNER test;
\c test
REVOKE ALL ON DATABASE test FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
ALTER SCHEMA public OWNER TO test;

I also sometimes create read-only users, which typically involves granting fine-grained SELECT permission on specific objects. I am now using pg16 and trying to get up to speed on the recommended role management strategies. I have three questions: 1) How much of my pre-pg16 DB creation script (see above) is still relevant? Can it be simplified? 2) How can I create a new role with read-only access to public and read-write access to its own schema? For example editor has read-only access to test.public and read-write to test.editor 3) How can I create another role with read-only access to _all_ schemas in a given DB? I don't need anything further, such as row-level security, etc. Thanks in advance!

I'm building a server application where any number of "modules" are created over time, that can reference each other in the form of foreign keys or reads. They can't write to each other, and they definitely can't create/delete database objects. Since the "modules" can be linked together, it seems necessary they be schema objects, since having them in separate database objects wouldn't allow them to link together. The trouble I'm having is how to design the role system: - There needs to be a "server" role, which has the ability to create/delete the "module" schema objects. - There need to be various "module" roles capable of creating/deleting/reading/writing tables etc inside a module, so that each module can manage itself over time and fulfill requests made to it. This would be straightforward if the various "module" roles could just have a login password they could use. However since in my application all interactions with this database go through the server, and it inherently needs to dynamically create these modules and their associated roles, giving each module role a login would mean storing randomly generated passwords somewhere and fetching them before executing actions on behalf of the module, which is slower and another point of vulnerability. I've been trying to find a way to construct my role relationships so that the "server" role can use SET ROLE some_module_role. However this setup allows some_module_role to set itself *back* to the server role, which is insecure in this context. It seems there's no way to restrict the role relationships in this way, since [permissions checks for SET ROLE always use the original signed in user](https://www.postgresql.org/docs/current/sql-set-role.html) . > Note that SET ROLE and SET SESSION AUTHORIZATION are exceptions; permissions checks for those continue to use the current session user and the initial session user (the authenticated user), respectively. And [GRANT some_module_role to server_role with set false](https://www.postgresql.org/docs/current/sql-grant.html) only works in one direction. And this seems to be borne out in my experiments:

-- logged in as admin to initially bootstrap the application
create role server_role createrole noinherit login password 'server_role_password';
grant set on parameter role to server_role;

-- logged in as server_role
create role some_module_role noinherit nologin;
grant some_module_role to server_role;
set role some_module_role; -- SHOULD be allowed and is
set role server_role; -- should NOT be allowed but is!!

Is there any way to achieve this? Or do I have to bite the bullet and accept I have to save module role passwords somewhere and connect with them?

I am working on new processes and policies for my PostgreSQL databases when they are first created, and I am trying to define what role attributes are necessary for a role specifically used for backups. I have found for MySQL that the grants SELECT, PROCESS, LOCK TABLES, SHOW VIEW, TRIGGER are enough for a user to perform backups, but I can't figure out how to translate grants like this to PostgreSQL. Any recommendations, other than having to rely on Superuser?

Let's say I have two Postgresql database groups, "authors" and "editors", and two users, "maxwell" and "ernest". create role authors; create role editors; create user maxwell; create user ernest; grant authors to editors; --editors can do what authors can do grant editors to maxwell; --maxwell is an editor grant authors to ernest; --ernest is an author I would like to write a performant function that returns a list of the roles (preferably their oid's) that maxwell belongs to, something like this: create or replace function get_all_roles() returns oid[] ... It should return the oids for maxwell, authors, and editors (but not ernest). But I am not sure how to do it when there is inheritance.

Is there a way to visualize which users are members of a role in PostgreSQL / pgAdminIII, like role: council_stuff members: Harry Ben Steve Melinda

# Context I have a PostgreSQL server (localhost:5432) which holds a database called my_database. I have created long time ago, a simple role which owns this database; my_user, different from the postgres role; List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- my_user | Superuser, Create role, Create DB | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} This manually created role does not have an OS equivalent user, like postgres does. I have a bash script to reinitialize my_database with a set of schemas and tables (from the create_db.sql file); #!/bin/bash set -e dropdb -U my_user my_database || true createdb -U my_user my_database --owner=my_user psql -U my_user -d my_database -h localhost -v ON_ERROR_STOP=1 -a -f create_db.sql # Issue Since a few days, this script is asking me 3 times the password for my_user. I guess one time per line. Before that, I was able to run it without any prompt for any password. That was really cool. The only thing I changed in between was this; I usually connect to this server using pgadmin3 with the postgres user to checkout what's inside my tables. In pgadmin3, I once set up this server credentials to my_user and its password to checkout some differences with the postgres login. Then, always in pgadmin3, I switched back the credentials to postgres as they were before. # Question I cannot understand why this would have broken something which had since then made my script asking for password... It may be related to something other, but I really don't know what? I also tried to set up a .pgpass file with this line; localhost:5432:my_database:my_user:my_user_password and changing its access to 0600 as stated in the [documentation](https://www.postgresql.org/docs/9.1/libpq-pgpass.html) . But it doesn't seem to be recognized; the bash script is still asking 3 times for my_user password. And, as I (the ubuntu normal OS user, which is *not* the same as the role my_user which owns my_database) owns this file , I wonder if I should set up permissions to this file to the postgres OS user instead? Anyway, this .pgpass file was empty before, when I was able to reset my_database without any password prompt. So if I can recover the previous behavior it would be nice, except if this is not a good practice. Any clue would be appreciated as I'm not much used to database management. # Information "PostgreSQL 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit" ## EDIT I just noticed that if I set the .pgpass file with this content; *:*:*:my_user:my_user_password it works (it stops asking for a password). But with the database name; *:*:my_database:my_user:my_user_password it doesn't (it keeps asking for a password).

PostgreSQL - Version 16.4 I am trying to change the password of a user (xyz). I have logged in as abc. abc has ADMIN and CREATEROLE privileges. I am getting an error - ERROR: permission denied to alter role DETAIL: To change another role's password, the current user must have the CREATEROLE attribute and the ADMIN option on the role. I guess this means abc must have ADMIN option on xyz. How can I find out which users/roles have ADMIN option on xyz? How can I grant ADMIN on xyz to abc?

I'm trying to add the ability for a user to be authorized with a given role on a contract/site/experiment. More specifically, I want the ability for a user to have role A on a contract but at the same time role B on a specific site of this same contract and possibly a role C on an experiment of this site. My first idea was a join table between User and Role with a third column storing the id of either a contract, site or experiment. This solution has many problems in my opinion: - I need a row for the contract, each authorized sites and each authorized experiments. It can grow very large very quickly. - No integrity. A non existant contract can be referenced - If I want to know all the sites and experiments of a contract where a user is authorized I have to first query all sites of this contract, then all the experiments of these sites and then find all the rows referencing those ids. It seems a bit hacky. I feel like this solution could work but I was wondering if there were other way? Maybe some pattern I am not aware of? PS: I am using MySQL v8

I am facing an strange issue dealing with Server Roles in my Azure DB environment when using a login derived from AAD Group (Microsoft Entra) for low-privileged admin/deployment activities (such as create DBs, deploy changes in it, etc.). **IMPORTANT:** It **does** work when I am using a simple SQL Login and individual AAD Logins but **does not** work with AAD Groups. For example if I add **AAD Login** (Group) to **##MS_DatabaseConnector##** role, I am not able to login to a virtual server and getting the following error: Login failed for user 'token-identified principal'. (Framework Microsoft SqlClient Data Provider) Script I am using to add user/permissions is very trivial: CREATE LOGIN [SG-User] FROM EXTERNAL PROVIDER; GO ALTER SERVER ROLE ##MS_DatabaseConnector## ADD MEMBER [SG-User]; GO Selecting below give me empty result-set when testing: SELECT * FROM fn_my_permissions(NULL, 'SERVER'); However, selecting below show me appropriate role membership for a login: SELECT member.principal_id AS MemberPrincipalID, member.name AS MemberPrincipalName, roles.principal_id AS RolePrincipalID, roles.name AS RolePrincipalName FROM sys.server_role_members AS server_role_members INNER JOIN sys.server_principals AS roles ON server_role_members.role_principal_id = roles.principal_id INNER JOIN sys.server_principals AS member ON server_role_members.member_principal_id = member.principal_id LEFT JOIN sys.sql_logins AS sql_logins ON server_role_members.member_principal_id = sql_logins.principal_id WHERE member.principal_id NOT IN ( -- prevent SQL Logins from interfering with resultset SELECT principal_id FROM sys.sql_logins AS sql_logins WHERE member.principal_id = sql_logins.principal_id ); I know there is a possibility to use another way to grant access via users in master DB by adding to specific master db roles but I would like to make it work according to Microsoft recommendation where those new Server Roles is the right way to go for such requirements. Can someone please point me to the right direction if this is a known issue? So far I have not been able to locate similar issues in the web.

I have a very old user named "ADMINX" that has control in almost all tablespaces in an Oracle 19c database. It has been given "DBA" role and also a custom role named "APP_ADMIN_ROLE" that has DBA role inside. If i revoke DBA role from ADMINX itself but let APP_ADMIN_ROLE as it is, would tablespace quotas be reset? I suppose it should keep unlimited quotas since APP_ADMIN_ROLE still has DBA role within but i'm never sure how Oracle behaves in such cases. Sadly DBA_TS_QUOTAS doesn't show unlimited quotas taken by DBA roles.