Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

sql: implement privilege-related built-ins. #22734

Closed
mberhault opened this issue Feb 15, 2018 · 3 comments
Closed

sql: implement privilege-related built-ins. #22734

mberhault opened this issue Feb 15, 2018 · 3 comments
Assignees
@mberhault
Milestone
2.0

Comments

@mberhault
Copy link
Contributor

mberhault commented Feb 15, 2018 •
edited
Loading

The postgresql list is: https://www.postgresql.org/docs/8.4/static/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE

name return type description
has_any_column_privilege(table, privilege) boolean does current user have privilege for any column of table
has_column_privilege(user, table, column, privilege) boolean does user have privilege for column
has_column_privilege(table, column, privilege) boolean does current user have privilege for column
has_database_privilege(user, database, privilege) boolean does user have privilege for database
has_database_privilege(database, privilege) boolean does current user have privilege for database
has_foreign_data_wrapper_privilege(user, fdw, privilege) boolean does user have privilege for foreign-data wrapper
has_foreign_data_wrapper_privilege(fdw, privilege) boolean does current user have privilege for foreign-data wrapper
has_function_privilege(user, function, privilege) boolean does user have privilege for function
has_function_privilege(function, privilege) boolean does current user have privilege for function
has_language_privilege(user, language, privilege) boolean does user have privilege for language
has_language_privilege(language, privilege) boolean does current user have privilege for language
has_schema_privilege(user, schema, privilege) boolean does user have privilege for schema
has_schema_privilege(schema, privilege) boolean does current user have privilege for schema
has_server_privilege(user, server, privilege) boolean does user have privilege for foreign server
has_server_privilege(server, privilege) boolean does current user have privilege for foreign server
has_table_privilege(user, table, privilege) boolean does user have privilege for table
has_table_privilege(table, privilege) boolean does current user have privilege for table
has_tablespace_privilege(user, tablespace, privilege) boolean does user have privilege for tablespace
has_tablespace_privilege(tablespace, privilege) boolean does current user have privilege for tablespace
pg_has_role(user, role, privilege) boolean does user have privilege for role
pg_has_role(role, privilege) boolean does current user have privilege for role

Obviously some do not apply to us, but we could easily implement table/schema/role related functions.
@mberhault mberhault self-assigned this Feb 15, 2018
@mberhault
Copy link
Contributor Author

@jordanlewis mentioned the need for has_schema_privilege for external tools.

@jordanlewis
Copy link
Member

That's needed for #15441.

@nvanbenschoten
Copy link
Member

Related to #20784 as well. @jordanlewis we can probably close that in favor of this issue.

I mocked out has_schema_privilege and pgweb began working like a charm! We should aim to get this in for the 2.0 release.

@nvanbenschoten nvanbenschoten added this to the 2.0 milestone Feb 17, 2018
@nvanbenschoten nvanbenschoten mentioned this issue Mar 3, 2018
Merged
nvanbenschoten added a commit to nvanbenschoten/cockroach that referenced this issue Mar 3, 2018
@nvanbenschoten
sql: introduce PG-compatible access privilege inquiry functions
ae97d2f 
Fixes cockroachdb#22734.
Fixes cockroachdb#20784.
Relates to cockroachdb#15441.

This change introduces a series of Postgres-compatible privilege-related
builtin functions:
- `has_any_column_privilege`
- `has_column_privilege`
- `has_database_privilege`
- `has_foreign_data_wrapper_privilege`
- `has_function_privilege`
- `has_language_privilege`
- `has_schema_privilege`
- `has_sequence_privilege`
- `has_server_privilege`
- `has_table_privilege`
- `has_tablespace_privilege`
- `has_type_privilege`
- `pg_has_role` (_coming soon!_)

These all follow the specification documented by Postgres in:
https://www.postgresql.org/docs/8.4/static/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE

These Access Privilege Inquiry Functions allow users to query object
access privileges programmatically. Each function has a number of
variants, which differ based on their function signatures. These
signatures have the following structure:
```
- optional "user" argument
  - if used, can be a STRING or an OID type
  - if not used, current_user is assumed
- series of one or more object specifier arguments
  - each can accept multiple types
- a "privilege" argument
  - must be a STRING
  - parsed as a comma-separated list of privilege
```

This means that in total, each function has at least 6 variants.

The main reason for adding these builtins in is because they were the
last remaining issue that was blocking full compatibility with pgweb.
Pgweb is a web-based database browser written in Go, which means that
can run on OSX, Linux and Windows machines!

Release note (sql change): Introduces a series of Postgres-compatible
privilege-related builtin functions.
nvanbenschoten added a commit to nvanbenschoten/cockroach that referenced this issue Mar 14, 2018
@nvanbenschoten
sql: introduce PG-compatible access privilege inquiry functions
94c25be 
Fixes cockroachdb#22734.
Fixes cockroachdb#20784.
Relates to cockroachdb#15441.

This change introduces a series of Postgres-compatible privilege-related
builtin functions:
- `has_any_column_privilege`
- `has_column_privilege`
- `has_database_privilege`
- `has_foreign_data_wrapper_privilege`
- `has_function_privilege`
- `has_language_privilege`
- `has_schema_privilege`
- `has_sequence_privilege`
- `has_server_privilege`
- `has_table_privilege`
- `has_tablespace_privilege`
- `has_type_privilege`
- `pg_has_role` (_coming soon!_)

These all follow the specification documented by Postgres in:
https://www.postgresql.org/docs/8.4/static/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE

These Access Privilege Inquiry Functions allow users to query object
access privileges programmatically. Each function has a number of
variants, which differ based on their function signatures. These
signatures have the following structure:
```
- optional "user" argument
  - if used, can be a STRING or an OID type
  - if not used, current_user is assumed
- series of one or more object specifier arguments
  - each can accept multiple types
- a "privilege" argument
  - must be a STRING
  - parsed as a comma-separated list of privilege
```

This means that in total, each function has at least 6 variants.

The main reason for adding these builtins in is because they were the
last remaining issue that was blocking full compatibility with pgweb.
Pgweb is a web-based database browser written in Go, which means that
can run on OSX, Linux and Windows machines!

Release note (sql change): Introduces a series of Postgres-compatible
privilege-related builtin functions.
nvanbenschoten added a commit to nvanbenschoten/cockroach that referenced this issue Mar 14, 2018
@nvanbenschoten
sql: introduce PG-compatible access privilege inquiry functions
6474f3b 
Fixes cockroachdb#22734.
Fixes cockroachdb#20784.
Relates to cockroachdb#15441.

This change introduces a series of Postgres-compatible privilege-related
builtin functions:
- `has_any_column_privilege`
- `has_column_privilege`
- `has_database_privilege`
- `has_foreign_data_wrapper_privilege`
- `has_function_privilege`
- `has_language_privilege`
- `has_schema_privilege`
- `has_sequence_privilege`
- `has_server_privilege`
- `has_table_privilege`
- `has_tablespace_privilege`
- `has_type_privilege`
- `pg_has_role` (_coming soon!_)

These all follow the specification documented by Postgres in:
https://www.postgresql.org/docs/8.4/static/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE

These Access Privilege Inquiry Functions allow users to query object
access privileges programmatically. Each function has a number of
variants, which differ based on their function signatures. These
signatures have the following structure:
```
- optional "user" argument
  - if used, can be a STRING or an OID type
  - if not used, current_user is assumed
- series of one or more object specifier arguments
  - each can accept multiple types
- a "privilege" argument
  - must be a STRING
  - parsed as a comma-separated list of privilege
```

This means that in total, each function has at least 6 variants.

The main reason for adding these builtins in is because they were the
last remaining issue that was blocking full compatibility with pgweb.
Pgweb is a web-based database browser written in Go, which means that
can run on OSX, Linux and Windows machines!

Release note (sql change): Introduces a series of Postgres-compatible
privilege-related builtin functions.
nvanbenschoten added a commit to nvanbenschoten/cockroach that referenced this issue Mar 14, 2018
@nvanbenschoten
sql: introduce PG-compatible access privilege inquiry functions
f51bab0 
Fixes cockroachdb#22734.
Fixes cockroachdb#20784.
Relates to cockroachdb#15441.

This change introduces a series of Postgres-compatible privilege-related
builtin functions:
- `has_any_column_privilege`
- `has_column_privilege`
- `has_database_privilege`
- `has_foreign_data_wrapper_privilege`
- `has_function_privilege`
- `has_language_privilege`
- `has_schema_privilege`
- `has_sequence_privilege`
- `has_server_privilege`
- `has_table_privilege`
- `has_tablespace_privilege`
- `has_type_privilege`
- `pg_has_role` (_coming soon!_)

These all follow the specification documented by Postgres in:
https://www.postgresql.org/docs/8.4/static/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE

These Access Privilege Inquiry Functions allow users to query object
access privileges programmatically. Each function has a number of
variants, which differ based on their function signatures. These
signatures have the following structure:
```
- optional "user" argument
  - if used, can be a STRING or an OID type
  - if not used, current_user is assumed
- series of one or more object specifier arguments
  - each can accept multiple types
- a "privilege" argument
  - must be a STRING
  - parsed as a comma-separated list of privilege
```

This means that in total, each function has at least 6 variants.

The main reason for adding these builtins in is because they were the
last remaining issue that was blocking full compatibility with pgweb.
Pgweb is a web-based database browser written in Go, which means that
can run on OSX, Linux and Windows machines!

Release note (sql change): Introduces a series of Postgres-compatible
privilege-related builtin functions.
@nvanbenschoten nvanbenschoten mentioned this issue Sep 8, 2021
Merged
craig bot pushed a commit that referenced this issue Sep 16, 2021
@nvanbenschoten
Merge #69939
407f504 
69939: sql: implement pg_has_role r=nvanbenschoten a=nvanbenschoten

Needed for #69010.
Related to #22734.

This commit implements the `pg_has_role` builtin function. `pg_has_role`
returns whether the user has privileges for a specified role or not.
Allowable privilege types are MEMBER and USAGE. MEMBER denotes direct or
indirect membership in the role (that is, the right to do SET ROLE),
while USAGE denotes whether the privileges of the role are immediately
available without doing SET ROLE.

`pg_has_role` was the last remaining unimplemented "access privilege
inquiry functions", and was omitted from 94c25be because our role-based
access control system was not mature enough to support it at the time.

The commit also makes a small modification to `pg_catalog.pg_roles` and
`pg_catalog.pg_authid` to reflect that fact that all users and roles
inherit the privileges of roles they are members of.

Release note (sql change): The pg_has_role builtin function is now
supported, which returns whether a given user has privileges for a
specified role or not.

Release justification: None, waiting for v22.1.

Co-authored-by: Nathan VanBenschoten <nvanbenschoten@gmail.com>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@mberhault mberhault

Labels
None yet
Projects
None yet
Milestone
2.0
Development

No branches or pull requests
3 participants
@jordanlewis @nvanbenschoten @mberhault